CODEWITHSUNDEEP
phpmysqlarrays
patrick.j.goodwin
patrick.j.goodwin

Reputation: 105

How can I return MySQL results with id's that do not exist in an array of already used id's?

I'm creating a web-based reading test. The student's receive a random reading passage, and once they answer the questions, the passage id and their student id is placed in a table that tracks which passages they have used.

I need to return the id's of the passages that have not already been used. I have been trying to use the NOT IN feature of MySQL.

$newpassage = "SELECT passName 
               FROM passages 
               WHERE passid NOT IN ('$passidarray');";

$passidarray is the array that contains the ids of the passages that have already been used. But every time, it still returns the passage id that has already been used. I hard coded the numbers to see if it would work and it does. But the passages they used grows the more they use the test.

For example, if there are three passages and they've only used 1, the result should return 2 and 3, but it is returning 1, 2, and 3. Then once they've used 2, it should only return 3. But it's returning all of them. Is there something that I am doing wrong. Any help would be greatly appreciated or even a better way of doing it.

Upvotes: 2

Views: 474

Answers (2)

deefour
deefour

Reputation: 35370

SELECT passName FROM passages WHERE passid NOT IN ('$passidarray')

You can't just embed an Array to a string like this. You need to convert the array of values into a string MySQL can understand. For example, if $passidarray is

array(1, 2, 3, 4)

you must implode() the array into a comma delimited string.

$passidstring = implode(',', $passidarray);
$newpassage = "SELECT passName FROM passages WHERE passid NOT IN ($passidstring);";

Upvotes: 0

amindfv
amindfv

Reputation: 8448

SQL doesn't use php's array object format. Try:

$newpassage = "SELECT passName FROM passages WHERE passid NOT IN (" . implode(',', $passidarray) . ");";

Upvotes: 1

Related Questions