在第一个表上使用WHERE子句进行LEFT OUTER JOIN

Question

I want to SELECT a some rows from a table, but these rows must satisfy a condition like column = value. Here's my SELECT statement:

$query = "SELECT t1.title, t1.introtext, t2.jr_street, t2.jr_city, 
    t2.jr_state, t2.jr_postalcode, t2.jr_country, t3.created, t3.name, 
    t3.title, t3.comments, t4.ave 
FROM $table1 t1
    LEFT OUTER JOIN $table2 t2 ON t1.id = t2.contentid
    LEFT OUTER JOIN $table3 t3 ON t1.id = t3.pid
    LEFT OUTER JOIN $table4 t4 ON t1.id = t4.reviewid
";

I tried adding a WHERE clause after the FROM statement, but I am getting an SQL syntax error.

Answer 1

Seems like I have to guess your problem. Next time you should give us your full example a the exact error. Did you try something like this?

$value = (int) $_POST['value'];
$query = "SELECT t1.title, t1.introtext, t2.jr_street, t2.jr_city, t2.jr_state,
t2.jr_postalcode, t2.jr_country, t3.created, t3.name, t3.title, 
t3.comments, t4.ave FROM $table1 t1
LEFT OUTER JOIN $table2 t2 ON t1.id = t2.contentid
LEFT OUTER JOIN $table3 t3 ON t1.id = t3.pid
LEFT OUTER JOIN $table4 t4 ON t1.id = t4.reviewid
WHERE t2.column = ".$value."
AND t1.catid=8";

I just guessed you want to select from table2 ;) You have to fill in the right table and column. Also i put the category selection in the where clause since imho this makes it more readable.

Please make absolutely sure that all variables like $value (and the tables) are save for usage in a query, for example that $value is an integer and the tables are explicitly set in your code and are not a user input. You can read more about SQL-Injections here:

• http://de.wikipedia.org/wiki/SQL-Injection
• How can I prevent SQL injection in PHP?
• Why shouldn't I use mysql_* functions in PHP?