MySQL LIMIT question

Question

I'm using pagination for my search results but for some reason my pagination is not working correctly with my search script. I can't seem to find the correct place to put the LIMIT $start, $display in my search query code where ever I put it in my code it displays the pagination all wrong. Can some one please help me?

Here is the part of my PHP & MySQL search code.

$mysqli = mysqli_connect("localhost", "root", "", "sitename");
mysqli_select_db($mysqli, "sitename");

    $search_explode = explode(" ", $search);

    foreach($search_explode as $search_each) {
        $x++;
        if($x == 1){
            $construct .= "article_content LIKE '%$search_each%' OR title LIKE '%$search_each%' OR summary LIKE '%$search_each%'";
        } else {
            $construct .= "OR article_content LIKE '%$search_each%' OR title LIKE '%$search_each%' OR summary LIKE '%$search_each%'";
        }

    }

$construct = "SELECT users.*, users_articles.* FROM users_articles
              INNER JOIN users ON users_articles.user_id = users.user_id
              WHERE $construct";
$run =  mysqli_query($mysqli, $construct);

$foundnum = mysqli_num_rows($run);

SQL statement.

SELECT users.*, users_articles.* FROM users_articles INNER JOIN users ON users_articles.user_id = users.user_id WHERE article_content LIKE '%find%' OR title LIKE '%find%' OR summary LIKE '%find%'OR article_content LIKE '%this%' OR title LIKE '%this%' OR summary LIKE '%this%'OR article_content LIKE '%article%' OR title LIKE '%article%' OR summary LIKE '%article%' LIMIT 0, 10

to add `$search_each = mysqli_real_escape_string($mysqli,$search_each);` would be nice. or use prepared statements — Your Common Sense, Jul 21 '10 at 06:31

score 2 · Answer 1 · answered Jul 21 '10 at 06:29

2

The basic syntax of SELECT is:

SELECT [fields]
FROM [tables [JOIN tables]]
WHERE [conditions]
GROUP BY [fields]
HAVING [conditions]
ORDER BY [fields]
LIMIT [limit]

So, right at the end after the WHERE conditions.

Here's the documentation which shows the full syntax.

answered Jul 21 '10 at 06:29

nickf

537,072
198
649
721

But where exactly in my code? I tried everything but with no success :( – lone Jul 21 '10 at 06:31
3

I don't believe you really tried it. He said right after the WHERE conditions, thats as simple as it can be explained. "WHERE $conditions LIMIT..." – enricog Jul 21 '10 at 06:34
I did it displays my pagination all wrong for example if I have 40 records it will only display one and not the rest. – lone Jul 21 '10 at 06:37
@lone: That looks like something in the `WHERE` clause is wrong. – Daniel Vassallo Jul 21 '10 at 06:41
@Daniel Vassallo any clues to what it is? – lone Jul 21 '10 at 06:44
@lone: Do you get all the results when you omit the `LIMIT` part? – Daniel Vassallo Jul 21 '10 at 06:45
@Daniel Vassallo for some reason it will only display 1 result when it should display about ten at a time. – lone Jul 21 '10 at 06:48
@Daniel Vassallo yes I get all the results when I leave out the LIMIT – lone Jul 21 '10 at 06:58
@lone What exactly are the values that are getting passed to the LIMIT ($start and $display)? – enricog Jul 21 '10 at 07:28
Ok that looks fine. Then maybe print out the whole SQL statements in both cases and add it to your original post. Maybe also the result sets (or a part of it if they are too big). – enricog Jul 21 '10 at 07:51

score 1 · Answer 2 · answered Jul 21 '10 at 08:40

Are you remembering to insert spaces into your string snippets as you build the query? Remember that PHP doesn't put anything into a string that you don't put there yourself.

$x = 'a';
$x .= 'b';

gives you ab, not a[space]b. Your $construct will be full of syntax errors if more than one search term is entered, as the subsequent OR will be attached directly to the end of any previous added search terms. You'll end up with something like:

[...snip...] OR summary LIKE '%$search_each%OR article_content LIKE[...snip...]
                                           ^^--syntax error here.

The same applies if you append the limit clause without making sure there's a space between the end of your query and the "LIMIT X,Y" text you append.

score 0 · Answer 3 · edited May 23 '17 at 12:30

0

This should show the first 10 rows:

SELECT      users.*, users_articles.* FROM users_articles
INNER JOIN  users ON users_articles.user_id = users.user_id
WHERE       article_content LIKE '%something%'
LIMIT       0, 10

Changing the LIMIT clause to the following should display rows from 11 to 20:

LIMIT       10, 10

Also note that you should probably escape your $construct string as @Col. Shrapnel suggested in a comment above. This is to avoid SQL injection.

In addition, you may want to investigate using Full Text Indexing instead.

edited May 23 '17 at 12:30

Community

1
1

answered Jul 21 '10 at 06:32

Daniel Vassallo

337,827
72
505
443

1

I'd add that LIMIT clause parameters should be explicitly cast/evaluated to int, to be protected from injection as well – Your Common Sense Jul 21 '10 at 06:38

score 0 · Answer 4 · answered Jul 21 '10 at 08:45

$mysqli = mysqli_connect("localhost", "root", "", "sitename");
mysqli_select_db($mysqli, "sitename");

    $search_explode = explode(" ", $search);

    foreach($search_explode as $search_each) {
        $x++;
        if($x == 1){
            $construct .= " article_content LIKE '%$search_each%' OR title LIKE '%$search_each%' OR summary LIKE '%$search_each%'";
        } else {
            $construct .= " OR article_content LIKE '%$search_each%' OR title LIKE '%$search_each%' OR summary LIKE '%$search_each%'";
        }

    }

$construct = "SELECT users.*, users_articles.* FROM users_articles
              INNER JOIN users ON users_articles.user_id = users.user_id
              WHERE $construct";
$construct .= " LIMIT 0, 10" ; // replace 0, 10 with your variables

$run =  mysqli_query($mysqli, $construct);

$foundnum = mysqli_num_rows($run);

MySQL LIMIT question

4 Answers4