CODEWITHSUNDEEP
phpmysqlsql
Anton Raskin
Anton Raskin

Reputation: 143

Pass an array as parameters to SQL procedure

I have SQL procedure in which I'm using an IN statment. It goes like this: 

SELECT * FROM costumers WHERE id IN('1','2','12','14')

What I need to do is pass the values in to the IN statment as parameter which is an array in php, rather than hard-coded. How can I do that?

Upvotes: 2

Views: 3683

Answers (6)

jmleroux
jmleroux

Reputation: 947

implode() is the right function, but you also must pay attention to the type of the data.

If the field is numeric, it is simple:

$values = array(1. 2, 5);
$queryPattern = 'SELECT * FROM costumers WHERE id IN(%s)';
$query = sprintf($queryPattern, implode(', ',$values));

But if it's a string, you must play with single and double quotes:

$values = array("foo","bar","baz");
$queryPattern = 'SELECT * FROM costumers WHERE id IN("%s")';
$query = sprintf($queryPattern, implode('", "',$values));

Upvotes: 1

davur
davur

Reputation: 1438

use PHP's join function to join the values of an array.

$arr = array(1,2,12,14);

$sql = "SELECT * FROM costumers WHERE id IN(" . join($arr, ',') . ")";

Upvotes: 0

Kevin
Kevin

Reputation: 41903

You can implode on this case:

$array = array('1','2','12','14');
$ids = "'".implode("','", $array) . "'";
$sql = "SELECT * FROM `costumers` WHERE `id` IN($ids)";
echo $sql;
// SELECT * FROM `costumers` WHERE `id` IN('1','2','12','14')

or if you do not want any quotes:

$ids = implode(",", $array);

Upvotes: 3

Bobby Russell
Bobby Russell

Reputation: 475

Try imploding the php into an array, and then interpolating that string into the SQL statement:

$arr = array('foo', 'bar', 'baz');
$string = implode(", ", $arr);

SELECT * FROM customers WHERE id in ($string);

Upvotes: 0

Rab
Rab

Reputation: 35582

You can use PHP function Implode 

$array = array("1","2","12","14");

$query = "SELECT * FROM costumers WHERE id IN(".implode(', ',$array).")"

Upvotes: 1

JasonK
JasonK

Reputation: 5294

This should do the trick

$array = array('1','2','12','14');

SELECT * FROM `costumers` WHERE `id` IN('{$array}');

Upvotes: 0

Related Questions