mysql_num_rows returns 0, but the same query in phpMyAdmin return results

Question

Hello all,

We have been working for a few hours on a serious problem.

We have the following code:

mysql_connect("localhost","xxx","xxx") or die(mysql_error());
mysql_select_db("xxxe");

$q = "SELECT m.id, m.expired_date, u.email
     FROM jos_osemsc_member m, jos_osemsc_orders o, jos_users u
    WHERE o.order_id  = $orderID
   AND m.member_id = o.user_id
   AND u.id  = o.user_id";

$res = mysql_query($q);

if (!$res) {
  mail('[email protected]','test',mysql_error());
}

mail("[email protected]", "count",  mysql_num_rows($res));

We receive the "count" mail, but with "0" for result of mysql_num_rows. If we send the query ($q) by e-mail, and execute it in phpMyAdmin, it works and we get one row resulted...

Any ideas ?

Thanks for the help which will be VERY apperciated

Answer 1

It is possible that user xxx has more restrictive permissions than the user you use for PMA.

Answer 2

Try re-wording your query. I find LEFT JOINs much easier to comprehend and deal with.

SELECT m.id, m.expired_date, u.email
FROM jos_osemsc_orders AS o 
        LEFT JOIN jos_osemsc_member AS m ON (m.member_id = o.user_id) 
        LEFT JOIN jos_users AS u ON (u.id = o.user_id)
WHERE o.order_id  = $orderID;

If that doesn't work, reduce the query to just the orders table and make sure you get a result. If that works add a table, etc...

Also, I can see where there would be a problem if an order was placed by someone who was a user but not a member, or vice versa. However the left join style query would solve that problem. Also, I edited the order of the tables in the query to make more sense.