2

I have a query which is not constant all the time ..., In fact it will be generate according to some factor. And it is sometimes like this:

select * from table1 where id = :id

And sometimes else it could be like this:

select * from table1 where id = :id
    union all
select * from table2 where id = :id

And sometimes else it maybe be like this:

select * from table1 where id = :id
    union all
select * from table2 where id = :id
    union all
select * from table3 where id = :id

Already: I used PHP version 5.2.6 so far and it was completely fine. I mean is, I just passed one time :id parameter and I could use it for multiple times in the query. Just this:

$stm->bindValue(':id', $id, PDO::PARAM_INT);

This ^ was good for all those above queries.

Now: I have updated my PHP version (my current PHP version is 5.6.8). Well, As you know, in the new version, I cannot do that like former. I need to pass a separated parameter for every time that I want to use it in the query. like this:

For query1:

$stm->bindValue(':id', $id, PDO::PARAM_INT);

For query2:

$stm->bindValue(':id1', $id, PDO::PARAM_INT);
$stm->bindValue(':id2', $id, PDO::PARAM_INT);

For query3:

$stm->bindValue(':id1', $id, PDO::PARAM_INT);
$stm->bindValue(':id2', $id, PDO::PARAM_INT);
$stm->bindValue(':id3', $id, PDO::PARAM_INT);

So given that I don't know how many times I need to pass :id parameter to the query (because my query is dynamic), How can I solve this problem?

Community
  • 1
  • 1
stack
  • 10,280
  • 19
  • 65
  • 117
  • You could use [PDOStatement::debugDumpParams](http://php.net/manual/en/pdostatement.debugdumpparams.php) to find your parameters, regex param name out and use them in bindValue – zedfoxus Dec 31 '15 at 13:09
  • @zedfoxus Honestly I read what you linked twice but still I cannot understand what that function does ... – stack Dec 31 '15 at 13:19
  • Let me see if I can build an example with a similar query as yours to help understand it better. – zedfoxus Dec 31 '15 at 13:33
  • As I started creating an example, I realized that my understanding of debugDumpParams was incorrect and that it will not solve your problem. So I apologize for steering you in the wrong direction. maxhb's answer is decent. How are you creating the dynamic query? Are you looping through variables or doing if..then to build your query? – zedfoxus Dec 31 '15 at 13:56
  • @zedfoxus ah I see, and no need to apologize ... ! yes maybe I use maxhb's answer. anyway ok thanks for your attention. – stack Dec 31 '15 at 14:24

1 Answers1

1

You could store all id values in an array and iterate over it.

This will work as long as all values are of same type (int) and your place holders are of the form :idNUMBER:

$ids = array('what','ever');
for($c=0;$c<count($ids);$c++) {
  $stm->bindValue(':id' . ($c+1), $ids[$c], PDO::PARAM_INT);
}

Not very elegant but will do the job.

A solution to even handle different types is described at http://www.pontikis.net/blog/dynamically-bind_param-array-mysqli

maxhb
  • 8,554
  • 9
  • 29
  • 53
  • emm, not bad ...! However I think I need to handle those `:ParameterName` in the query too. Anyway ok thanks :-) – stack Dec 31 '15 at 12:54
  • 1
    Take a look at the link I posted. If you can assemble an array which holds information about name, type and value of your parameters then you can adopt the code to your needs. – maxhb Dec 31 '15 at 13:03