Pdo stopping at first number in array and returning null
So i have it pdo
$emailLists = implode(",'",$emailListIds); $stmt = new Database(); $query = "SELECT es.ID AS SAVE_ID, es.SEND_DATE AS CREATED, es.NAME AS TITLE, es.CAMPAIGN_ID AS CAMPAIGN_ID FROM schedule_lists sl LEFT JOIN TXT2GIVE_EMAIL_SCHEDULE es ON (sl.email_save_id=es.ID) WHERE es.ID IN (:emailList) AND es.CHARITY_ID = :clientID GROUP BY es.ID"; $stmt->query( $query ); $stmt->bind( ':clientID', $clientId ); $stmt->bind( ':emailList', $emailLists ); $stmt->execute(); $result = $stmt->all();
This is one of those annoyances with PDO and prepared statements - you simply can't do what you want the way you want. The reason is down to the way parameters are passed into a prepared statement. Basically, it'll wrap it in quotes. The format for an IN clause is this:
WHERE IN (1,2,3,4,5)
However, when you pass the parameter in, it get's written like so:
WHERE IN ("1,2,3,4,5")
And that just won't work. There are a couple of workarounds. The first one relies on placeholders (?) rather than named parameters, and you dynamically create your SQL statement to contain as many place holders as there are in your array:
SELECT col1, col2, col3 FROM someTable WHERE id IN (?, ?, ?, ?, ?, ?) AND someCol = ?
The alternative is that you just don't use a parameter for the IN clause:
$sql = sprintf( "SELECT col1, col2, col3 * FROM someTable WHERE id IN (%s) AND someCol = :someVar", implode(',', $emailListIds) );
That will correctly create your SQL statement with a properly formatted IN clause, and use the named parameters for the other parts of the query. When you execute, you no longer need to pass in the $emailListIds because they're now hardcoded into the SQL
Darren
Hi,
I think the line below is the problem
$emailLists = implode(",'",$emailListIds);
it's putting a' in front of each number but not at the end '105, instead of '105',
Even if you wrap each element in quotes, the problem still exists, because you'd end up with:
IN (" '1', '2', '3', '4' ")
Alex Lord
ASKER
$emailLists = implode("','",$emailListIds); $stmt = new Database(); $query = printf("SELECT es.ID AS SAVE_ID, es.SEND_DATE AS CREATED, es.NAME AS TITLE, es.CAMPAIGN_ID AS CAMPAIGN_ID FROM schedule_lists sl LEFT JOIN TXT2GIVE_EMAIL_SCHEDULE es ON (sl.email_save_id=es.ID) WHERE es.ID IN ('%s') AND es.CHARITY_ID = :clientID GROUP BY es.ID",$emailLists ); $stmt->query( $query ); $stmt->bind( ':clientID', $clientId ); //$stmt->bind( ':emailList', $emailListIds ); $stmt->execute(); $result = $stmt->all();
this is what i did and im getting error on ajax return,
this is wat it is
"response text - ↵SELECT↵ es.ID AS SAVE_ID,↵ es.SEND_DATE AS CREATED,↵ es.NAME AS TITLE,↵ es.CAMPAIGN_ID AS CAMPAIGN_ID↵ FROM schedule_lists sl↵ LEFT JOIN TXT2GIVE_EMAIL_SCHEDULE es ON (sl.email_save_id=es.ID)↵ WHERE es.ID IN ('105','106','155','156','159','160','157','158','161','187','188','192','199','218','222','226','227') AND es.CHARITY_ID = :clientID↵ GROUP BY es.ID"
This is one of those annoyances with PDO and prepared statements - you simply can't do what you want the way you want. The reason is down to the way parameters are passed into a prepared statement. Basically, it'll wrap it in quotes. The format for an IN clause is this:
WHERE IN (1,2,3,4,5)
However, when you pass the parameter in, it get's written like so:
WHERE IN ("1,2,3,4,5")
And that just won't work. There are a couple of workarounds. The first one relies on placeholders (?) rather than named parameters, and you dynamically create your SQL statement to contain as many place holders as there are in your array:
SELECT col1, col2, col3 FROM someTable WHERE id IN (?, ?, ?, ?, ?, ?) AND someCol = ?
The alternative is that you just don't use a parameter for the IN clause:
$sql = sprintf( "SELECT col1, col2, col3 * FROM someTable WHERE id IN (%s) AND someCol = :someVar", implode(',', $emailListIds) );
That will correctly create your SQL statement with a properly formatted IN clause, and use the named parameters for the other parts of the query. When you execute, you no longer need to pass in the $emailListIds because they're now hardcoded into the SQL