0

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 ?

Improve this question
5
  • You mix up mysqli() and mysql() functions at first. So please rewrite your code to mysqli() only like: mysqli_query($conn,$sql); and mysqli_fetch_assoc($result); Commented Mar 25, 2015 at 19:33
  • Sorry but I don't know... I have copied this code from a different website and it is working on my page so I thought it is right... just I changes the database table and column name.... Commented Mar 25, 2015 at 19:44
  • Please correct my code .... Commented Mar 25, 2015 at 19:45
  • if you don't what i am talking about i suggest you first read about MySQLi in PHP: php.net/manual/en/book.mysqli.php so you know next time. Commented Mar 25, 2015 at 19:49
  • Okay I will read it... but currently I need that auto table created code urgently.....please give ! Commented Mar 25, 2015 at 19:54

2 Answers 2

Reset to default
1

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 :)

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

I made no changes with your code, It is resulting in HTTP error. Do I have to replace $column_name with real column name ?
I just updated code, can please use it again and let me know. It should work without any error. Also send me full error message if there is any. Thanks
Earlier everytime.. Information Not Available.... Now nothing displaying...blank page
Can you give me simple code without bootstrap so that I can check whether that is working or not ?
Did you replace DB credentials with your one? Also, in order to run this code, you must submit $_POST['Class'] value with the one exists in the database.
|
1

I had the same problem so I came up with a function that would print my SQL data(table) from the array called $result as a proper html table and it would be responsive. Meaning that all sorts of SQL tables would be printed correctly.

function printTable($result){
    if(!mysqli_num_rows($result) > 0){
        echo 'no results';
    }
    else{        
        echo '<table class="table">';
        $y=0;
        while($row = mysqli_fetch_assoc($result)){
            if ($y <= 0){
                echo "<tr>";
                for ($x = 0; $x < count(array_keys($row)); $x++){
                    echo "<th>";
                    echo array_keys($row)[$x];
                    echo "</th>";
                }
                echo "</tr>";
            }
            echo "<tr>";
            for ($x = 0; $x < count(array_keys($row)); $x++){
                echo "<td>";
                echo $row[array_keys($row)[$x]];
                echo "</td>";}
            echo "</tr>";
            $y++;
        }
        echo "</table>";
    }
    
}

You use the function by getting $result from sqli query and parsing it to function like this:

$sql = "SELECT * FROM table";
$result = mysqli_query($conn, $sql);
if(mysqli_num_rows($result) > 0){
            printTable($result);}

The Output looks something like this:

    columnName1...columnName2...columnName3...columnNameX
    .....................................................
    row1Field1 ...row1Field2 ...row1Field3 ...row1FieldX
    row2Field1 ...row2Field2 ...row2Field3 ...row2FieldX
    row3Field1 ...row3Field2 ...row3Field3 ...row3FieldX
    rowXField1 ...rowXField2 ...rowXField3 ...rowXFieldX
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.