Reading data from database #

Now we want to view the data from the database. To view the data you need to send a sql query to the database which will return the data. Once we got the data in php from the database we will render that that.

Read data from database #

  • We need database connection to run query into the database from php. So, first of all we call the connection file db.php.
  • Now we need to write the query which we want to run in the database. We will run this query SELECT * FROM 'product' to get the data from product table.
  • Now it’s time to run the query in the database. On $con object we will run the query, we will get the response from the sql which I stored in $result.

Result has response from MySQL.

<?php
    require_once('db.php');

    $sql = "SELECT * FROM `product`";
    $result = $con->query($sql);

?>

Total row in result #

You can call num_rows on result object of mysql query. It return number of rows return by the mysql. We will check if we have more than zero rows before rendering MySQL data.

$result->num_rows

Rendering the data from MySQL #

Now we will show the data to user if the webpage.

  • We will check if the number of rows in the table in more than 0
  • We will get each row one by one using while loop and show in the webpage.
<?php
    if($result->num_rows > 0 ){
        while($row = $result->fetch_assoc()){
            echo "<tr>";
            echo "<td>" . $row['id'] . "</td>";
            echo "<td>" . $row['name'] . "</td>";
            echo "<td> Rs. " . $row['price'] . "</td>";
            echo "<td>" . $row['description'] . "</td>";
            echo "</tr>";
        }
    }
?>

final code #

show.php
<?php
    require_once('db.php');

    $sql = "SELECT * FROM `product`";
    $result = $con->query($sql);

?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Product Company</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css" integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous">
</head>
<body>
    <div class="jumbotron">
        <h1 class="text-center">
            Product Company
        </h1>
    </div>

    <div class="container">
        <table class="table table-striped table-borderrer">
            <tr>
                <th>ID#</th>
                <th>Name</th>
                <th>Price</th>
                <th>Description</th>
            </tr>

            <?php
                if($result->num_rows > 0 ){
                    while($row = $result->fetch_assoc()){
                        echo "<tr>";
                        echo "<td>" . $row['id'] . "</td>";
                        echo "<td>" . $row['name'] . "</td>";
                        echo "<td> Rs. " . $row['price'] . "</td>";
                        echo "<td>" . $row['description'] . "</td>";
                        echo "</tr>";
                    }
                }
            ?>
        </table>
    </div>
</body>
</html>