Comparison operator IN

[nesvarbu]

I have a problem in arraya nd operator IN.
Here's peaces of my code:

<?php

$query = mysql_query("SELECT ID FROM $_SESSION[admin_module] where ldgroup= '$rez[ldgroup]' ");

$rezb[] = mysql_fetch_array($query);

$query2 = mysql_query ("SELECT * FROM stud where ID IN $rezb ") or die (MySql_Error());

?>

I have a mysql error smth about array. The problem is I dont know how to use comparison operator IN with ARRAY, and I dont know if my code is correct. SO if anybody can help please do.

[mattsinclair]

hmm, i don't know of an operator called IN

but if you're trying to search for rows where ID cotnains $rezb, use this

SELECT * FROM stud where ID LIKE '%$rezb%'

where % is a wildcard in SQL
and it's good practice to put tables and columns in ``

[mattsinclair]

also, i'm not sure if i'm understanding your code correctly... but..

$query = mysql_query("SELECT ID FROM $_SESSION[admin_module] where ldgroup= '$rez[ldgroup]' ");

you'll need to put $rez[ldgroup] in {} for it to give the variable

$query = mysql_query("SELECT ID FROM $_SESSION[admin_module] where ldgroup= '{$rez[ldgroup]}' ");

and.. i dont know if you can store or compare arrays in SQL. when you print $rez it should give you "Array"...so if you try to do:

SELECT * FROM stud where ID LIKE '%$rezb%'

It will be searching for the text "Array". You'll need to put an actual variable in there instead of referencing the entire array.

[nesvarbu]

Yep you're right. Now I'am trying to put all the $rezb values into one string but it's not that simple as it looks, or I'm too lame at php :/. Can you help me?

[nesvarbu]

I think I got it. Thanks for the help🙂

[mattsinclair]

not sure how the var is setup, but if you're trying to see if every value of $rezb is in ID you could do something like this,

$query = "SELECT * FROM stud where 1=1";
foreach ($rezb as $k => $v) { $query .= " AND `ID` LIKE %$v%"; }

what that does is appends each value in $rezb to a seperate LIKE command, so it'll keep narrowing the field until you get what you're looking for

[Moonglobe]

[man]implode[/man]:

$sql = "SELECT * FROM `stud` where 1=1 AND WHERE `ID` LIKE '%";
$sql .= implode("%' AND `ID` LIKE '%",$rezb)."%'";

[mattsinclair]

wow, implode looks better than foreach()... do you know if it's faster at imploding a large array ? I would assume so but you never know

[ahundiak]

The original post was about using the sql IN operator which somehow got diverted into using LIKE. IN takes a comma delimited list of values. For example:

SELECT * FROM stud WHERE id IN (23,45,77);

To generate a list of ids from a query do something like:

$result = mysql_query("SELECT id FROM whatever");
$ids = array();
while($record = mysql_fetch_assoc()) {
  $ids[] = $record['id'];
}
$ids = implode(',',$ids);
$sql = "SELECT * FROM stud WHERE id IN ($ids)";

Make sure you have at least one ID as mysql will generate an error for an empty list.

[nesvarbu]

I did it like this:

<?php

$sentence = ' ';
	while ($rezb = mysql_fetch_row($query))
	{
	$sentence = $sentence.$rezb[0].' , '; // place all the $rezb values into $sentence seperated by commas
	}
							$sentence= strrev(substr(strrev($sentence),2)); // eliminating the last comma

$query2 = mysql_query ("SELECT * FROM stud WHERE ID IN ($sentence) order by surname "); 

?>

[Moonglobe]

Originally posted by nesvarbu
$sentence= strrev(substr(strrev($sentence),2));

$sentence = substr($sentence,0,-1);

[nesvarbu]

Thanks I'll do that.