1

I am using PDO to execute the following SQL query which is supposed to match up to 5 users with a single mod whose ID I have retrieved beforehand:

$sql =  'INSERT INTO modusergrade (`ModId`, `UserId`)
             SELECT :modId AS ModId, u.Id AS UserId
             FROM users AS u
             LEFT OUTER JOIN modusergrade AS g ON g.UserId = u.Id
             WHERE u.Id NOT IN
                              (SELECT UserId
                               FROM moduserconflicts
                               WHERE ModId = :modId)
             GROUP BY u.Id
             ORDER BY COUNT(g.ModId), rand()
             LIMIT :maxUsers';

$query = $db->prepare($sql);
$query->bindParam(':modId', $id, PDO::PARAM_INT);
$query->bindParam(':maxUsers', $maxUsers, PDO::PARAM_INT);
$query->execute();

However, executing this yields the following error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near "5" at line 11

Line 11 corresponds to the LIMIT, so I'm assuming that there is some kind of issue with rand(). Am I supposed to pass it some other way to get an "order randomly" effect?

filpa
  • 3,651
  • 8
  • 52
  • 91
  • Although not quite the same, the solution ended up being the same. I had retrieved `maxUsers` above and forgot to cast it to `int`, and kept on thinking that it surely is one. You can go ahead and submit that as an answer, and I'll gladly accept it. Thank you. – filpa Mar 30 '15 at 12:42
  • I'll leave you something tomorrow after my :LIMIT is back as an positiv integer. – Shaeldon Mar 30 '15 at 12:49

1 Answers1

1

As shown here the problem lies within the way pdo handles this parameterized Limit. You need to cast the value to an Integer (int) first before binding it.

Community
  • 1
  • 1
Shaeldon
  • 873
  • 4
  • 18
  • 28