1

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.

Improve this question
1
  • Do you get any results when you just select from pending (without the join)? Commented Oct 5, 2012 at 5:20

2 Answers 2

Reset to default
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?

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

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?
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.
so the relationship between the two tables is paymentid = log_id right? Anyway, I just updated the answer.
No, paymentid = linkid. Why, do they both have to be keys for this to work?
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?
|
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.

Improve this answer

1 Comment

Could you provide an example?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.