Display Dates & Fill Rows If Data Available

[sparz14]

Hello,

 

Im fairly new to php.

 

I have a database table which is filled in for every shift that a taxi driver does (displays how much money they made, etc). Im trying to create two tables in HTML - one to display day shifts and one to display night shifts and display them next to eachother.

 

I currently have all of this data displayed in the two HTML tables, although a car is not always driven every day or every shift. E.g. there are 30 days in a month and only 27 entries appear.

 

What i want to do is display an empty row at the correct date when a shift entry does not exist (instead of currently skipping that date all together)

 

Any help is appreciated

I know im iterating over the rows in the database, but how could i do it programatically and iterate over all of the dates in the date range, then display data if dates match?

 

Here's a small section of my code (its just one table - day shifts):

.........

$sql=mysql_query("SELECT DA, date, nettotal, petrol, (nettotal-petrol) as net FROM daily WHERE daily.date between '$date7' and '$date8' and $car=carused and starttime between '00:00:01' and '12:00:00' order by date")
or die ("Error: ".mysql_error());

$row = mysql_fetch_array($sql);

...................

$sql2=mysql_query("SELECT date, carused, round(avg(nettotal),2) as average, sum(nettotal) as Totalgross, sum(petrol) as Totalpetrol, sum(nettotal-petrol) as Totalnet from daily WHERE daily.date between '$date7' and '$date8' and starttime between '00:00:00' and '12:00:00' and $car=carused")

or die ("Error: ".mysql_error());
$row2 = mysql_fetch_array($sql2);

..................


<table>
<tr>
<td style="width: 100%; table-align: center;" >

<table width="400" border="0" style="text-align: center;">
  <tr>
<td><span class="trr">Date</span></td>
<td><span class="trr">Gross</span></td>
<td><span class="trr">Fuel</span></td>
<td><span class="trr">Net</span></td>
  </tr>

 <?php $i = 0; ?>
 <?php do {?>


 <tr <?php echo $i++ % 2 ? 'class="even"' : 'class="odd"'; ?>>
<td><?php echo $row['date'];?></td>
<td><?php echo $row['nettotal'];?></td>
<td><?php echo $row['petrol'];?></td>
<td><?php echo $row['net'];?></td>
 </tr>
 <?php } while ($row = mysql_fetch_assoc($sql)) ?>
<tr>
<td><span class="trr">TOTAL</td>
<td><span class="trr"><?php echo $row2['Totalgross'];?></td>
<td><span class="trr"><?php echo $row2['Totalpetrol'];?></td>
<td><span class="trr"><?php echo $row2['Totalnet'];?></td>
 </tr>
</table>

<?php
echo "Average Per Shift $" . $row4['average'];
?>

</td>
<td>

Edited September 23, 2012 by sparz14

[jcbones]

So the table is really a calendar in a table format. You would need to check the database date against the display date, if it doesn't exist then you would write an empty row.

[sparz14]

How would i go about this? Do i need to create an array and fill it with dates, then compare to my database? I'm really new to this

[Christian F.]

Instead of trying to create the calender in the database, you should only store the actual events.

Then when you're using PHP to create the calender, you just check against the data you've fetched from the database, to see if a slot has been taken.

You only need to query the database once, to get all of the (relevant) data from it. From there on you should do all of the checking in PHP.

 

You're thinking about this backwards, which is making the whole problem seem a lot more complex than what it is.

Just imagine you're making a template for a physical list: You wouldn't need to check the driving lists for each row when making the template, but only when filling the data out.

Do the same in the code, only that you do it on a per-field basis. So make a field then, if it's taken, add the data to it.

[Barand]

How would i go about this? Do i need to create an array and fill it with dates, then compare to my database? I'm really new to this

 

Before querying the database, generate an array with the dates for that that month as the keys.

When you iterate through the query results place the data in the array using the date keys. Where there is no data for a date you will have the date key but an empty value. Create your HTML table from the array.