PHP MySQL - select where id not equal to two one of 2 ids

Question

So I'm trying to pick 2 rows randomly but the id isn't allowed to be equal to x or equal to y.

That's what I tried so far:

  $statment = $db -> "SELECT * FROM player ORDER BY RAND() LIMIT 2 WHERE 
              NOT(id = ?) OR WHERE NOT(id=?)";
  $statement->execute(array($player1, $player2));

But getting:

Parse error: syntax error, unexpected '"SELECT * FROM player ORDER BY' (T_CONSTANT_ENCAPSED_STRING), expecting identifier (T_STRING) or variable (T_VARIABLE)

Parse error: syntax error, unexpected '"SELECT * FROM player ORDER BY' (T_CONSTANT_ENCAPSED_STRING), expecting identifier (T_STRING) or variable (T_VARIABLE) — Ibrahim, Oct 13 '17 at 18:43
Um, you have a string where a method (probably `prepare()`)should be. How has no one spotted this yet???? — John Conde, Oct 13 '17 at 18:51
may i ask what is $db? localhost,username,password,database? and what do you have written in your code where you have the questionmark? — Jurick Pastechi Genaro, Oct 13 '17 at 18:53
Fatal error: Uncaught Error: Call to a member function execute() on null in — Ibrahim, Oct 13 '17 at 18:58
ok thank you guys. It works now. I changed the sql code, added the prepare method and renamed $statment to $statement — Ibrahim, Oct 13 '17 at 19:00
am wondering if all this was the wrong variable to start with. — Funk Forty Niner, Oct 13 '17 at 19:02

AbraCadaver · Accepted Answer · 2017-10-13T19:04:30.213

5

You need AND to make sure it is not equal <> to both:

WHERE id <> ? AND id <> ?

Or check if it is not IN a list:

WHERE id NOT IN(?, ?)

And I'm not sure about the order of the SQL statement, try:

SELECT * FROM player WHERE id <> ? AND id <> ?
         ORDER BY RAND() LIMIT 2

Also, you're missing something like maybe prepare:

$statement = $db->prepare("SELECT * FROM player WHERE id <> ? AND id <> ?
                                   ORDER BY RAND() LIMIT 2");

edited Oct 13 '17 at 19:04

answered Oct 13 '17 at 18:36

AbraCadaver

78,200
7
66
87

still getting : Parse error: syntax error, unexpected '"SELECT * FROM player ORDER BY' (T_CONSTANT_ENCAPSED_STRING), expecting identifier (T_STRING) or variable (T_VARIABLE) – Ibrahim Oct 13 '17 at 18:38
So you didn't mention that. Your object syntax is wrong. – AbraCadaver Oct 13 '17 at 18:40
[you missed this..........](https://stackoverflow.com/questions/46736093/php-mysql-select-where-id-not-equal-to-two-one-of-2-ids#comment80419617_46736093) – Funk Forty Niner Oct 13 '17 at 19:01
Damn, I knew this question was trouble from the start :-( – AbraCadaver Oct 13 '17 at 19:04
.................yep. First thing I spotted. – Funk Forty Niner Oct 13 '17 at 19:05

Barmar · Answer 2 · 2017-10-13T19:05:11.633

-1

Use NOT IN. You're also missing the call to prepare().

$statment = $db ->prepare("SELECT * FROM player
                    WHERE id NOT IN (?, ?)
                    ORDER BY RAND()
                    LIMIT 2");

Also, the order of clauses in a SELECT statement is:

SELECT
FROM
JOIN
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

edited Oct 13 '17 at 19:05

answered Oct 13 '17 at 18:37

Barmar

741,623
53
500
612

Parse error: syntax error, unexpected '"SELECT * FROM player ' (T_CONSTANT_ENCAPSED_STRING), expecting identifier (T_STRING) or variable (T_VARIABLE) or '{' or '$' in – Ibrahim Oct 13 '17 at 18:41
Not my dv here, but [you missed this..........](https://stackoverflow.com/questions/46736093/php-mysql-select-where-id-not-equal-to-two-one-of-2-ids#comment80419617_46736093). – Funk Forty Niner Oct 13 '17 at 19:01

PHP MySQL - select where id not equal to two one of 2 ids

2 Answers2