Limit data selection isn't working for my pagination

Question

I have this code, but when I put the LIMIT data selection with the variables it won't work:

$sql = "SELECT *
        FROM structure
        WHERE row1 LIKE '%" . $_POST["query"] . "%'
        OR row2 LIKE '%" . $_POST["query"] . "%'
        OR row3 LIKE'%" . $_POST["query"] . "%'
        OR row4 LIKE'%" . $_POST["query"] . "%'
        OR row5 LIKE'%" . $_POST["query"] . "%'
        OR row6 LIKE'%" . $_POST["query"] . "% '
        LIMIT {$variable} , {$variable2}";

But if I put the same code, but changing the LIMIT data selection to numbers it will work:

$sql = "SELECT * FROM structure WHERE row1   LIKE '%".$_POST["query"]."%' OR row2 LIKE'%".$_POST["query"]."%' OR row3 LIKE'%".$_POST["query"]."%' OR row4 LIKE'%".$_POST["query"]."%' OR row5 LIKE'%".$_POST["query"]."%' OR row6 LIKE'%".$_POST["query"]."% ' LIMIT 0 , 5 ";

I need to use variables, since it won't be always constant.

EDIT: here is my entire code:

$record_per_page = 5;
if(isset($_POST['query'])) {
    $page = $_POST['query'];
    $start_from = ($page - 1) * $record_per_page;
    $sql = "SELECT *
            FROM structure
              WHERE row1 LIKE '%" . $_POST["query"] . "%'
              OR row2 LIKE '%" . $_POST["query"] . "%'
              LIMIT $start_from , $record_per_page";
    $result = mysqli_query($connect, $sql);
    if(mysqli_num_rows($result) > 0) {
        while($row = mysqli_fetch_array($result)) {
            $output .= ' ';
        }
        echo $output;
    }
}

use variable directly there:- `$sql = "SELECT * FROM structure WHERE row1 LIKE '%".$_POST["query"]."%' OR row2 LIKE'%".$_POST["query"]."%' OR row3 LIKE'%".$_POST["query"]."%' OR row4 LIKE'%".$_POST["query"]."%' OR row5 LIKE'%".$_POST["query"]."%' OR row6 LIKE'%".$_POST["query"]."% ' LIMIT $variable1 , $variable2";` — Alive to die - Anant, Oct 14 '17 at 09:36
Check that the two variables hold what your expecting ($variable1 & 2) — Nigel Ren, Oct 14 '17 at 09:52
yes they contain the pagination : $page = $_POST['query']; $start_from = ($page - 1)*$record_per_page; — Who iscoo, Oct 14 '17 at 18:06
what does this line of Your code: `$page = $_POST['query'];`??? — Salim Ibrohimi, Oct 22 '17 at 10:22
With this I wanted to get what the ajax function is getting from the search, and know how many "pages" contais the result — Who iscoo, Oct 22 '17 at 10:48
in this line: `$start_from = ($page - 1) * $record_per_page;` You subtracted `1` from `$page`, when Your `$page` variable is string??? — Salim Ibrohimi, Oct 22 '17 at 11:08
yes, i wanted to put ($_POST['query']), to get the number of pages that the result has. So imagine Im search for "text" and text has 100 entries in my data base, and the record per page is 5. so that will be 20 pages of result ( 100 / 5). — Who iscoo, Oct 22 '17 at 12:33
ok if `$_POST['query']` is number of entries, then what is that: ` WHERE row1 LIKE '%" . $_POST["query"] . "%' OR row2 LIKE '%" . $_POST["query"] . "%'`??? — Salim Ibrohimi, Oct 23 '17 at 03:21

score 0 · Accepted Answer · answered Oct 14 '17 at 10:57

0

Not enough rep to comment, sry. First read this if you're not already using PDO, especially the prepared statements section. https://phpdelusions.net/pdo

Next, select the columns you actually need, not a lazy select *. Also, could you post some your table definition? Curious to see what it is like.

And finally:

$query = "
SELECT *
FROM structure
WHERE row1 LIKE
    OR row2 LIKE ? 
    OR row3 LIKE ?
    OR row4 LIKE ?
    OR row5 LIKE ?
    OR row6 LIKE ?
LIMIT " . $variable1 . " , " . $variable2;

$stmt = $pdo->prepare($query);
$stmt->execute( ); //insert your parameters here

This should get you on your way =)

answered Oct 14 '17 at 10:57

Erik

384
1
9

Hello, what are the PDO used for? – Who iscoo Oct 14 '17 at 15:43
With the SELECT* I have selected all the rows i had in my table wich are as many as I show in the example – Who iscoo Oct 14 '17 at 15:44
PDO allows you to use prepared statements. Including user input directly into your query is a massive security risk. As far as doing `select *` is concerned, imagine you change the table later on to add columns, they will now automatically be included in that query. – Erik Oct 14 '17 at 21:18

Salim Ibrohimi · Answer 2 · 2017-10-22T11:09:12.260

0

You don't need curly braces in LIMIT:

$sql = "SELECT *
        FROM structure
        WHERE row1 LIKE '%" . $_POST["query"] . "%'
        OR row2 LIKE '%" . $_POST["query"] . "%'
        OR row3 LIKE'%" . $_POST["query"] . "%'
        OR row4 LIKE'%" . $_POST["query"] . "%'
        OR row5 LIKE'%" . $_POST["query"] . "%'
        OR row6 LIKE'%" . $_POST["query"] . "% '
        LIMIT " . $start_from. " , " . $record_per_page. "";

EDIT: print Your $variable & $variable2 for sure...

Change this line: $start_from = ($page - 1) * $record_per_page;

edited Oct 22 '17 at 11:09

answered Oct 14 '17 at 11:04

Salim Ibrohimi

1,351
3
17
35

hi, i tried not inserting curly braces but It still doen't work – Who iscoo Oct 14 '17 at 11:18
$result = mysqli_query($connect, $sql); if(mysqli_num_rows($result) > 0) { while($row = mysqli_fetch_array($result)) { $output .= ' '; } } echo $output; } else { echo 'Data Not Found'; } } ?> – Who iscoo Oct 14 '17 at 18:04
before the $sql variable – Who iscoo Oct 21 '17 at 08:59
these variables has value? – Salim Ibrohimi Oct 21 '17 at 09:03
$page = $_POST['query']; $start_from = ($page - 1)*$record_per_page; – Who iscoo Oct 21 '17 at 11:06
Dear friend, please if You can add You entire code! – Salim Ibrohimi Oct 21 '17 at 11:31
$record_per_page = 5; if(isset($_POST['query'])){ $page = $_POST['query']; $start_from = ($page - 1)*$record_per_page; $sql = "SELECT * FROM structure WHERE row1 LIKE '%" . $_POST["query"] . "%' OR row2 LIKE '%" . $_POST["query"] . "%' LIMIT {$start_from} , {$record_per_page}"; $result = mysqli_query($connect, $sql); if(mysqli_num_rows($result) > 0) { while($row = mysqli_fetch_array($result)) { $output .= ' '; } echo $output; – Who iscoo Oct 21 '17 at 13:33

Limit data selection isn't working for my pagination

2 Answers2