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

JavaScript
​x
 
Name Class Sub1 Sub2 Sub3 sub4​s1    2     10   12  45   15s2    2     50   12  14   60s3    2     10   12  40   15s4    2     20   12  14   15s5    2     10   12  11   15........................................................s500  2     11   12  13   16a1    5     05   10  12   14a2    5     45   10  16   14a3    5     50   11  12   15a4    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
............................
............................

JavaScript
 
Name Class Sub1 Sub2 Sub3 Sub4a2    5     45   10  16   14a3    5     50   11  12   15a4    5     45   10  12   14a5    5     45   10  16   14a6    5     50   11  12   15a7    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>

JavaScript
 
<!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 connectionif ($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 datawhile($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>

JavaScript
 
<!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