PHP Array as input to Stored Procedure

Question

This is what i am doing now : - in PHP

foreach($array as $value)
{
$query = select abc from tblname where colname =" .$value.
// fire query
}

then i create array of these values and display accordingly.

The PROBLEM: -

I have applied foreach, which fires the query every time it encounters a value in the array. result, if i have 10 values in my array it fires 10 queries. and uses network 10 times, result slow output.

What i want -

I want to give the array to a stored procedure which shall give me a resultset which will have the outputs corresponding to all the elements in the array.

I know this can be done but do not know how. the mysql doesnot take arrays as datatype.

the result shall be that network shall be used only once, despit of any number of values in the array.

LIKE -

StoredProcedure(inputMysqlARRAY)   // not possible, need a workaroung
{
// fire simple select(same) query for each value.
}

then call this stored procedure from PHP and input array. // need workaround.

possible duplicate of PHP PDO: Can I bind an array to an IN() condition? — Steve-o
– Steve-o, Commented Aug 11, 2011 at 9:33

John Green · Accepted Answer · 2011-08-11 09:21:57Z

1

You just have to be smarter about your calls. For instance, keeping cached DB objects around and that sort of thing.

Without knowing more about your code (your question is fairly garbled), it seems that if your query is something like this:

$query = "select abc from tblname where colname =" .$value; // run 10 times.

You really just need to write smarter code:

$values = array(); // Now, populate this array.  
                   // When you're done, run the query:
$query = 'select abc from tblname where colname IN (\''.implode('\',\'', $values).'\')';

Generally, we refer to this as Dynamic SQL and is the underpinning for how things are typically done today. A stored procedure (or, based on how I read your question, stored function) is useful at times, but is somewhat antiquated as a first-order methodology for interfacing with SQL. The DB guys still sometimes swear by it, but I think that even they are fairly well in consensus that smarter queries are always better.

answered Aug 11, 2011 at 9:21

John Green

13.5k3 gold badges31 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Saurabh Over a year ago

WOW.. gr8 solution.. y did i not think of it? Since, i also need to make sure that i am getting 1 result for each of these, i can count the no. of rows returned and compare it with the number of elements in the array and if its not equal i shall show an error. also, i can apply limit to the above query $query = 'select abc from tblname where colname IN (\''.implode('\',\'', $values).'\')' LIMIT . $countArrayValues.";"

John Green Over a year ago

Yeah, you can do all sorts of things when you iterate over the result set. Although... I don't see a reason to add a LIMIT here based on what I think you're trying to do with your code. You might consider an exit condition in your result parsing logic though.

Collectives™ on Stack Overflow

PHP Array as input to Stored Procedure

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related