Build 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