How to run a sql query with a WHERE LIMIT using variables

Question

I need to run a SQL query with a where and a limit clause I seem to be having an issue with running when I include the where clause

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, bool given in C:\xampp\htdocs\Aerosa\functions\get_posts.php on line 30

the limit query runs as expected until I had a where clause

    <?php


$status = 'publish';
// define how many results you want per page
$results_per_page = 2;
// find out the number of results stored in database
$sql = "SELECT * FROM at_posts ";
$result = mysqli_query($conn, $sql);
$number_of_results = mysqli_num_rows($result);
// determine number of total pages available
$number_of_pages = ceil($number_of_results/$results_per_page);
// determine which page number visitor is currently on
if (!isset($_GET['page'])) {
  $page = 1;
} else {
  $page = $_GET['page'];
}






// determine the sql LIMIT starting number for the results on the displaying page
$this_page_first_result = ($page-1)*$results_per_page;
// retrieve selected results from database and display them on page
$sql='SELECT * FROM at_posts WHERE post_status = '.$status.' LIMIT ' . $this_page_first_result . ',' .  $results_per_page;
$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_array($result)) {
  echo $row['id'] . ' ' . $row['post_name']. '<br>';
}
echo '<nav aria-label="Page navigation">
        <ul class="pagination">';
// display the links to the pages
for ($page=1;$page<=$number_of_pages;$page++) {
  echo '<li class="page-item"><a class="page-link" href="?page=' . $page . '">' . $page . '</a></li>';
}
echo '  </ul>
      </nav>';
?>

Thanks beforehand.

with var dump i get string(60) "SELECT * FROM at_posts WHERE post_status = publish LIMIT 0,2" — Joshua Sigaban, Jun 24 '19 at 08:24
Actualy its not a duplicate, its question is not about sql-injection itself but his code not running. But of course his problem could be solved via preventing sql-injection. — J. Knabenschuh, Jun 24 '19 at 08:59
@j.knabenschuh The problem wouldn't be if they used prepared statements. Even if op didn't ask this question the answer is the same. — Dharman, Jun 24 '19 at 12:37
Of course, the problem is solved even if no code is written :-) Based on your statement, there is always only one question for a result. enlighten us all, what is the one question that gives 42? XD — J. Knabenschuh, Jun 25 '19 at 08:35

score -1 · Answer 1 · answered Jun 24 '19 at 08:26

Your query is in single quotes, which mean your variable $status is not interpreted as you expected and instead the string "$status" is sent to the SQL server which is an invalid query

Since your query query failed, mysqli_result returned FALSE instead of the result you expected.

score -1 · Answer 2 · answered Jun 24 '19 at 08:31

Your query failed, so mysqli_result returns FALSE instead of an result. Thats because you get your error message.

So why does your query fail? I think you have to qoute the "publish" value. Its no valid SQL. Following should be valid:

SELECT * FROM at_posts WHERE post_status = 'publish' LIMIT 0,2

so you have to write the following:

$sql='SELECT * FROM at_posts WHERE post_status = \''.$status.'\' LIMIT ' . $this_page_first_result . ',' .  $results_per_page;

PS: please inform yourself about SQL-Injection!

Thank you this worked ! and I agree this statement is not secure at all but I just wanted it to work before I change it into a prepared statement again thank you — Joshua Sigaban, Jun 24 '19 at 08:37

How to run a sql query with a WHERE LIMIT using variables

2 Answers2