[RESOLVED] pulling all values from 1 table and partial from another problem

PABobo

I am trying to pull a list of gift names from 1 table. Easy enough. Then i am SUMMING another table based on gift id's from table 1 to table 2. On my output page i need to take list from table 1 show them all then match my results from table 2 to table 1. My problem is that if i have a null value it doesn't line up. I have TRIED IFNULL(SUM(column), 0) as reult1 and it doesn't return a value.

here is what i have in the queries

$resultgiftsr=mysql_query("select gifts.gift_name as gift_namer, gifts.gift_id as revid from gifts WHERE gifts.gift_type = '$rev' ORDER BY gifts.gift_name")or die('gift_rev_died');
$num_fields = mysql_num_fields($resultgiftsr);
$j=0;
$x=1;
while($row=mysql_fetch_array($resultgiftsr)){

for($j=0;$j<$num_fields;$j++){
$name = mysql_field_name($resultgiftsr, $j);
$objectgr[$x][$name]=$row[$name];
}$x++;
}
$resultoctgr=mysql_query("SELECT gifts.gift_id as octrevid, IFNULL(SUM($octg$year.gift_order_total),0) as totalsoctobergiftsgr, IFNULL(SUM($octg$year.gift_order_fmv),0) as totalsoctobergiftsfmvgr FROM $octg$year INNER JOIN gifts ON gifts.gift_id = $octg$year.gift_id WHERE $octg$year.member_profile_id ='$col' AND $octg$year.status = '$stats' AND gifts.gift_type = '$rev' Group By gifts.gift_name ORDER BY gifts.gift_name")or die("$octg$year died");
$num_fields = mysql_num_fields($resultoctgr);
$j=0;
$x=1;
while($row=mysql_fetch_array($resultoctgr)){

for($j=0;$j<$num_fields;$j++){
$name = mysql_field_name($resultoctgr, $j);
$objectgr[$x][$name]=$row[$name];
}$x++;
}

Roger_Ramjet

Do it all in 1 query with a LEFT JOIN is how this should be done - always assuming you have your data normalised properly. You can get also away with some GROUP BY coding shortcuts in mysql that will NOT work with most other db engines

$query = "SELECT gifts.gift_name, gifts.gift_id, SUM($octg$year.gift_order_total) AS totalsoctobergiftsgr,  SUM($octg$year.gift_order_fmv) as totalsoctobergiftsfmvgr
FROM gifts LEFT JOIN $octg$year USING (gift_id)
WHERE gifts.gift_type = '$rev' AND $octg$year.member_profile_id ='$col' AND $octg$year.status = '$stats'
GROUP BY $octg$year.gift_order_total, $octg$year.gift_order_fmv
ORDER BY gifts.gift_name";

Now, the resulting NULL values that you will get for the $octg$year columns when there is no match in that table are what you test for in your php code when generating the html output, ie

if (isnull($row['totalsoctobergiftsgr'])) {
   echo '0.00';
} else {
   echo $row['totalsoctobergiftsgr'];
}

PABobo

I have changed it to be a right join and also a different method of If NULL. The one reason i don't want to do it in 1 script is that i have 12 months (12 tables) to pull from. so even if i have october_gifts.member_profile_id = TRUE january_gifts.member_profile_id may be false which would return a false throughout. Am i correct in this?
This is what i came up with don't know if it's optimized though. Thanks for your help it will work for me on another report i'm working on.!

$resultgiftsr=mysql_query("select gifts.gift_name as gift_namer, gifts.gift_id as revid from gifts WHERE gifts.gift_type = '$rev' ORDER BY gifts.gift_id")or die('gift_rev_died');
$num_fields = mysql_num_fields($resultgiftsr);
$j=0;
$x=1;
while($row=mysql_fetch_array($resultgiftsr)){

for($j=0;$j<$num_fields;$j++){
$name = mysql_field_name($resultgiftsr, $j);
$objectgr[$x][$name]=$row[$name];
}$x++;
}
$resultoctgr=mysql_query("SELECT if($octg$year.gift_order_total IS NOT NULL,SUM($octg$year.gift_order_total), '0') as totalsoctobergiftsgr, if($octg$year.gift_order_fmv IS NOT NULL,SUM($octg$year.gift_order_fmv), '0') as totalsoctobergiftsfmvgr FROM $octg$year RIGHT JOIN gifts ON gifts.gift_id = $octg$year.gift_id WHERE $octg$year.member_profile_id ='$col' AND $octg$year.status = '$stats' AND gifts.gift_type = '$rev' OR $octg$year.member_profile_id IS NULL AND $octg$year.status IS NULL AND gifts.gift_type = '$rev' Group By gifts.gift_id ORDER BY gifts.gift_id")or die("$octg$year died");
$num_fields = mysql_num_fields($resultoctgr);
$j=0;
$x=1;
while($row=mysql_fetch_array($resultoctgr)){

for($j=0;$j<$num_fields;$j++){
$name = mysql_field_name($resultoctgr, $j);
$objectgr[$x][$name]=$row[$name];
}$x++;
}

Roger_Ramjet

You should read up on JOINS in the manual; even though it is not very clear. For 1 thing they deprecate the RIGHT JOIN.

A left join will list all rows from the left table, but only those rows from the right table that match - otherwise the selected columns from the second table will be filled with NULL

select tbl1.col1, tbl1.col2, tbl2.col1, tbl2.col2 from tbl1 LEFT JOIN tbl2 USING (col3)

might return

tbl1.c1 tbl1.c2 tbl2.col1 tbl2.col2
1 test1 blah blah2
2 test2 NULL NULL
3 test3 halb blcik
4 test1 NULL NULL

The only thing that will exclude a row from the LEFT table is a WHERE condition on that table: WHERE conditions on the right table have no effect on the left table.