I'm trying to retrieve data from 3 tables using a left join but there's some things I'm having problems understanding. The code below works.
$joins = "SELECT porders.reqid, porders.podatesent, porders.porderno, reqs.reqdatechanged, reqs.dateraised, reqs.reqno, gdsin.gdsinstatus FROM porders LEFT JOIN reqs ON porders.reqid = reqs.reqid LEFT JOIN gdsin ON porders.reqid = gdsin.reqid WHERE `postatus` = 'processed' AND `poclientid` = '$client_id' ";
And the bold is my problem.
It didn't work when I had AND `clientid` = '$client_id' and the only commonality I could see was the column name. clientid is initiated on log in, stored in a session and used to retrieve all client content so its in all the tables.
As soon as I changed clientid to poclientid in the porders table it worked.
The reqid is also in all the tables and I use it to stitch the query together but when other columns (clientid) are the same it doesn't work, my fix is change the column name but that can't be right can it?
The other thing I don't understand is when I initiate my stipulations WHERE `postatus` = 'processed' AND `poclientid` = '$client_id' I'm referring to the porders table because postatus and poclientid are both in there but at the start of that line I LEFT JOIN gdsin. From this I'm assuming that mysql has access to them because I make a call from the porders table with the FROM porders part of the query.
That assumption sort of adds up to me but changing the column name in mysql surely can't be right can it?
I thought clientid was used by loads of applications for retrieving user defined content therefore it would be present in one table as the primary key and the other tables as the unique id. Do you have to change it to different column names if you want to use JOINS?
24 hours later I remember I could try the query in phpMyAdmin :-)
I changed the table back to clientid from poclient and tried it, got some more info, this error.
mysql #1052 - Column 'clientid;' in where clause is ambiguous.
Googled it and found many posts and a very concise answer:
Its just like the error is saying that your field 'catID' in where clause in ambiguous, this means that it doesnt know wich field you are using because the same field is in both tables (tbl_listings,tbl_category), so you need to tell to mysql witch one u want.
Changed my code to this and works well:
WHERE porders.postatus = 'processed' AND porders.clientid = '$clientid'