too long

Question

I need a help in a query rows creating a table through php. Here below is the rows in my mysql table named "routine".

Now i want to fetch through week days in a single loop creating a table like below:

Is it possible with a single loop? Here i want to separate the rows through week and auto columns through time.

Answer 1

Yes! It is possible with single loop but we use php array...

Try this... Data insertion php coding - insert.php

<?php
    $servername = "localhost";
    $username = "root";
    $password = "";
    $database = "my_db";

    // Create connection
    $conn = new mysqli($servername, $username, $password, $database);

    $week = "Friday";
    $subject = "Physics";
    $time = 3;

    $sql = "INSERT INTO routine (Week, Subject, Time)VALUES ('".$week."', '".$subject."', '".$time."')";

    if ($conn->query($sql) === TRUE) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }

    $conn->close();

?>

Php code for displaying rows through week and auto columns through time - view.php

<?php
    $servername = "localhost";
    $username = "root";
    $password = "";
    $database = "my_db";

    // Create connection
    $conn = new mysqli($servername, $username, $password, $database);

    //Initial Declarations
    $data = [];     

    $data[0][0] = "<tr><td>Week</td>";
    $data[0][1] = "<tr><td>Monday</td>";
    $data[0][2] = 0;

    $data[1][0] = "<tr><td>Week</td>";
    $data[1][1] = "<tr><td>Tuesday</td>";
    $data[1][2] = 0;

    $data[2][0] = "<tr><td>Week</td>";
    $data[2][1] = "<tr><td>Wednesday</td>";
    $data[2][2] = 0;

    $data[3][0] = "<tr><td>Week</td>";
    $data[3][1] = "<tr><td>Thursday</td>";
    $data[3][2] = 0;

    $data[4][0] = "<tr><td>Week</td>";
    $data[4][1] = "<tr><td>Friday</td>";
    $data[4][2] = 0;


    $sql = "SELECT * FROM routine";
    $result = mysqli_query($conn, $sql);

    while($row = mysqli_fetch_array($result, MYSQLI_NUM))       // Table Fields - Week ($row[0]), Subject($row[1]), Time($row[2])
    {
        if($row[0] == "Monday")
        {
            $data[0][0] = $data[0][0] . "<td>" . $row[2] . "</td>";
            $data[0][1] = $data[0][1] . "<td>" . $row[1] . "</td>";
            $data[0][2] = intval($data[0][2]) + 1;
        }
        else if($row[0] == "Tuesday")
        {
            $data[1][0] = $data[1][0] . "<td>" . $row[2] . "</td>";
            $data[1][1] = $data[1][1] . "<td>" . $row[1] . "</td>";
            $data[1][2] = intval($data[1][2]) + 1;
        }
        else if($row[0] == "Wednesday")
        {
            $data[2][0] = $data[2][0] . "<td>" . $row[2] . "</td>";
            $data[2][1] = $data[2][1] . "<td>" . $row[1] . "</td>";
            $data[2][2] = intval($data[2][2]) + 1;
        }
        else if($row[0] == "Thursday")
        {
            $data[3][0] = $data[3][0] . "<td>" . $row[2] . "</td>";
            $data[3][1] = $data[3][1] . "<td>" . $row[1] . "</td>";
            $data[3][2] = intval($data[3][2]) + 1;
        }
        else if($row[0] == "Friday")
        {
            $data[4][0] = $data[4][0] . "<td>" . $row[2] . "</td>";
            $data[4][1] = $data[4][1] . "<td>" . $row[1] . "</td>";
            $data[4][2] = intval($data[4][2]) + 1;
        }

    }

    //Final Assigning
    $data[0][0] = $data[0][0] . "</tr>";
    $data[0][1] = $data[0][1] . "</tr>";

    $data[1][0] = $data[1][0] . "</tr>";
    $data[1][1] = $data[1][1] . "</tr>";

    $data[2][0] = $data[2][0] . "</tr>";
    $data[2][1] = $data[2][1] . "</tr>";

    $data[3][0] = $data[3][0] . "</tr>";
    $data[3][1] = $data[3][1] . "</tr>";

    $data[4][0] = $data[4][0] . "</tr>";
    $data[4][1] = $data[4][1] . "</tr>";


    // Display Result
    if($data[0][2] > 0)
        echo "<table border='1' cellspacing = 0 cellpadding = '4'>".$data[0][0] . $data[0][1] . "</table><br>";
    if($data[1][2] > 0)
        echo "<table border='1' cellspacing = 0 cellpadding = '4'>".$data[1][0] . $data[1][1] . "</table><br>";
    if($data[2][2] > 0)
        echo "<table border='1' cellspacing = 0 cellpadding = '4'>".$data[2][0] . $data[2][1] . "</table><br>";
    if($data[3][2] > 0)
        echo "<table border='1' cellspacing = 0 cellpadding = '4'>".$data[3][0] . $data[3][1] . "</table><br>";
    if($data[4][2] > 0)
        echo "<table border='1' cellspacing = 0 cellpadding = '4'>".$data[4][0] . $data[4][1] . "</table><br>";

?>