Page 1 of 1

Joining 3 tables

Posted: Mon Sep 10, 2007 11:34 pm
by snappydesigns
Hey,

I have 3 tables: clients, john1_proofs, and prices. clients and john1_proofs have a column in common, and john1_proofs and prices have a column in common.

I keep getting this error:
Could not execute the query: Unknown column 'john004' in 'where clause'
This makes no sense because the column is called "proof_name" and john004 is one of the inputs for a particular row. So if I click on the "john005" thumbnail, I get the same message but with 'john005' in the message.

Here's the query I've got:

Code: Select all

$query = 'SELECT john1_proofs.proof_name, john1_proofs.session_date, john1_proofs.image_name, clients.client_name, prices.8wallets, prices.5x7, prices.8x10, prices.11x14, prices.16x20, prices.20x24, prices.20x30, prices.11x14can, prices.16x20can, prices.20x30can FROM john1_proofs INNER JOIN clients ON john1_proofs.client_code = clients.client_code LEFT JOIN prices ON john1_proofs.prints = prices.prints WHERE john1_proofs.proof_name = ' . $id;
I've also tried:

Code: Select all

$query = 'SELECT john1_proofs.proof_name, john1_proofs.session_date, john1_proofs.image_name, clients.client_name, prices.8wallets, prices.5x7, prices.8x10, prices.11x14, prices.16x20, prices.20x24, prices.20x30, prices.11x14can, prices.16x20can, prices.20x30can FROM john1_proofs, clients, prices WHERE john1_proofs.client_code = clients.client_code AND john1_proofs.prints = prices.prints AND john1_proofs.proof_name = ' . $id;
Both give me the same error. This is the first join I've tried, so attempting to join 3 tables is about to make my head pop. Any help would be VERY appreciated.

Thanks a bunch!
Jen

Posted: Tue Sep 11, 2007 8:10 am
by Zoxive
Did you read your error message?

Your not quoting your input.

Code: Select all

$query = "SELECT john1_proofs.proof_name, john1_proofs.session_date, john1_proofs.image_name, clients.client_name, prices.8wallets, prices.5x7, prices.8x10, prices.11x14, prices.16x20, prices.20x24, prices.20x30, prices.11x14can, prices.16x20can, prices.20x30can FROM john1_proofs, clients, prices WHERE john1_proofs.client_code = clients.client_code AND john1_proofs.prints = prices.prints AND john1_proofs.proof_name = '$id' ";

Posted: Tue Sep 11, 2007 8:52 am
by snappydesigns
Awesome...that worked. I did read the error message a thousand times, but I didn't realize that's all it was telling me I needed. I've used the same syntax for other projects (just using one table though) and never needed those extra quotes before. Glad to know now.

Thanks so much. I'll probably be back for more help because this is just the beginning :wink:.

Jen

Posted: Tue Sep 11, 2007 9:01 am
by Zoxive
snappydesigns wrote:Awesome...that worked. I did read the error message a thousand times, but I didn't realize that's all it was telling me I needed. I've used the same syntax for other projects (just using one table though) and never needed those extra quotes before. Glad to know now.

Thanks so much. I'll probably be back for more help because this is just the beginning :wink:.

Jen
Before you probably only had Numbers which don't need to be quoted (Some times are in Parentheses though).
Strings on the other hand do.