Joining 3 tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
snappydesigns
Forum Commoner
Posts: 28
Joined: Tue Feb 13, 2007 11:37 am

Joining 3 tables

Post 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
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Post 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' ";
snappydesigns
Forum Commoner
Posts: 28
Joined: Tue Feb 13, 2007 11:37 am

Post 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
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Post 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.
Post Reply