How to echo SQL table data in auto created html/PHP table

I have a SQL table named res having some rows and columns. For example:

Name Class Sub1 Sub2 Sub3 sub4

s1    2     10   12  45   15
s2    2     50   12  14   60
s3    2     10   12  40   15
s4    2     20   12  14   15
s5    2     10   12  11   15
............................
............................
s500  2     11   12  13   16
a1    5     05   10  12   14
a2    5     45   10  16   14
a3    5     50   11  12   15
a4    5     45   10  12   14
............................
............................
a900  5     30   15   14  20

If someone enter 5 in class.php form then the result should be displayed in automatically generated html/PHP table in descending order of the name. For example:

Name Class Sub1 Sub2 Sub3 Sub4
a2    5     45   10  16   14
a3    5     50   11  12   15
a4    5     45   10  12   14
a5    5     45   10  16   14
a6    5     50   11  12   15
a7    5     45   10  12   14
............................
............................

My class.php code is

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.2.0/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
  <script src="http://maxcdn.bootstrapcdn.com/bootstrap/3.2.0/js/bootstrap.min.js"></script>
</head>
<table class="table table-bordered" >
<?php
$servername = "localhost";
$username = "adsdt";
$password = "ssfdfsg";
$dbname = "absdt";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$Class = mysqli_real_escape_string($conn, $_POST['Class']);


$sql = "SELECT * from res
    WHERE class = '$class'";



$result = $conn->query($sql);
$columns = array();
$resultset = array();
while ($row = mysql_fetch_assoc($result)) {
    if (empty($columns)) {
        $columns = array_keys($row);
    }
    $resultset[] = $row;
}


if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {


    echo "<thead><tr class='info';><th>Name</th><th>Class</th><th>Sub1</th><th>Sub2</th><th>Sub3</th><th>Sub4</th></tr></thead><tbody><tr class='success';><td>{$row['name']}</td><td>{$row['class']}</td><td>{$row['Sub1']}</td><td>{$row['Sub2']}</td><td>{$row['Sub3']}</td><td>{$row['Sub4']}</td></tr></tbody></table>";
        echo "</table>";



// Print the data
while($row = mysql_fetch_row($result)) {
    foreach($row as $_column) {
        echo "{$_column}";
    }
}
    }
} else {
    echo "Information Not Available";
}

?>


</table>
</body>
</html>

My code is fetching all the result matching the criteria but displaying only one (first result) result in table and all other are just only written without space in betweem them… I cannot specify the row number as I don’t know the exact number, all rows are varying depending upon the class or cannot write the same repeated code for echoing all the row because I don’t know how much exactly row is…

What changes should be made in the code to display all the SQL table data in html/PHP generated table ?

Answer

here is the code you need for dynamic columns and records set to display in HTML form

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
</head>

<?php

        # Your database credentials goes here
        $servername = "localhost";
        $username = "root";
        $password = "123456";
        $dbname = "stackoverflow";

        // Create connection
        $conn = new mysqli($servername, $username, $password, $dbname);
        // Check connection
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }

        # Set Your Table class id to fetch records
        # You can set it from $_GET OR $_POST value
        $class = 5;
        //$class = mysqli_real_escape_string($conn, $_POST['Class']);

        # Fetch records 
        $sql = "SELECT * FROM res WHERE class = '$class'";

        $result = $conn->query($sql);
        $columns = array();
        $resultset = array();

        # Set columns and results array
        while ($row = mysqli_fetch_assoc($result)) {
            if (empty($columns)) {
                $columns = array_keys($row);
            }
            $resultset[] = $row;
        }


        # If records found
        if( count($resultset > 0 )) {
?>
            <table class="table table-bordered" >
                <thead>
                    <tr class='info';>
                        <?php foreach ($columns as $k => $column_name ) : ?>
                            <th> <?php echo $column_name;?> </th>
                        <?php endforeach; ?>
                    </tr>
                </thead>
                <tbody>

                    <?php

                        // output data of each row
                        foreach($resultset as $index => $row) {
                        $column_counter =0;
                    ?>
                        <tr class='success';>
                            <?php for ($i=0; $i < count($columns); $i++):?>
                                <td> <?php echo $row[$columns[$column_counter++]]; ?>   </td>
                            <?php endfor;?>
                        </tr>
                    <?php } ?>

                </tbody>
            </table>

    <?php }else{ ?>
        <h4> Information Not Available </h4>
    <?php } ?>

</body>
</html>

Hope, it helps you buddy. I also modify coding errors, so no worries 🙂

Advertisement

Answer