PHP PDO Delete from array

Question

I have an array of GUIDs:

Array
(
    [0] => 1770302746_580821208_2068022554_1652435499
    [1] => 595039408_884272175_1344680100_1997277231
    ...
)

which I implode to a comma serparated string*:

1770302746_580821208_2068022554_1652435499,595039408_884272175_1344680100_1997277231

that I then pass to a sql DELETE statement:

$q=$conn->prepare(" DELETE FROM su_posts_att_sort WHERE FileGUID IN ({$GUIDs}) ");
$q->execute();

No rows are deleted however. Placing try / catch blocks to return any exception has not thrown any. Can anyone let me know where I'm going wrong?

*Have also tried with each GUID enclosed in double quotes.

EDIT Here is the submitted sql:

DELETE FROM su_posts_att_sort WHERE FileGUID IN ('1770302746_580821208_2068022554_1652435499','595039408_884272175_1344680100_1997277231')

Works fine through the console...

are you putting quotes around the ids? they are most likely considered strings which need to be quoted. — Jonathan Kuhn
– Jonathan Kuhn, Commented Jan 18, 2013 at 17:23
without quotes, those'll be interepted as wonky/bad integers, and mysql will be looking for 1770302746 and 595039408, ignoring the rest as garbage. — Marc B
– Marc B, Commented Jan 18, 2013 at 17:25
Have tried with the following, using both single and double quotes: $GUIDs = "\'".implode("\',\'", $_POST['arrDeleted'])."\'"; — Eamonn
– Eamonn, Commented Jan 18, 2013 at 17:26
@Eamonn Try echoing the resulting SQL and adding it to the question. — Joachim Isaksson
– Joachim Isaksson, Commented Jan 18, 2013 at 17:30

Spudley · Accepted Answer · 2013-01-18 18:55:15Z

2

You need to use single quotes, not double quotes, and certainly not unquoted -- they contain underscores, so they are definitely strings, not numerics.

But rather than just adding quotes, it would be better to use the PDO::quote method, as this will guarantee that they are using the right quoting, no matter what DB you're using, and no matter what weird characters might be lurking in the data.

You can apply PDO::quote to all the elements in the array in a single line of code, along with the implode() by using array_map(). Something like this:

$guids = implode(',', array_map(array($pdo,'quote'), $guidArray));

edited Jan 18, 2013 at 18:55

answered Jan 18, 2013 at 17:30

Spudley

169k39 gold badges240 silver badges309 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Eamonn Over a year ago

Have tried $GUIDs = implode(array_map(array($conn,'quote'), $_POST['arrDeleted'])); without effect. This looks a very efficient method however...

Eamonn Over a year ago

I notice that the resultant string is not comma separated, would it not need to be?

Joachim Isaksson Over a year ago

@Eamonn Yes, it needs to be comma separated.

Eamonn Over a year ago

@JoachimIsaksson How would I achieve this, using this method? It's not one I've used before...

Joachim Isaksson Over a year ago

@Eamonn If I remember correctly, just add a string with a comma, ',' as a first parameter, and it'll implode with a comma as separator.

|

Collectives™ on Stack Overflow

PHP PDO Delete from array

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related