Formatting HTML Table from SQL Database

Question

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.

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:

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>

score 0 · Accepted Answer · 2016-08-21 21:12:29Z

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>

Great Idea, however this gives me the Monday deal on one row, then Tuesday on one row, when I desire all daily deals for the given location on 1 row. Should I set my database up a different way to make it simpler?
Humm, on 'day' row add the random string that tells this is daily deal, then in the default add the deal to all the variables, I will show you in edit.
Now if you add on the day row the string "Daily" the switch case runs the default case. then all the variables will get the $deal value.Good luck
I apologize, I'm a little confused by what you mean to add the string "Daily"? Where exactly would I need to add that?
The code the other person answered with? I did, and that code and your's both give the same result.

RGriffiths · Accepted Answer · 2016-08-21 20:58:29Z

0

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.

edited Aug 21, 2016 at 20:58

answered Aug 21, 2016 at 20:34

RGriffiths

6,02022 gold badges86 silver badges135 bronze badges

1 Comment

LClarke27 Over a year ago

I see, but my 'field1' and 'field2' are both the 'deal' field in my database. How do I differentiate between Monday deal and Tuesday deal and so on?

Collectives™ on Stack Overflow

Formatting HTML Table from SQL Database

2 Answers 2

5 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Linked

Related