php - Populating an HTML table with MySQL data, linked by calendar date

Question

I am using the code below to generate a simple HTML table that displays the next 90 calendar days. Each day is a row in this simple table.

$now = time();
echo "<table>";
for ($i=0;$i<90;$i++)
{
   $thisDate = date("d/m/Y",$now + ($i*86400));
   echo "<tr><td>".$thisDate."</td></tr>
";
}
echo "</table>";

Also, I have a MySQL table with the following fields:

event           varchar(1000)   
datescheduled   date    

How can I make a second column in the aforementioned HTML table, containing "event" from the MySQL table, matched by date?

Answer 1

This can be tackled in numerous ways. Consider this example:

PHP

<?php
$con = mysqli_connect("localhost","dbuser","dbpass","database");
$query = mysqli_query($con, "SELECT * FROM event");

// First build the array of events, put the dates in keys, then the values as events
$events = array();
while($result = mysqli_fetch_assoc($query)) {
    $events[$result['datescheduled']] = $result['event'];
}

?>

---

// Structure should be something like this:
Array
(
    [2014-05-02] => Day of lorem
    [2014-06-02] => Day of ipsum
    [2014-07-02] => Day of days
)

---

HTML

<!-- the compare selected values on the current loop, check its keys -->

<?php $now = time(); ?>
<table border="1" cellpadding="10">
<?php for($i=0;$i<90;$i++): ?>
    <?php $thisDate = date("Y-m-d", $now + ($i*86400)); ?>
    <tr>
        <td><?php echo $thisDate; ?></td>
        <td><?php echo array_key_exists($thisDate, $events) ? $events[$thisDate] : ''; ?></td>
</tr>
<?php endfor; ?>
</table>