Pulling ids from cookie, then using NOT IN sql query not working with variable

Question

I am setting a cookie everytime a user views an image, and then when they view another I modify that cookie to also have a value including the last image id viewed.

E.g.

First image: Cookie = 0;

For each after: Cookie = 0,3,4,6,1,44,2

etc.

I am then checking for that cookie, getting the value and trying to put into a query:

$value = 0;
// check for cookie
if ( isset($_COOKIE['viewed']) ) {
    $value = $_COOKIE['viewed'];
}

$stmt = $conn->prepare("SELECT * FROM images where id NOT IN (:viewed) ORDER BY rand() LIMIT 1");
$stmt->execute([':viewed' => $value]);

But this isn't taking any affect on the result returned.

If I hardcode the NOT IN to (1,2,4) then it will eliminate those results.

In my JSON return I check what the string is:

$return['cookie'] = $value;

And the value shows:

"cookie":"0,3,3,2,2,2,2,3,2,2,1,1,1,1,2,1,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,1,3,1,3,2,2,3,3,2,2,3,2,2,2,1"

How can I get this to work?

Have you checked the value of $value just before running the query to ensure it's got the correct data in it? — versalle88, Aug 26 '15 at 11:59
@versalle88 yes, die()'ing the $value returns e.g. 0,1,3,5,2,44,55 — Lovelock, Aug 26 '15 at 12:03
instead of $stmt->execute([':viewed' => $value]); try $stmt->execute(array(':viewed' => $value)); — Yogendra, Aug 26 '15 at 12:05
PDO Parameters cant be an array that way. See some answers here: http://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition — ThaDafinser, Aug 26 '15 at 12:06

Yogendra · Answer 1 · 2015-08-26T12:26:05.193

1

Try this:

$value = 0;
// check for cookie
if ( isset($_COOKIE['viewed']) ) {
    $value = $_COOKIE['viewed'];
}

$stmt = $conn->prepare("SELECT * FROM images where id NOT IN (:viewed) ORDER BY rand() LIMIT 1");
$stmt->execute(array(':viewed' => $value));

PDO::execute() only take value in array. see: http://php.net/manual/en/pdostatement.execute.php

edited Aug 26 '15 at 12:26

answered Aug 26 '15 at 12:11

Yogendra

2,139
3
14
27

score 0 · Answer 2 · answered Aug 26 '15 at 12:07

Probably thats due to the $value param itself. PHP is a loosely typed language

$stmt = $conn->prepare("SELECT * FROM images where id NOT IN (:viewed) ORDER BY rand() LIMIT 1");
$stmt->execute([':viewed' => $value]);
// Here $value is something not evaluated

Instead, do this

$valueArr = $value.explode(",");
$notIn = "";

for($i=0; $i<$valueArr.count(); $i++){
    $notIn .= $valueArr[$i]
}
$stmt = $conn->prepare("SELECT * FROM images where id NOT IN (:viewed) ORDER BY rand() LIMIT 1");
$stmt->execute([':viewed' => rtrim($notIn), ","]);

Pulling ids from cookie, then using NOT IN sql query not working with variable

2 Answers2