Display data from database table into html table inside a php file

Question

I Am trying to retrieve data from the DB and display it in a table, but i don't know how to implement that in php. In addition i would like some help with implementing the search function on the displayed data based on the customer_id and date.

<?php
include 'connect.php';
$query = ("SELECT * FROM 'transaction'");
$result = mysqli_query($conn, $query);
?>
<!DOCTYPE html>
<html lang="en">
<body>
 <form class="form-inline my-2 my-lg-0" action="search2.php" method="POST">
        <input class="form-control mr-sm-2" type="text" placeholder="Search" aria-label="Search">
        <button class="btn btn-outline-success my-2 my-sm-0" type="submit" name="search">Search</button>
      </form>
    </div>
  </nav>
<!-- Table -->
<h1>Sales</h1>
          <div class="table-responsive">
            <table class="table table-striped table-sm">
              <thead>
                <tr>
                  <th>Customer ID</th>
                  <th>Date</th>
                  <th>Outlet</th>
                  <th>Employee ID</th>
                  <th>Product ID</th>
                  <th>Quantity</th>

                </tr>
              </thead>
              <tbody>
                <?php while ($row = mysqli_fetch_row($result)) :?>
    <tr>
      <th scope="row"><?php echo $i; ?></th>
        <td><?php echo $row['customer_id']; ?></td>
        <td><?php echo $row['date']; ?></td>
        <td><?php echo $row['outlet_id']; ?></td>
        <td><?php echo $row['emp_id']; ?></td>
        <td><?php echo $row['Prod_id']; ?></td>
        <td><?php echo $row['quantity']; ?></td>
    </tr>
<?php
if (!$result) {
  die ("Database access failed: " .mysql_error());
}
endwhile; ?>
              </tbody>
            </table>
          </div>

</body>
</html>

This is a separate file for the DB conection.

<?php
$ser = "localhost";
$user = "root";
$pass = "";
$db = "music_store_db";

// Create connection
$conn = new mysqli($ser, $user, $pass, $db);

// Check connection
if (mysqli_connect_error()){
  die("Connection Failed: ".mysqli_connect_error());
}
// echo "Connection Success";
 ?>

When the link for this page is clicked, it's supposed to display sales transactions from the DB.

The output i get is:

Warning: mysqli_fetch_row() expects parameter 1 to be mysqli_result, bool given

Yes, having a Boolean instead of a results object generally means that the query has failed for some reason. You should really check to see whether that has happened _before_ you try to use the results, rather than at the end of the code. — droopsnoot, Oct 18 '19 at 12:19
You have an error. `mysql_error()` does not exist. Please consider switching error mode on instead. [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) — Dharman, Oct 18 '19 at 23:20

score -1 · Accepted Answer · answered Oct 18 '19 at 12:16

Please use mysqli_fetch_assoc insted of mysqli_fetch_row and remove before and after from table name in query

<?php
include 'connect.php';
$query = ("SELECT * FROM transaction");
$result = mysqli_query($conn, $query);
?>
<!DOCTYPE html>
<html lang="en">
<body>
 <form class="form-inline my-2 my-lg-0" action="search2.php" method="POST">
        <input class="form-control mr-sm-2" type="text" placeholder="Search" aria-label="Search">
        <button class="btn btn-outline-success my-2 my-sm-0" type="submit" name="search">Search</button>
      </form>
    </div>
  </nav>
<!-- Table -->
<h1>Sales</h1>
          <div class="table-responsive">
            <table class="table table-striped table-sm">
              <thead>
                <tr>
                  <th>Customer ID</th>
                  <th>Date</th>
                  <th>Outlet</th>
                  <th>Employee ID</th>
                  <th>Product ID</th>
                  <th>Quantity</th>

                </tr>
              </thead>
              <tbody>
                <?php while ($row = mysqli_fetch_assoc($result)) :?>
    <tr>
      <th scope="row"><?php echo $i; ?></th>
        <td><?php echo $row['customer_id']; ?></td>
        <td><?php echo $row['date']; ?></td>
        <td><?php echo $row['outlet_id']; ?></td>
        <td><?php echo $row['emp_id']; ?></td>
        <td><?php echo $row['Prod_id']; ?></td>
        <td><?php echo $row['quantity']; ?></td>
    </tr>
<?php
if (!$result) {
  die ("Database access failed: " .mysql_error());
}
endwhile; ?>
              </tbody>
            </table>
          </div>

</body>
</html>

You have an error. `mysql_error()` does not exist. Please consider switching error mode on instead. [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) — Dharman, Oct 18 '19 at 23:20

Display data from database table into html table inside a php file

1 Answers1