CODEWITHSUNDEEP
phpsqlsymfonypdodoctrine-orm
Haruji Burke
Haruji Burke

Reputation: 459

Doctrine 2 can't run SELECT query issue

I have encountered this issue which I don't know why is happening. I code with dao.account.php like this:

$sql = " 
        SELECT last_ip
        FROM accounts 
        WHERE login IN (:strAcc) 
";
$strAcc = " \'acb\' , \'gfh\' ";
$pdo = $this->em->getConnection();
$stmt = $pdo->prepare($sql);
$stmt->bindValue('strAcc',$strAcc);         
$stmt->execute();               
$rs = $stmt->fetchAll();
return $rs;

But it returns array(0){}. I don't know why??? In my database, the query ran well. Could someone help me???

Upvotes: 2

Views: 139

Answers (2)

Sᴀᴍ Onᴇᴌᴀ
Sᴀᴍ Onᴇᴌᴀ

Reputation: 8297

Add a separate bound parameter for each value to be used in the IN () condition. That way, the comma separating the values is not part of the values, and there will be no quotes escaped. If you need to get the values from an array, you can iterate over the array, calling bindValue() for each value.

$sql = " 
    SELECT last_ip
    FROM accounts 
    WHERE login IN (:login1, :login2) 
";
$pdo = $this->em->getConnection();
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':login1',"acb");         
$stmt->bindValue(':login2',"gfh");         
$stmt->execute();               
$rs = $stmt->fetchAll();
return $rs;

Edit:

Per the comment about an unknown number of values, as I alluded to in my explanation above, one should be able to loop through values ( presuming they are in an array), and use implode() to add the parameter names to the query text, like the example below (one could also use array_map() with a callback that uses $stmt to call $stmt->bindValue() and returns the strings):

$pdo = $this->em->getConnection();
$parameterNames = array();
foreach ($values as $index => $value) {
    $parameterNames[] = ':login'.$index;
}
$sql = " 
SELECT last_ip
FROM accounts 
WHERE login IN (".implode(', ', $parameterNames).") ";
$stmt = $pdo->prepare($sql);
foreach ($values as $index => $value) {
    $stmt->bindValue(':login'.$index, $value);
}

Upvotes: 2

cn0047
cn0047

Reputation: 17091

You can use DQL:

$logins = ['acb', 'gfh'];
$dql = " 
        SELECT a.last_ip
        FROM Accounts a
        WHERE a.login IN (?1)
";
$q = $this->em->createQuery($dql)->setParameter(1, $logins);
$result = $q->execute();

Upvotes: 0

Related Questions