PDO fetch returns nothing

Question

I've encountered a little problem. I have the following code:

    $query = $db->prepare('(SELECT last_visit, last_ip FROM user_log WHERE user_id = :id) 
                            UNION
                           (SELECT time AS last_visit, packet_hex AS last_ip FROM crack_log WHERE target_id = :id)
                            ORDER BY last_visit DESC LIMIT 0,10;');
    $query->execute(array(':id'=> $_SESSION['id']));
    $log = $query->fetchAll(PDO::FETCH_ASSOC); //Last visit/IP

    var_dump($log);

Which returns:

array(0) { }

I've tried the query in phpmyadmin and it worked fine. Can you please help me find the error?

I don't think you can bind a single parameter multiple times — Musa, Mar 04 '13 at 00:12
@Musa you were right, the multiple :id caused the problem, renaming the second one and adding it solved it. Thank you very much! — Chris Illusion, Mar 04 '13 at 00:15
@minitech I meant :id couldn't be used twice, according to Luc M answer that's the problem. — Musa, Mar 04 '13 at 00:19

Luc M · Accepted Answer · 2013-03-04T21:47:30.147

Accorrding to the documentation

You cannot use a named parameter marker of the same name twice in a prepared statement. You cannot bind multiple values to a single named parameter in, for example, the IN() clause of an SQL statement.

In you case, you should use something like

$query = $db->prepare('(SELECT last_visit, 
                               last_ip 
                        FROM user_log 
                        WHERE user_id = :id_1
                       ) 
                        UNION
                       (SELECT time AS last_visit,
                               packet_hex AS last_ip 
                        FROM crack_log 
                        WHERE target_id = :id_2
                       )
                       ORDER BY last_visit DESC LIMIT 0,10;'
                     );
    $query->execute(array(':id_1'=> $_SESSION['id'],
                          ':id_2'=> $_SESSION['id'] 
                         )
                   );

PDO fetch returns nothing

1 Answers1

Linked