Query will not work. Mind Taking a look at it?

Question

I keep getting:

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in

I've listed the sql in question below and am wondering what im doing wrong ($status is a string that just says "Form Submitted)

$sql= "SELECT * FROM `veggie` ORDER BY `buy_date` desc LIMIT 20 OFFSET 0 WHERE `status` = '$status' ";

edit: can do. line 50 and 84

<?php
require_once $_SERVER['DOCUMENT_ROOT'].'/system/init.php';
if (!is_logged_in()) {
  login_error_redirect();
}
include 'includes/head.php';
include 'includes/navigation.php';

$page='';
$status = "Form Submitted";
if (!isset($_GET['page'])){
  $sql= "SELECT * FROM `veggie` ORDER BY `buy_date` desc LIMIT 20 OFFSET 0 WHERE `status` = '$status' ";
}

if(isset($_GET['page'])){
  $page = $_GET['page'];
  $offset = 20 * $page;
  $sql= "SELECT * FROM veggie ORDER BY buy_date desc LIMIT 20 OFFSET $offset WHERE `status` = '$status' ";
}
$result = $db->query($sql);

$p = $page;

?>


<h2 class="text-center">Veggie Crate Forms</h2><hr>

<table class= "table table-bordered table-striped table-auto">
<thead>

  <th class="info">Full_Name</th>
  <th class="info">email</th>
  <th class="success">crate</th>
  <th class="active"># people</th>
  <th class="active">Products</th>
  <th class="active">Bulk Items</th>
  <th class="active">Interests</th>
  <th class="active">How did you find us?</th>
  <th class="active">Are you ready?</th>
<!--  <th class="danger">Delete</th> -->

</thead>
<tbody>
  <?php


    ?>

    <?php while($archived = mysqli_fetch_assoc($result)) : ?>

      <?php
      $full_name = $archived['first'].' '.$archived['last'];
       ?>
    <tr>

      <td><?=$full_name?></td>
      <td><?=$archived['email']?></td>
      <td><?=$archived['crate']?></td>
      <td><?=$archived['people']?></td>
      <td><?=$archived['bulk']?></td>
      <td><?=$archived['class']?></td>
      <td><?=$archived['how']?></td>
      <td><?=$archived['ready']?></td>
      <!-- <td><a href="archived.php?delete=<?=$archived['id']?>"><span class="glyphicon glyphicon-minus">Delete</span></a></td> -->
    </tr>
    <?php  endwhile; ?>
</tbody>
</table>


<div id="decrease" style="" class="">


<?php if ($page > 0 ): ?>
    <form class="form" action="whf.php?page=<?=--$p?> " method="post">
  <input type="submit" name="name" value="<--">
<?php endif; ?>

  </div>

  <div id="increase" class="">

    <?php   $countr = mysqli_num_rows($result);

    if($countr == 10){


     if ($page == '' || $page < 1 ): ?>
        <form class="form" action="whf.php?page=1 " method="post">
      <input type="submit" name="name" value="-->">
    <?php endif; ?>

  <?php

  if ($page < $countr && $page != '' && $page != 0 ): ?>
      <form class="form" action="whf.php?page=<?=++$p?> " method="post">
    <input type="submit" name="name" value="-->">
  <?php endif;

} ?>


    </div>



 <?php include 'includes/footer.php';  ?>

please post the rest of your code as well, especially the line that produces that warning — Pevara, May 10 '16 at 21:24
Have you echoed out your sql and run it directly on mysql? I would make sure that it actually returns some results when you run it against mysql — xJoshWalker, May 10 '16 at 21:25
Your query is incorrect. `WHERE` clause should be after `FROM` clause. — Rajdeep Paul, May 10 '16 at 21:26

score 2 · Accepted Answer · answered May 10 '16 at 21:26

2

Your order and limit are in the wrong place.

http://dev.mysql.com/doc/refman/5.7/en/select.html

Try:

SELECT * FROM `veggie` WHERE `status` = '$status' ORDER BY `buy_date` desc LIMIT 20 OFFSET 0

You also should look into using prepared statements.

answered May 10 '16 at 21:26

chris85

23,591
7
30
47

Ah posted the same, didn't saw your answer – Strahinja Djurić May 10 '16 at 21:30
Thank You chris that worked wonders. – Cjfidler May 10 '16 at 21:32

bakz · Answer 2 · 2016-05-10T21:31:15.513

1st. The WHERE clause comes before the the ORDER clause

2nd. read the warning, you are giving it the wrong parameter.

$link = mysqli_connect("localhost", "username", "password", "database");
$result = mysqli_query($link, $sql)
$row = mysqli_fetch_assoc($result);

3rd. why are you using object oriented then switching to procedural?? if you started with

$res = $db->query($sql);

then it should be:

$row = $res->fetch_assoc();

check the manual http://php.net/manual/en/mysqli-result.fetch-assoc.php

score 0 · Answer 3 · answered May 10 '16 at 21:30

0

You have multiple syntax errors. Your query should look like this:

SELECT * FROM `veggie`  WHERE `status` = "$status"  ORDER BY `buy_date` desc LIMIT 20 OFFSET 0

ORDER BY, LIMIT and OFFSET are going at the end of query.

answered May 10 '16 at 21:30

Strahinja Djurić

262
1
2
13

Query will not work. Mind Taking a look at it?

3 Answers3