[Resolved] trouble with left joins

phpmysqlrules

Greetings and Good Day ... and happy spring to all the westerners

I have a little problem with my query below...

Could some pro offer advise on how I am structuring it wrong ??


$getlisting="Select photo_1,photo_2,photo_3,photo_4,photo_5,title,price,description,
agents.phone,agents.fax,agents.cell,agents.email,agents.website,agents.agent_logo
FROM listings LEFT JOIN  agents on listings.agent_id=agents.agent_id";

the above query works

however when I insert the where clause It does not work

WHERE listing_id='5'


$getlisting="Select photo_1,photo_2,photo_3,photo_4,photo_5,title,price,description,
agents.phone,agents.fax,agents.cell,agents.email,agents.website,agents.agent_logo
FROM listings WHERE listing_id='5' LEFT JOIN  agents on listings.agent_id=agents.agent_id";

this is the error I get :::

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

Thanks for looking

Cheers

AstroTeg

The LEFT JOIN clause goes in the FROM clause and before the WHERE clause (you need to move your WHERE statement behind your LEFT JOIN statement).

It also can be a super-duper help to toss your query into something like phpMyAdmin and it'll give you a more descriptive error which may lead you to the problem faster than posting the query on a board (just a thought).

phpmysqlrules

Thanks for the tip... it worked great 🙂

I did throw it into phpmyadmin but the result was not very helpful to me as the query got chopped off before it recognized the problem...

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '$getlisting = "Select photo_1,photo_2,photo_3,photo_4,photo_5...(this is as far as it went...)

Thnaks so much for your time...

much appreciated

PEace

AstroTeg

Originally posted by phpmysqlrules

I did throw it into phpmyadmin but the result was not very helpful to me as the query got chopped off before it recognized the problem...

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '$getlisting = "Select photo_1,photo_2,photo_3,photo_4,photo_5...(this is as far as it went...)
PEace

In a case like this, you can swap out your entire select clause with an so it will bring back all fields available. This isn't what you want in your code, but it will reduce the text in your query so you can see what its hanging up on. So your test query might look like: Select FROM listings LEFT JOIN agents on listings.agent_id=agents.agent_id

Also, I don't know if it was a copy and paste job gone wrong, but you posted '$getlisting = "Select photo_1,photo_2,photo_3,photo_4,photo_5...(this is as far as it went...) - phpMyAdmin will choke on the "$getlisting = " part.

phpmysqlrules

Thanks for the follow up...

will try the SELECT * appoach next time...

spent most of the day reading MySql book by Dubois (New Riders)...

not a bad book ...

Cheers

liquorvicar

actually, i suspect you were getting that error from phpMyAdmin because you had $getlisting = " in your SQL query. make sure you strip out all PHP code before you run the query through phpMyAdmin.

in general, PMA will only output the contents of mysql_error when you get an error and MySQL will quote the section of SQL directly after where the error has occurred, which is why it chops the quoted section of code as it should have already output enough for you to see the context.

also, in future when you get "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource ".try re-running the script with

or die(mysql_error());

at the end of your mysql_query statement to trap any SQL errors and get a meaningful message.