0

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('xxx@xxx.com','test',mysql_error());
}

mail("xxx@xxx.com", "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

BoltClock
  • 700,868
  • 160
  • 1,392
  • 1,356
Florian
  • 71
  • 8
  • 4
    I refuse to help someone that gets answers from this site but doesn't participate and mark answers as accepted – Galen Aug 27 '10 at 16:56
  • 1
    Hi guys. Yes I know haven't answered the last question, but that is for a simple reason: I worked in a bank before, and many script executions where blocked on the browsers. And I couldn't validate an answer or post any comments because of this restriction. I'm really sorry about that. – Florian Aug 27 '10 at 16:58
  • 1
    @Florian hard code `$orderID` and check out if it turns out ok. And START GIVING CREDIT FOR THOSE ANSWERS! What's the matter with you? – Frankie Aug 27 '10 at 17:00
  • We tried hard coding $orderID, same problem. And I started giving credit. Chill man ! – Florian Aug 27 '10 at 17:01
  • Do you have access to the mysql console on that server? Best to try the query in that, rather than going through phpmyadmin. It's one less thing to cause problems. – Marc B Aug 27 '10 at 17:08
  • when trying the following in the mysql console: "SELECT m.id, m.expired_date, u.email FROM jos_osemsc_member m, jos_osemsc_orders o, jos_users u WHERE o.order_id = 470 AND m.member_id = o.user_id AND u.id = o.user_id;" I get "Empty set (0.00sec)"... WIRED – Florian Aug 27 '10 at 17:18
  • On a completely unrelated note, to save you from headaches in the future. Use prepared statements, or at least start real escaping your query parameters. – jlindenbaum Aug 27 '10 at 17:36

2 Answers2

3

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

Lekensteyn
  • 64,486
  • 22
  • 159
  • 192
  • No, not possible. We use the same users – Florian Aug 27 '10 at 17:00
  • 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 Aug 27 '10 at 17:02
  • There are no errors, just some warnings in the code after, which is normal. Thanks for help – Florian Aug 27 '10 at 17:03
0

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
  • 4,475
  • 2
  • 22
  • 33
  • 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 Aug 27 '10 at 17:38
  • 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 Aug 27 '10 at 17:43