SELECT SUM PHP MySQL problem

Question

This is driving me nuts! Below you will find my PHP/MySQL code but I will post the direct mySQL statement here:

   SELECT SUM( ot.value ) AS msa
     FROM orders o
LEFT JOIN orders_total ot ON ot.orders_id = o.orders_id
    WHERE ot.class = 'ot_total'
      AND UNIX_TIMESTAMP( o.date_purchased ) >=1262332800
      AND UNIX_TIMESTAMP( o.date_purchased ) <=1264924800
      AND o.sales_rep_id = '2'

When I execute this statement inside of phpMyAdmin I get the sum for ot.value which is associated to "msa". Although, when I run my php code it does not return a value. Anyone see the problem?

// works in phpMyAdmin but not displaying during PHP execution!
$monthly_sales_amount_sql = "SELECT SUM(ot.value) AS msa 
                               FROM orders o 
                          LEFT JOIN orders_total ot ON ot.orders_id = o.orders_id 
                              WHERE ot.class = 'ot_total' 
                                AND UNIX_TIMESTAMP(o.date_purchased) >= $start_timestamp 
                                AND UNIX_TIMESTAMP(o.date_purchased) <= $end_timestamp 
                                AND o.sales_rep_id = '" . $sales_rep_id . "'";                                       

$result = mysql_query($monthly_sales_amount_sql);
$row = mysql_fetch_assoc($result); 

echo "MSA: " . $row['msa'] . "<BR><BR>";

Answer 1

For situations like these, print the SQL statement out just before the mysql_query command is executed:

print($monthly_sales_amount_sql)
$result = mysql_query($monthly_sales_amount_sql);

Reminder: Your query is susceptible to SQL Injection attacks.

Answer 2

AND o.sales_rep_id = '" . $sales_rep_id "'";

There's a syntax error right there after $sales_rep_id as you're missing a concatenation operator.

Answer 3

You should go through standard debugging.

Display the sql string you are attempting to execute to make sure that it really is what you think it is. This is the most common error when doing these things.

Check for mysql errors from php, syntax, connection, etc.