[Resolved] How to do query "where XXX in YYY"?

[rachelkoh]

Hi, I'm having problem with the "in" operator.
I'm not sure how to use it in my suituation. Running the below, I got Nothing3. Please help me... 🙁

$result1=mysql_query("select pid from work_on where username = '$username'");
  if (!$result1)
  { echo "Nothing1";
    return false; 
  }
  $result2 = mysql_query( "select username from work_on where pid in (\"$result1\")");
  if (!$result)
  { echo "Nothing2";
    return false; 
  }

  if (mysql_num_rows($result2) == 0)
	{ echo "Nothing3";
    return false; 
  }

[laserlight]

Maybe try:

$result1 = mysql_query("select pid from work_on where username = '$username'");
if (!$result1 || mysql_num_rows($result1) == 0)
{
	echo "Nothing1";
	return false;
}
$result2 = mysql_query("select username from work_on where pid in ('$result1')");
if (!$result2)
{
	echo "Nothing2";
	return false;
}

if (mysql_num_rows($result2) == 0)
{
	echo "Nothing3";
	return false;
}

I'm not too sure of the in operator myself though.

[LordShryku]

The in operator is for selecting one field within multiple values. It's like a short OR

SELECT *
FROM table
WHERE field IN ('value1','value2','value3')

Instead of...

SELECT * 
FROM table
WHERE (field = 'value1'
       OR field = 'value2'
       OR field = 'value3')

[rachelkoh]

not working too. Same output Nothing3 🙁
I know that it has to be in this format:

XXX in ("YY1","YY2")

tests whether XXX in set ("YY1","YY2").
I noticed that my result2 is an array, can I change it into the set format?

[laserlight]

oh, okay.

The reason is that $result1 is a resource, not a string.

You need to use mysql_fetch_array() on it, then access the relevant array element, e.g.
$row = mysql_fetch_array($result1);
then use $row['pid']

[LordShryku]

select username from work_on where pid in (\"$result1\")

$result1 at this point is nothing more than a Resource ID since you never fetched the results. You need mysql_fetch_row, mysql_fetch_array, assoc, something to fetch them.....

[rachelkoh]

Oh ok! But I still got Nothing3. I print_r($result1_array) and got this:

Array ( [0] => Array ( [0] => 1 ) [1] => Array ( [0] => 2 ) [2] => Array ( [0] => 4 ) )

The code I tried:

$result1 = mysql_query("select pid from work_on where username = '$username'"); 
    if (!$result1 || mysql_num_rows($result1) == 0) 
	{ 
    	echo "Nothing1"; 
    	return false; 
	} 

//create an array of result1
$result1_array = array();
for ($count = 0; $row = mysql_fetch_row ($result1); ++$count) 
{
	$result1_array[$count] = $row;
}  

print_r($result1_array);
$result2 = mysql_query("select username from work_on where pid in ('$result1_array')"); 
if (!$result2) 
{ 
	echo "Nothing2"; 
	return false; 
} 

if (mysql_num_rows($result2) == 0) 
{ 
	echo "Nothing3"; 
	return false; 
}

[LordShryku]

Seems like the long way to get the arrat, but as-is, you can do this...

// replace
$result2 = mysql_query("select username from work_on where pid in ('$result1_array')");  

// with this
$sql = "SELECT username FROM work_on WHERE pid IN (";
for($i=0;$i<count($result1_array);$i++) {
   if($i==0) {
      $sql .= "'".$result1_array[$i][0]."'";
   }
   else {
      $sql .= ", '".$result1_array[$i][0]."'";
   }
}
$sql .= ")";
$result2 = mysql_query($sql); 

[rachelkoh]

Hey, THANKS you solve it! 🙂