We help IT Professionals succeed at work.

Pdo stopping at first number in array and returning null

Alex Lord
Alex Lord asked
on
100 Views
Last Modified: 2018-08-24
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();

Open in new window


it keeps returning null and i think i know why.

it seems to be going into line
WHERE es.ID IN (:emailList)

Open in new window

and only going to the first id.

which is this here - View of array
also note this is how the array is created -

foreach($result as $emailSaveID){
			if(!empty($emailSaveID)):
				$emailListIds[] = $emailSaveID['EMAIL_SAVE_ID'];
			endif;
		}

Open in new window


what can i do to solve this ?
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Hey Alex,

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
DarrenProject Manager / Technical Lead
CERTIFIED EXPERT

Commented:
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',

Just a thought,

Darren

Author

Commented:
Thx for the response i will give these a go
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Even if you wrap each element in quotes, the problem still exists, because you'd end up with:

IN (" '1', '2', '3', '4' ")

Author

Commented:
$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();

Open in new window


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"

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
lol chris i changed to spriint lol my mistake all works correctly
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Good news Alex :)

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions