How to write a SQL query with dynamic LIMIT

Question

SELECT * FROM user LIMIT (SELECT group_limit FROM groups WHERE groupid = 7471);

Answer 1

This is from the MySQL Database Knowledge base:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).

For your query to work, you will need to write it as a prepared statement, and then execute that.

SET @a = (SELECT group_limit FROM groups WHERE groupid = 7471);

PREPARE STMT FROM 'SELECT * FROM user LIMIT ?';
EXECUTE STMT USING @a;