Working with multiple tables

[Drabin]

Here's my current code:

<?php

// Establish our connection and select our database
$link = mysql_connect($db_host, $db_user, $db_passwd) or die ("Could not connect to desired database.");
mysql_select_db($db_name) or die ("Could not select desired database.");

## Get Count for ZG Coins per name
$countcoins = "SELECT COUNT(coins) AS zgc FROM `".ZG."` WHERE name='".$name."'";
$coinresult = mysql_query($countcoins);
$cointotal = mysql_result($coinresult, 0, 'zgc');

## Get Variable for ZG Coins
$varcoins = "SELECT zgcoins FROM `".VALUE."`";

## Get CIP Total for ZG Coins
$cipcointotal = (($cointotal * $varcoins)*100);
die('' . $name . ' cip coin total is equal to ' . $cipcointotal . '%.');
?>

In the .ZG. table I have 4 rows of data.

Benkei Coin1
Benkei Coin2
Joshua Coin3
Joshua Coin4

It's supposed to count up the number of Coins it sees from the name Benkei and then multiply that by .25 which is the variable set in zgcoins from the table .VALUE. and then multiply this by 100 and get my total.

Instead I get the following after initiating the file:

cip coin total is equal to 0%.

What am I doing wrong?

[Roger_Ramjet]

What is this ".ZG." - specifically what is ZG

It's not a variable name - no $
It's not anything in php that I know of
Maybe it's the name of a function that you wrote.

[Drabin]

Ah sorry, it's a set of table names that I put in the config file. Here is the reference to that:

  // Table Names
  define('TOTAL',$db_prefix.'fab_total');
  define('VALUE',$db_prefix.'fab_value');
  define('ZG',$db_prefix.'fab_zg');
  define('MC',$db_prefix.'fab_mc');
  define('BWL',$db_prefix.'fab_bwl');

[Roger_Ramjet]

Echo the query to the screen before you run it. The fact that $name is an empty string in your output means it is probably an empty string in your query: hence 0 rows returned.

[Drabin]

After many hours, I figured out a good output system. I came up with:

// Establish our connection and select our database
$link = mysql_connect($db_host, $db_user, $db_passwd) or die ("Could not connect to desired database.");
mysql_select_db($db_name) or die ("Could not select desired database.");

## Performing a query to find all the ingredients of the ZG loot system, including the 
## event days where the names match up for both tables

//$coincount = "SELECT name, COUNT(coins) as coins FROM `".ZG."` GROUP BY name HAVING ( COUNT(coins) >= 1 )";
$coincount = "SELECT fab_zg.name,
fab_zg.class,
fab_zg.coins as loot1, 
fab_zg.bijous as loot2,
fab_zg.primals as loot3,
fab_zg.common as loot4,
fab_zg.rares as loot5,
fab_zg.epics as loot6,
COUNT(fab_zg.coins) as coins, 
COUNT(fab_zg.bijous) as bijous,
COUNT(fab_zg.primals) as primals,
COUNT(fab_zg.common) as common,
COUNT(fab_zg.rares) as rares,
COUNT(fab_zg.epics) as epics,
fab_total.ed_zg as events 
FROM `".ZG."`, `".TOTAL."` 
WHERE fab_zg.name = fab_total.name 
GROUP BY name";
//HAVING ( COUNT(coins) >= 1 )";
$coinresult = mysql_query($coincount);
$num=mysql_numrows($coinresult);

## Get Variable for the ZG System percentages

$varcoins = "SELECT * FROM `".VALUE."`";
$varresult = mysql_query($varcoins);
$coinperc = mysql_result($varresult, 0, "zgcoins");
$bijouperc = mysql_result($varresult, 0, "zgbijous");
$primalperc = mysql_result($varresult, 0, "zgprimals");
$commonperc = mysql_result($varresult, 0, "zgcommon");
$rareperc = mysql_result($varresult, 0, "zgrares");
$epicperc = mysql_result($varresult, 0, "zgepics");

## Search through the queries and sort the information
## Information sorted by item type and is then plugged 
## into the FAB loot system formula

$i=0;
	while ($i < $num) {

$name=mysql_result($coinresult,$i,"name");
$class=mysql_result($coinresult, $i, "class");
// Now plug the variables for numbers of types of items gained
$coins=mysql_result($coinresult,$i,"coins");
$bijous=mysql_result($coinresult,$i,"bijous");
$primals=mysql_result($coinresult,$i,"primals");
$common=mysql_result($coinresult,$i,"common");
$rares=mysql_result($coinresult,$i,"rares");
$epics=mysql_result($coinresult,$i,"epics");
// Now plug the variables for name types on loot
$loot1=mysql_result($coinresult,$i,"loot1");
$loot2=mysql_result($coinresult,$i,"loot2");
$loot3=mysql_result($coinresult,$i,"loot3");
$loot4=mysql_result($coinresult,$i,"loot4");
$loot5=mysql_result($coinresult,$i,"loot5");
$loot6=mysql_result($coinresult,$i,"loot6");
// Now plug the number of events
$events=mysql_result($coinresult,$i,"events");
// Now formalize the percentages for each item gained
$coincip=(($coins/($events * $coinperc)) * 100);
$bijoucip=(($bijous/($events * $bijouperc)) * 100);
$primalcip=(($primals/($events * $primalperc)) * 100);
$commoncip=(($common/($events * $commonperc)) * 100);
$rarecip=(($rares/($events * $rareperc)) * 100);
$epiccip=(($epics/($events * $epicperc)) * 100);
$i++;
}
?>

I split the while loop so that it surrounds my table.php content and it works good now.