Why isn't my SQL LEFT JOIN query working?

Question

It is returning 0 rows when there should be some results.

This is my first attempt at using JOIN but from what I've read this looks pretty much right, right?

"SELECT pending.paymentid, pending.date, pending.ip, pending.payer, pending.type, pending.amount, pending.method, pending.institution, payment.number, _uploads_log.log_filename 
FROM pending 
LEFT JOIN _uploads_log 
ON pending.paymentid='".$_GET['vnum']."' 
AND _uploads_log.linkid = pending.paymentid"

I need to return the specified values from each table where both pending.paymentid and _uploads_log.log_filename are equal to $_GET['vnum]

What is the correct way to do this? Why am I not getting any results?

If someone more experienced than me could point me in the right direction I would be much obliged.

EDIT

For pending the primary key is paymentid, for _uploads_log the primary is a col called log_id and log_filename is listed as index.

Do you get any results when you just select from pending (without the join)? — Greg, Oct 05 '12 at 05:20

score 5 · Accepted Answer · edited May 23 '17 at 11:56

5

Try this

 SELECT  pending.paymentid, 
         pending.date, 
         pending.ip, 
         pending.payer, 
         pending.type, 
         pending.amount, 
         pending.method, 
         pending.institution, 
         payment.number, 
         _uploads_log.log_filename 
FROM     pending 
         LEFT JOIN _uploads_log 
              ON _uploads_log.linkid = pending.paymentid
WHERE   _uploads_log.log_filename = '" . $_GET['vnum']  . "'

Your current query is vulnerable with SQL Injection. Please take time to read the article below.

Best way to prevent SQL injection in PHP?

edited May 23 '17 at 11:56

Community

1
1

answered Oct 05 '12 at 05:20

John Woo

258,903
69
498
492

you mean the code I added won't work? actually your question is not clear. can you add the schema of your tables? what are the foreign keys of the tables? – John Woo Oct 05 '12 at 05:35
Yes your code isn't returning any results either. For `pending` the primary key is `paymentid`, for `_uploads_log` the primary is a col called `log_id` and `log_filename` is listed as index. – cream Oct 05 '12 at 05:48
so the relationship between the two tables is `paymentid = log_id` right? Anyway, I just updated the answer. – John Woo Oct 05 '12 at 05:51
No, `paymentid` = `linkid`. Why, do they both have to be keys for this to work? – cream Oct 05 '12 at 05:55
becuase they are the keys that define how are the two tables be link with each other. have you tried running it on command line or mysql browser? does it have results? or does `$_GET['vnum']` has value? – John Woo Oct 05 '12 at 06:02
I haven't tried it outside of PHP yet. `$_GET['vnum']` has a value. So you're saying I need to make `linkid` the primary? – cream Oct 05 '12 at 06:06
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/17585/discussion-between-john-woo-and-higgs-boson) – John Woo Oct 05 '12 at 06:07

score 0 · Answer 2 · answered Oct 05 '12 at 05:27

0

The ON clause only should have the condition to link the two tables especially if it is LEFT JOIN. The WHERE clause then has the actual condition. Otherwise you will get nothing if there is no corresponding entry in _uploads_log. It also is more easy to read in my opinion.

As another remark. It is always better to work with bind parameters to avoid SQL injection.

answered Oct 05 '12 at 05:27

hol

8,255
5
33
59

Could you provide an example? – cream Oct 05 '12 at 05:35

Why isn't my SQL LEFT JOIN query working?

2 Answers2