StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Your query ends with:</p> <pre><code>"LIMIT " . $page_number . ", $results_per_page" </code></pre> <p>But that's not how the LIMIT clause works in SQL. It's defined to be:</p> <pre><code>LIMIT offset, row_count </code></pre> <p><code>offset</code> is a row number, starting from 0. So if you do:</p> <pre><code>LIMIT 1, 10 </code></pre> <p>You'll get records 2 through 11, not 1 through 10. The correct way to do it is:</p> <pre><code>"LIMIT " . ($page_number-1)*$results_per_page . ", $results_per_page" </code></pre> <p>Something else you should find useful is the <code>SQL_CALC_FOUND_ROWS</code> option to the <code>SELECT</code> statement. This tells MySQL to calculate how many total rows matched the criteria, even though you used <code>LIMIT</code> to return just a subset. This way you don't have to do two queries, one with <code>COUNT(*)</code> and another to get a page of data. After doing a query with this option, you can use <a href="http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_found-rows" rel="nofollow"><code>SELECT FOUND_ROWS()</code></a> to get the full count.</p> <pre><code>$safequery = mysqli_real_escape_string($con,$query); $stmt = "SELECT SQL_CALC_FOUND_ROWS * FROM searchengine WHERE title LIKE '%" . $safequery . "%' OR keywords LIKE '%" . $safequery . "%' OR link LIKE '%" . $safequery . "%' LIMIT " . ($page_number-1)*results_per_page . " , $results_per_page"; $result = mysqli_query($con,$stmt) or die(mysqli_error($con)); $count_sql = "SELECT FOUND_ROWS() AS c"; $search_count = mysqli_fetch_array(mysqli_query($con,$count_sql)); $number_of_result = $search_count['c']; </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