StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow can I remove everything past "LIMIT" from a query? (details inside)
text
Body
copied!<p>I got a few queries built dynamically by my scripts. They usually fit the following template:</p> <pre><code>SELECT ... FROM ... JOIN ... WHERE ... (lots of filters, search conditions, etc. here) ... ORDER BY (optional) ... LIMIT (optional) ... OFFSET (optional) </code></pre> <p>I want to remove the LIMIT and OFFSET parts from the query. I used</p> <pre><code>$sql_entitati = implode("LIMIT", explode("LIMIT", $sql_entitati, -1)); </code></pre> <p>to do it but then it hit me: what if there's no LIMIT in the query and what if the only LIMIT is somewhere in the where clauses?</p> <p>So my question to you is: How can I safely remove everything after the LIMIT key word, without screwing it up if there's no LIMIT and/or there's a "LIMIT" somewhere in the where clause? All this done in php.</p> <p>A bit of an edit for clarity:</p> <p>the algorithm i use:</p> <pre><code>$sql = implode("LIMIT", explode("LIMIT", $sql, -1)); </code></pre> <p>Will work on 99% of the cases. The problem occurs when the "LIMIT" key word at the end is missing, AND there is "LIMIT" written somewhere in the conditions. for example:</p> <pre><code> SELECT * FROM table WHERE bla = 'SPEED LIMIT' ORDER BY table.a </code></pre> <p>this is the problem i need to tackle.</p> <p>Solved using the following algorithm (Credit to techfoobar):</p> <pre><code> $p = strrpos($sql, "LIMIT"); if($p !== false) { $q = strpos($sql, ")", $p); $r = strpos($sql, "'", $p); $s = strpos($sql, "\"", $p); if($q === false && $r === false && $s === false) $sql = substr($sql, 0, $p); } </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload