Retrive an array stored in database using PDO

[hamedghaderi]

I have an array:

 productid = [1,2] 

. Now I want to fetch data from product table by using this part of code:

$sql = 'SELECT name FROM product WHERE id=:id';
$s = $pdo->prepare($sql);
foreach($productid as $id)
{
  $s->bindValue(':id', $id);
  $s->execute();
}

when I returned the names as follow:

foreach($s as $row)
{
  $name[] = array(
      'name' => $row['name']
  );
}

I just got the product name of second id and didn't get both names. What's the problem?

[r937]

when there is more than one value, use IN --

WHERE product_id IN ( 9, 37 )

the nice thing is, this also works with just one value --

WHERE product_id IN ( 937 )

[StarLion]

Just keep in mind that you'll likely need to dynamically create the query string, as was discussed recently.

As for why you only got the second value; your string has 1 parameter; $id.
You loop through your array - every time you do, you write the value of $id... and overwrite the previous value.
When you get to the end of the loop, $id has been bound to the last value it was assigned - which would be the last value of the array.

[hamedghaderi]

and how can I convert an array to some a list of integer values?

[hamedghaderi]

Just keep in mind that you'll likely need to dynamically create the query string, as was discussed recently.

As for why you only got the second value; your string has 1 parameter; $id.
You loop through your array - every time you do, you write the value of $id... and overwrite the previous value.
When you get to the end of the loop, $id has been bound to the last value it was assigned - which would be the last value of the array.

Thanks, but i didn't get the idea? how can i fix this problem?

[StarLion]

This question has ventured more into the PHP side of things than the database, so i'm going to flag it for moving;

The idea is simple;
Taking what r937 explained to you above (using IN), and applying a bit of PHP to make it dynamic to the size of your array.
You want a PDO query that looks (at the end) something like:

SELECT name FROM product WHERE id IN(?,?)

but, you cant just put ?,?, because what if the array has 12 elements in it? cant put 12 items in 2 spots.
So we need to be a bit more creative than a static string.
Give this a try yourself;
Write a script that will create your query string, based on the number of elements in $productid.
(Hints: count,array_fill,implode,str_pad are all possible helpers here)

[hamedghaderi]

This question has ventured more into the PHP side of things than the database, so i'm going to flag it for moving;

The idea is simple;
Taking what r937 explained to you above (using IN), and applying a bit of PHP to make it dynamic to the size of your array.
You want a PDO query that looks (at the end) something like:

SELECT name FROM product WHERE id IN(?,?)

but, you cant just put ?,?, because what if the array has 12 elements in it? cant put 12 items in 2 spots.
So we need to be a bit more creative than a static string.
Give this a try yourself;
Write a script that will create your query string, based on the number of elements in $productid.
(Hints: count,array_fill,implode,str_pad are all possible helpers here)

Thanks. The answer was so helpful

[Cups]

This question has ventured more into the PHP side of things than the database, so i'm going to flag it for moving;

The idea is simple;
Taking what r937 explained to you above (using IN), and applying a bit of PHP to make it dynamic to the size of your array.
You want a PDO query that looks (at the end) something like:

SELECT name FROM product WHERE id IN(?,?)

but, you cant just put ?,?, because what if the array has 12 elements in it? cant put 12 items in 2 spots.
So we need to be a bit more creative than a static string.
Give this a try yourself;
Write a script that will create your query string, based on the number of elements in $productid.
(Hints: count,array_fill,implode,str_pad are all possible helpers here)

Actually that is quite a common PDO pattern ... do others have any trick way of handling this?

[hamedghaderi]

This question has ventured more into the PHP side of things than the database, so i'm going to flag it for moving;

The idea is simple;
Taking what r937 explained to you above (using IN), and applying a bit of PHP to make it dynamic to the size of your array.
You want a PDO query that looks (at the end) something like:

SELECT name FROM product WHERE id IN(?,?)

but, you cant just put ?,?, because what if the array has 12 elements in it? cant put 12 items in 2 spots.
So we need to be a bit more creative than a static string.
Give this a try yourself;
Write a script that will create your query string, based on the number of elements in $productid.
(Hints: count,array_fill,implode,str_pad are all possible helpers here)

OK I write a function to create dynamic ? needed inside IN()
that's it :

create_question_mark($productid);
function create_question_mark($arr)
    {
        $string = '';
        for($i=0; $i<count($arr); $i++)
        {
            $string .= "? ";
            if($i<(count($arr)-1))
            {
                $string .= ", ";
            }
        }
        return $string;
    }

and this is my sql query:

$sql = "SELECT persian_name FROM product WHERE 
         id IN(" . create_question_mark($productid) . ")";

now the question is how to bind values to this question marks?

[StarLion]

Since Cups has asked, Here's how i would do it. the way the OP came up with works just as well.

The Query string itself, before it's Prepared, -is just a string-. So standard PHP foolery will work on it;

$query = "SELECT name FROM product WHERE id IN(".implode(',',array_fill(0,count($productid),'?')).")";
$pdo = $db->prepare($query); 

Will get you your query string.

Now, because we're using ?'s instead of named ID's, and we have no preceding question marks (that part is very important), we can supply a numerically indexed array to fill the question marks when we bind to PDO....

$bind = 1; //NOTE: Binding ?'s begins at index 1, NOT 0.
foreach($productid AS $pid) {
 $pdo->bindValue($bind,$pid);
 $bind++;
} 

if we DID have preceding question marks, we would have to take that into account with the value of $bind.

[StarLion]

slight edit: since you're assigning integers, you should specify that when binding the value;

$pdo->bindValue($bind,$pid,PDO::PARAM_INT);

[hamedghaderi]

slight edit: since you're assigning integers, you should specify that when binding the value;

$pdo->bindValue($bind,$pid,PDO::PARAM_INT);

Yes, that's right thanks. But I think it works properly without hinting PDO::PARAM_INT

[oddz]

In theory if your dealing with integers you can just cast the strings to ints and not worry binding. Either that or you could go complex route suggested. Either way would work and prevent the same thing. The solution doesn't *always have to be prepared statements with variable binding. It just depends on what type of data your dealing with. If you could have any string than you would have to go the complex route.

$insecure = array('1','2','this would mess up your stuff');
$secure = array();

foreach($insecure as $id) {
  $secure[] = (int) $id;
}

$query = 'SELECT * FROM whatever WHERE field in ('.implode(',',$secure).')'; 

[StarLion]

Be careful there oddz. You just modified rows 1, 2 and 0 in your query - sanitization of the data should remove that value entirely, not reduce it to 0

[Cups]

So the idea of coming up with a handy function(s) which handles this fairly common pattern - might seem YAGNI or a level of bother / data hiding not really worth bothering with then ... still,

seeing as we already have the possibility of:

a) offset = 2 - i.e. already has one preceding "?" placeholder

...
WHERE string_type = ?
AND id IN(?, ?, ?)

the IN() clause kinda suggests it is integer based, but as said, it might not be ...

...
WHERE id > ?
AND string_type IN(?, ?, ?)

b) escape_type = STRING

Having said that I think almost all of my real world PDO interactions are mostly encapsulated in other (fairly generic) classes which are passed a PDO connection.

EDIT Mysql IN() clause works with strings, to be honest it never crossed my mind to use it that way.