CODEWITHSUNDEEP
phpmysqlpdo
Nicholas Summers
Nicholas Summers

Reputation: 4756

Set a default LIMIT in PDO/MySQL when no LIMIT is set

I am trying to find out if it is possible to globally set and unset a default limit of results from MySQL in PHP.

Some pseudo code of what that might look like:

$pdo->prepare('SELECT * FROM example');
$pdo->setAttribute(PDO::DEFAULT_LIMIT, 1000); // max of 1000 rows returned
$pdo->exec();
$pdo->setAttribute(PDO::DEFAULT_LIMIT, 0); // no limit

// OR

$pdo->query('SET LIMIT_FOR_THIS_MYSQL_SESSION = 1000'); // max of 1000 rows returned
$pdo->prepare('SELECT * FROM example');
$pdo->exec();
$pdo->query('SET LIMIT_FOR_THIS_MYSQL_SESSION = 0');  // no limit

I am not sure if PDO/MySQL supports anything like this. If not is it possible to emulate this behavior? (Ideally without parsing the SQL in PHP, but I will take what I can get)

NOTE: In the actual code, PDO has a wrapper class for both connections and queries, so I can dynamically control what happens for every part of a query, and have easy access to the query string and query vars.

Context:

I am working on an API and we are trying to setup limits for how many results can be returned at once. Since there are dozens of endpoints, it would be a lot easier to be able to somehow globally set and unset a limit on queries.

Due to the customized nature of how the real code does queries, something like what is described above would be able to be implemented in just the PDO wrapper (1 file), rather than having to setup logic in each endpoint (each of which can be made up of several files) to dynamically add a LIMIT clause.

Upvotes: 1

Views: 1820

Answers (1)

Shadow
Shadow

Reputation: 34232

You can use MySQL's sql_select_limit setting to configure how many records a select should return. It's value can be set from the config file, dynamically from code within a session. Its value is overridden by any limit clause within the actual sql statement:

The maximum number of rows to return from SELECT statements. The default value for a new connection is the maximum number of rows that the server permits per table. Typical default values are (232)−1 or (264)−1. If you have changed the limit, the default value can be restored by assigning a value of DEFAULT.

If a SELECT has a LIMIT clause, the LIMIT takes precedence over the value of sql_select_limit.

Upvotes: 2

Related Questions