CODEWITHSUNDEEP
phpmysqlsql
drandom
drandom

Reputation: 180

Why Does This MySQL Query Return Just 1 Result?

Is the syntax right? 

SELECT * 
  FROM productOptions 
 WHERE productOptionsID IN ('1,2,3,4')

Have I used IN properly, or should the comma separated values be different?

This is followed by the following code:

$optionsresultsql= mysql_query($optionsquerysql) or die(mysql_error());

while($optionssql = mysql_fetch_array($optionsresultsql)) {
  $optionNamesID = $optionssql["optionNamesID"];
  echo $optionNamesID;
}

Only one result is shown, even though there are 4 matches in the DB.

Upvotes: 2

Views: 151

Answers (2)

gen_Eric
gen_Eric

Reputation: 227310

'1,2,3,4' is a string, which is being converted to an int. MySQL converts strings to ints by reading up until the 1st non-number character, in your case, the ,.

So,

IN ('1,2,3,4') = IN (CAST('1,2,3,4' AS INT)) = IN (1)

The query should just be:

productOptionsID IN (1,2,3,4)

Upvotes: 6

FatherStorm
FatherStorm

Reputation: 7183

remove the single-ticks when dealing with INT lookups..

"SELECT * FROM productOptions WHERE productOptionsID IN (1,2,3,4)"

Upvotes: 9

Related Questions