CODEWITHSUNDEEP
phphtmlmysql
Hoàng Việt
Hoàng Việt

Reputation: 174

Putting a whole SQL Database into HTML Table

I'm trying to put a whole SQL database into html table. I'm using MySQLi API. But it just return the first row of the table , and the rest of them just look mess up.Here's my code:

            <h1> School Lesson System</h1>

            <?php

                if(isset($_SESSION['u_id'])) {
                    echo "You are logged in \n";
                }
            ?>
            <table border="1">
                <thead>
                    <tr>
                        <td>Lesson_id</td>
                        <td>Teacher</td>
                        <td>Lesson</td>
                        <td>Day</td>
                        <td>Time</td>
                        <td>Classroom</td>
                        <td>Year</td>
                        <td>Curriculum</td>
                    </tr>
                </thead>
                <tbody>
                    <?php
                    require_once 'includes/dbh.inc.php';

                    $query = "SELECT * FROM monday";
                    $result = $conn->query($query);
                    $rows = $result->num_rows;
                    for ( $j = 0; $j < $rows; ++$j) {
                        $result->data_seek($j);
                        $row = $result->fetch_array(MYSQLI_ASSOC);
                    echo "<tr>";
                        echo "<td>" . $row['Lesson_id']. "</td>";
                         echo "<td>". $row['Teacher']. "</td>";
                         echo "<td>" .$row['Lesson']. "</td>";
                         echo "<td>" . $row['Day']. "</td>";
                         echo "<td>". $row['Time']. "</td>";
                         echo "<td>". $row['Classroom']. "</td>";
                         echo "<td>". $row['Year']. "</td>";
                         echo "<td>". $row['Curriculum']. "</td>";
                     echo "</tr>";
                echo"</tbody>";
            echo"</table>";
                    }


include_once 'footer.php';
?>

Any solution for this ????

Upvotes: 1

Views: 114

Answers (4)

Minar_Mnr
Minar_Mnr

Reputation: 1405

Change that : 

<?php
                    require_once 'includes/dbh.inc.php';

                    $query = "SELECT * FROM monday";
                    $result = $conn->query($query);
                    while ($row = $result->fetch_assoc()) {
                    echo "<tr>";
                        echo "<td>" . $row['Lesson_id']. "</td>";
                         echo "<td>". $row['Teacher']. "</td>";
                         echo "<td>" .$row['Lesson']. "</td>";
                         echo "<td>" . $row['Day']. "</td>";
                         echo "<td>". $row['Time']. "</td>";
                         echo "<td>". $row['Classroom']. "</td>";
                         echo "<td>". $row['Year']. "</td>";
                         echo "<td>". $row['Curriculum']. "</td>";
                     echo "</tr>";

                    }
  echo"</tbody>";
                echo"</table>";

Upvotes: 1

Death-is-the-real-truth
Death-is-the-real-truth

Reputation: 72299

1.Use while instead of for loop

2.don't close <tbody> and <table> inside loop (which is the main problem)

3.I am unable to see session_start(); in your code, while you are using SESSION in your code.So please check and if you don't have then add that on top of the page.

Do like below:-

<?php
require_once 'includes/dbh.inc.php';

$query = "SELECT * FROM monday";
$result = $conn->query($query);

while($row = $result->fetch_array(MYSQLI_ASSOC)){
    echo "<tr>";
        echo "<td>" . $row['Lesson_id']. "</td>";
        echo "<td>". $row['Teacher']. "</td>";
        echo "<td>" .$row['Lesson']. "</td>";
        echo "<td>" . $row['Day']. "</td>";
        echo "<td>". $row['Time']. "</td>";
        echo "<td>". $row['Classroom']. "</td>";
        echo "<td>". $row['Year']. "</td>";
        echo "<td>". $row['Curriculum']. "</td>";
    echo "</tr>";
}
echo "</tbody>";
echo "</table>";
include_once 'footer.php';
?>

Note:-

Table names like Monday is not good at all. It will be fruitful name which describes it's purpose itself like users(list of users),logs(track record of different activities)...etc.

Upvotes: 1

Agniswar Chakraborty
Agniswar Chakraborty

Reputation: 287

Create a class file for connecting the database

  class DBConnUtil {
 private $mysqli;
 function __construct() {
    $this->open_connection();
}

private function open_connection() {    // function to connect database;
        $this->mysqli = new mysqli(DB_SERVER,DB_USER,DB_PASS,DB_NAME);
        if ($this->mysqli->connect_error) {
            die('Error : ('. $this->mysqli->connect_errno .') '. $this->mysqli->connect_error);
        }
        $this->mysqli->set_charset("utf8");
}
public function run_query ($queryString) {
    $result=$this->mysqli->query($queryString);

    if (!$result) {
        trigger_error('Wrong SQL: ' . $queryString . ' Error: ' . $this->mysqli->error, E_USER_ERROR);                                                                  
    }
    $this->close_connection();
    return $result;
}



private function close_connection() {
        $this->mysqli->close();
}

}

Then another class file Lesson.php. Use the database column as private variable i it..Then write a function fetchData() like this.

    public function fetchData() {
    $dbConn = new DBConnUtil(); //name of the database class mention 
    before..
    $queryString = "select * from tablename ";
    $result = $dbConn->run_query($queryString);
    $rows_returned = $result->num_rows;

    $categorydata = array();
    while($rows = $result->fetch_object()){
   $categorydata[] =  $rows;
    }
    $result->free();
   return $categorydata;

   }

then use foreach statement to print it..

Upvotes: 0

Saman
Saman

Reputation: 221

That's because you close tbody and table tag inside for loop. 

       echo"</tbody>";
       echo"</table>";

move this two lines outside the for.

Upvotes: 2

Related Questions