SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Member hamedghaderi's Avatar
    Join Date
    Dec 2012
    Location
    Sanandaj, Kurdestan, Iran
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Retrive an array stored in database using PDO

    I have an array:
    PHP Code:
     productid = [1,2
    . Now I want to fetch data from product table by using this part of code:

    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:

    Code:
    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?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    when there is more than one value, use IN --
    Code:
    WHERE product_id IN ( 9, 37 )
    the nice thing is, this also works with just one value --
    Code:
    WHERE product_id IN ( 937 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    70 Post(s)
    Tagged
    0 Thread(s)
    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.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  4. #4
    SitePoint Member hamedghaderi's Avatar
    Join Date
    Dec 2012
    Location
    Sanandaj, Kurdestan, Iran
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    and how can I convert an array to some a list of integer values?

  5. #5
    SitePoint Member hamedghaderi's Avatar
    Join Date
    Dec 2012
    Location
    Sanandaj, Kurdestan, Iran
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    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?

  6. #6
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    70 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    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)
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  7. #7
    SitePoint Member hamedghaderi's Avatar
    Join Date
    Dec 2012
    Location
    Sanandaj, Kurdestan, Iran
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    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:

    Code:
    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

  8. #8
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by StarLion View Post
    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:

    Code:
    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?

  9. #9
    SitePoint Member hamedghaderi's Avatar
    Join Date
    Dec 2012
    Location
    Sanandaj, Kurdestan, Iran
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    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:

    Code:
    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 :

    Code:
    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:
    Code:
    $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?

  10. #10
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    70 Post(s)
    Tagged
    0 Thread(s)
    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;

    PHP Code:
    $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....
    PHP Code:
    $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.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  11. #11
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    70 Post(s)
    Tagged
    0 Thread(s)
    slight edit: since you're assigning integers, you should specify that when binding the value;

    $pdo->bindValue($bind,$pid,PDO::PARAM_INT);
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  12. #12
    SitePoint Member hamedghaderi's Avatar
    Join Date
    Dec 2012
    Location
    Sanandaj, Kurdestan, Iran
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    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

  13. #13
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    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.

    PHP Code:

    $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).')'
    The only code I hate more than my own is everyone else's.

  14. #14
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    70 Post(s)
    Tagged
    0 Thread(s)
    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
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  15. #15
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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

    Code:
    ...
    WHERE string_type = ?
    AND id IN(?, ?, ?)
    the IN() clause kinda suggests it is integer based, but as said, it might not be ...

    Code:
    ...
    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.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •