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

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

$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);