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( mysql_error() );
$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
|
|