views:

50

answers:

2

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

+2  A: 

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

Lekensteyn
No, not possible. We use the same users
Florian
Is that all of your code, or are you overwriting `$res` somewhere? If you haven't tuned `error_reporting` to `E_ALL`, do it now: `error_reporting(E_ALL);`. Do you get any errors?
Lekensteyn
There are no errors, just some warnings in the code after, which is normal. Thanks for help
Florian
Not an actual answer...
Eton B.
A: 

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.

Syntax Error
The implicit join syntax used in the question is actually equivalent to an inner join, but I agree that explicit join syntax is preferable.
Hammerite
Yes, I should have mentioned that, and I might be heavy handed with my use of LEFT JOINs, but in this case I think it would help detect if the reason for the empty set was that there wasn't a matching member or user.
Syntax Error