SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Guru bronze trophy TomB's Avatar
    Join Date
    Oct 2005
    Location
    Milton Keynes, UK
    Posts
    988
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    PDO use parameter twice?

    If I run this:

    PHP Code:
    $values = array('foo' => 'foo''bar' => 'bar');
    $sth $pdo->prepare('SELECT :foo, :bar, :foo');
    $sth->execute($values);
    print_r($sth->fetchAll()); 
    the second :foo is 0:

    Code:
    Array
    (
        [0] => Array
            (
                [?] => 0
                [0] => foo
                [1] => bar
                [2] => 0
            )
    
    )
    (I also don't know why there's a ? index, but there always seems to be.)


    Is there any way to get PDO to actually recognise the variables multiple times?

    Specifically I want to do:

    Code:
    INSERT INTO table foo = :foo, bar = :bar ON DUPLICATE KEY UPDATE foo = :foo, bar = :bar
    It works for inserts, but all the updates fill the record with zeros.

  2. #2
    SitePoint Zealot
    Join Date
    Apr 2010
    Posts
    106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $values = array(':foo' => 'foo'':bar' => 'bar'); 

  3. #3
    SitePoint Guru bronze trophy TomB's Avatar
    Join Date
    Oct 2005
    Location
    Milton Keynes, UK
    Posts
    988
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)
    Prefixing values with : makes 0 difference. Output is still:

    PHP Code:

    Array
    (
        [
    0] => Array
            (
                [?] => 
    0
                
    [0] => foo
                
    [1] => bar
                
    [2] => 0
            
    )



  4. #4
    . shoooo... silver trophy logic_earth's Avatar
    Join Date
    Oct 2005
    Location
    CA
    Posts
    9,013
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    http://us3.php.net/manual/en/pdo.prepare.php

    You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute(). You cannot use a named parameter marker of the same name twice in a prepared statement.
    Logic without the fatal effects.
    All code snippets are licensed under WTFPL.


  5. #5
    SitePoint Guru bronze trophy TomB's Avatar
    Join Date
    Oct 2005
    Location
    Milton Keynes, UK
    Posts
    988
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)
    well that just sucks, what's the point in using a named parameter then?

  6. #6
    . shoooo... silver trophy logic_earth's Avatar
    Join Date
    Oct 2005
    Location
    CA
    Posts
    9,013
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    It has to do with the fact the PDO has to convert named parameters to the "?" question marks for MySQL. MySQL doesn't understand named parameters.
    Logic without the fatal effects.
    All code snippets are licensed under WTFPL.


  7. #7
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    664
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by TomB View Post
    well that just sucks, what's the point in using a named parameter then?
    Just wait until you want to pass an array to a WHERE id IN ( :ids) statement. Can't be done cleanly.

    I think the named parameters stuff is more of a SQL Prepared statement limit as opposed to a php restriction. Might be wrong.

    It's actually fairly easy to do a bit of processing on your sql statement and implement the functionality you want. I always wrap the PDO object just so I can tweak this stuff.

  8. #8
    SitePoint Guru bronze trophy TomB's Avatar
    Join Date
    Oct 2005
    Location
    Milton Keynes, UK
    Posts
    988
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by ahundiak View Post
    Just wait until you want to pass an array to a WHERE id IN ( :ids) statement. Can't be done cleanly.

    I think the named parameters stuff is more of a SQL Prepared statement limit as opposed to a php restriction. Might be wrong.

    It's actually fairly easy to do a bit of processing on your sql statement and implement the functionality you want. I always wrap the PDO object just so I can tweak this stuff.
    Yes that's what I've resorted to doing... why can't PDO do this internally though? Makes no sense that it doesn't.


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
  •