CODEWITHSUNDEEP
sqlintersectionlogical-operatorsdisjoint-union
Vecta
Vecta

Reputation: 2350

SQL query with AND/OR logical operators

fI have what seems to be a relatively simple AND/OR clause that is giving me a bit of a headache. 

$query = "SELECT stv.name,stc.tmplvarid,stc.contentid,stv.type,stv.display,stv.display_params,stc.value";
$query .= " FROM ".$tb1." stc LEFT JOIN ".$tb2." stv ON stv.id=stc.tmplvarid ";
$query .= " LEFT JOIN $tb_content tb_content ON stc.contentid=tb_content.id ";
$query .= " WHERE stv.name='".$region."' AND stc.contentid IN (".implode($cIDs,",").") ";
$query .= " OR stv.name='".$countries."' AND stc.contentid IN (".implode($cIDs,",").") ";
$query .= " AND tb_content.pub_date >= '$pub_date' ";
$query .= " AND tb_content.published = 1 ";
$query .= " ORDER BY stc.contentid ASC;";

I need it to retrieve the values from both $region and $countries. However, the way it is it only returns the value of $countries. If I remove the line with the OR it returns the value of $region, but it won't return both. What am I doing incorrectly?

Upvotes: 0

Views: 593

Answers (2)

Gabriel Magana
Gabriel Magana

Reputation: 4536

Looks like you are missing parentheses, no?

A or B and C or D is ambiguous, you need to use parentheses to make it clear you mean (A or B) and (C or D)

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171401

Try putting brackets around the clauses on either side of the OR. See below:

$query = "SELECT stv.name,stc.tmplvarid,stc.contentid,stv.type,stv.display,stv.display_params,stc.value"; 
$query .= " FROM ".$tb1." stc LEFT JOIN ".$tb2." stv ON stv.id=stc.tmplvarid "; 
$query .= " LEFT JOIN $tb_content tb_content ON stc.contentid=tb_content.id "; 
$query .= " WHERE (stv.name='".$region."' AND stc.contentid IN (".implode($cIDs,",").")) "; 
$query .= " OR (stv.name='".$countries."' AND stc.contentid IN (".implode($cIDs,",").")) "; 
$query .= " AND tb_content.pub_date >= '$pub_date' "; 
$query .= " AND tb_content.published = 1 "; 
$query .= " ORDER BY stc.contentid ASC;";

Upvotes: 2

Related Questions