LClarke27 LClarke27 - 3 months ago 17
SQL Question

Formatting HTML Table from SQL Database

My database contains deals from my local area. So each deal contains a location, deal_id (primary key), day of the week it applies, and the deal itself.

Database schema

Ideally, I list out the data with the headers of my table being:

Location , Monday , Tuesday , Wednesday... so on.

With only one instance of each location per row. Problem is, I haven't been able to get that to happen, so far it puts Mondays deal from the first location in the first row for all days. Then the next row is the same place with the Tuesdays deals for each day, like so:

HTML Output Table

Here is my PHP code to convert my data to the HTML table:
I figure the error is in my loop. Not sure how to switch from one $row to the next

<!DOCTYPE HTML>
<html lang=en>
<head>
<title>Ashdeals</title>
<meta charset = "UTF-8">
<link rel="stylesheet" type="text/css" href="stylesheet.css">
</head>
<body>


<?php
//Create Connection
$mysqli = new MySQLi("localhost", "root", "", "ashdeals");

$sql = "SELECT * FROM deals";
// Query Database
$result = $mysqli->query($sql);

//Count the returned rows
if ($result->num_rows !=0){
//Turn results into an array

echo "<table>
<tr>
<th>Location</th>
<th>Monday</th>
<th>Tuesday</th>
<th>Wednesday</th>
<th>Thursday</th>
<th>Friday</th>
<th>Saturday</th>
<th>Sunday</th>

</tr>";
// output data of each row
while($row = mysqli_fetch_array($result)) {

$location = $row['location'];
$deal = $row['deal'];

echo
"<tr>

<td>" . $location . "</td>
<td>" . $deal . "</td>
<td>" . $deal . "</td>
<td>" . $deal . "</td>
<td>" . $deal . "</td>
<td>" . $deal . "</td>
<td>" . $deal . "</td>
<td>" . $deal . "</td>
</tr>";
}
echo "</table>";
} else {
echo "0 results";
}

?>

</body>

</html>

Answer

Simple solution: switch case to every day.

Edit: Daily deal.

 <!DOCTYPE HTML>
<html lang=en>
    <head>
        <title>Ashdeals</title>
        <meta charset = "UTF-8">
        <link rel="stylesheet" type="text/css" href="stylesheet.css">
    </head>
<body>


    <?php 
    //Create Connection
    $mysqli = new MySQLi("localhost", "root", "", "ashdeals");

    $sql = "SELECT * FROM deals";
     // Query Database  
    $result = $mysqli->query($sql);

    //Count the returned rows
    if ($result->num_rows !=0){
    //Turn results into an array   

         echo "<table>
            <tr>
                <th>Location</th>
                <th>Monday</th>
                <th>Tuesday</th>
                <th>Wednesday</th>
                <th>Thursday</th>
                <th>Friday</th>
                <th>Saturday</th>
                <th>Sunday</th>

            </tr>";
         // output data of each row
         while($row = mysqli_fetch_array($result)) {
             $location = $row['location'];
             $deal = $row['deal'];
             $day = $row['day'];
             $moDeal = ""; // Monday Deal variable. 
             $tuDeal = ""; // Tuesday Deal variable. 
             $weDeal = ""; // Wednesday Deal variable. 
             $thDeal = ""; // Thursday Deal variable. 
             $frDeal = ""; // Friday Deal variable. 
             $saDeal = ""; // Saturday Deal variable. 
             $suDeal = ""; // Sunday Deal variable. 
             switch ($day) {
                 case 'Mo':
                     $moDeal = $deal; 
                     break;
                 case 'Tu':
                     $tuDeal = $deal;
                     break;
                 case 'We':
                     $weDeal = $deal; 
                     break;
                 case 'Th':
                     $thDeal = $deal; 
                     break;
                 case 'Fr':
                     $frDeal = $deal;
                     break;
                 case 'Sa':
                     $saDeal = $deal; 
                     break;
                 case 'Su':
                     $suDeal = $deal; 
                     break;                 
                 default:
                     # In case some daily deal: 
                    $moDeal = $deal; 
                    $tuDeal = $deal; 
                    $weDeal = $deal; 
                    $thDeal = $deal; 
                    $frDeal = $deal; 
                    $saDeal = $deal; 
                    $suDeal = $deal; 
                     break;
             }
             echo 
             "<tr>
             <td>" . $location . "</td>
             <td>" . $moDeal . "</td>
             <td>" . $tuDeal . "</td>
             <td>" . $weDeal . "</td>
             <td>" . $thDeal . "</td>
             <td>" . $frDeal . "</td>
             <td>" . $saDeal . "</td>
             <td>" . $suDeal . "</td>
             </tr>";

         }
         echo "</table>";
    } else {
         echo "0 results";
    }

    ?>

</body>

</html>
Comments