CODEWITHSUNDEEP
phpsql
LClarke27
LClarke27

Reputation: 23

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>

Upvotes: 0

Views: 953

Answers (2)

user6063698
user6063698

Reputation:

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>

Upvotes: 0

RGriffiths
RGriffiths

Reputation: 5970

Just loop through: 

$dayArray = ['Monday', 'Tuesday' ..... 'Sunday'];
while($row = mysqli_fetch_array($result)) {

    echo "<tr>";

    echo "<td>" . $row['locationField'] . "</td>";

    foreach ($dayArray as $dayField) {
        if ($dayField == $row['dealDayField'] ) {
            echo "<td>" . $row['dealField'] . "</td>";
        }
        else {
            echo "<td></td>";
        }
    }
    echo "</tr>";
}

echo "</table>";

No need to do the assign $valueX and could echo $row[] but shown here for clarity.

Upvotes: 0

Related Questions