How to build an IN() list for a stored procedure

from the Artful MySQL Tips List


Often we have to build a list of arguments to IN() on the fly. Here is a simple way to do it in PHP. We will test the method against a simple table of string arguments:

drop table if exists t;
create table t(c char(3));
insert into t values('abc'),('def'),('ghi'),('jkl');
select * from t;
+------+
| c    |
+------+
| abc  |
| def  |
| ghi  |
| jkl  |
+------+

We want the stored procedure to accept a string argument which is itself a list of arguments:

drop procedure if exists pin;
delimiter |
create procedure pin( vc varchar(80) )
begin
  set @sql = concat( 'select * from t where c in (', vc, ')' );
  prepare stmt from @sql;
  execute stmt;
  drop prepare stmt;
end;
|
delimiter ;


<?php
$conn
=mysqli_connect"localhost""USR""PWD""test" ) or die( mysqli_error($conn) );
$arg "'abc','def'";
$call "CALL pin("$arg")";
echo 
"Call is: "$call"<br/>";
$res mysqli_query$conn$call ) or die( mysql_error() );
echo 
"Result is "gettype$res ), ". Rows returned="mysqli_num_rows$res ), ". Result:<br/>";
while( 
$row mysqli_fetch_row$res )) echo $row[0], "<br/>";
mysqli_close$conn );
?>



The page displays:

Call is: CALL pin("'abc','def'")
Result is an object. Rows returned=2. Result:
abc
def


Return to the Artful MySQL Tips page