Question about joining mysql tables and displaying summed value

PHP
1

Hi All, this is probably a rudimentary fix as I am new to web development so thanks in advance for any pointers…

I have 2 tables in a mysql db, called ‘points’ and ‘redirects’. The field to join them both is called ‘qotdid’. Some entries into the ‘points’ table have point values, but for those that don’t, I’d like to reference the ‘redirects’ table which contains entries for each ‘qotdid’ value and its associated point value. First, I’ve successfully created a program to output the sum of the point values from the ‘points’ table as follows:

$rev_sum_points = “SELECT SUM(points) AS points FROM nloppoints WHERE posttime BETWEEN ‘$fromdate’ and ‘$todate’”;
$rev_result_points = mysql_query($rev_sum_points);
$rev_display_points = mysql_fetch_assoc($rev_result_points);
$rev_output_points = $rev_display_points[‘points’];
echo $rev_output_points;

But I run into problems when trying to create a similar query to pull all the rows from the ‘points’ table where points=0, in which case the qotdid field is joined to the redirects table to pull the value. This is what i’ve tried (unsuccessfully):

$rev_sum_value = “SELECT points.qotdid, SUM(redirects.value) AS value FROM points, redirects WHERE points.qotdid = redirects.qotdid AND posttime BETWEEN ‘$fromdate’ and ‘$todate’”;
$rev_result_value = mysql_query($rev_sum_value);
$rev_display_value = mysql_fetch_assoc($rev_result_value);
$rev_output_value = $rev_display_points[‘value’];

echo $rev_output_value;

This produces no errors but doesn’t output anything either :-(. Any help is MUCH appreciated!

Thanks!

2

Try the SQL string in a desktop client, or even a web based one, to see if you’re returning the result-set you expect. :slight_smile:

3

First of all there’s no WHERE condition on ‘points = 0’ in that second query, so you’d get a result for al qotdid’s.
Second, that query does give an error, because you’re trying to SUM for each qotdid without using a GROUP BY clause.

You can test for errors the way Anthony describes, or checking for errors in your code, for example using ‘or die’:


$rev_result_value = mysql_query($rev_sum_value) or die("mysql error " . mysql_error() . " in query $rev_sum_value");
4

Thanks guys. I tried Anthony’s suggestion and ran this query within PHPMyAdmin and it outputs the intended results:

SELECT SUM(redirects.value) FROM points, redirects WHERE points.qotdid = redirects.qotdid AND posttime BETWEEN ‘2011-01-09 00:00:00’ and ‘2011-01-09 23:59:59’

All rows within the points table that have a value in the qotdid field do NOT have a value in the points field, so I didn’t think I needed to set a condition WHERE points = 0.

However, the php script still outputs nothing from this program - not even an error (and I did add the mysql_error function per Guido’s suggestion. This is what I used:

$rev_sum_value = “SELECT SUM(redirects.value) AS value FROM points, redirects WHERE points.qotdid = redirects.qotdid AND posttime BETWEEN ‘$fromdate’ and ‘$todate’”;
$rev_result_value = mysql_query($rev_sum_value) or die(“mysql error " . mysql_error() . " in query $rev_sum_value”);
$rev_display_value = mysql_fetch_assoc($rev_result_value);
$rev_output_value = $rev_display_points[‘value’];
echo $rev_output_value;

Any other suggestions?

Thanks.

5

$rev_display_value = mysql_fetch_assoc($rev_result_value);
$rev_output_value = $rev_display_points[‘value’];

6

Ahha! Here I was thinking it was some big formatting or syntax error when I simply overlooked the wrong variable name (copied from a different script and forgot to rename)! Thanks so much for your help!