Data arranged by day MySQL

Question

I have a MySQL Database with data storing things such at sales of items.

I wish to produce an html display table based on a per day sales of each of the items.

How do i produce a table of data to include the dates of where no items exist in the database on some of the days?

Example:

01-05-14 - 1 Sold
02-05-14 - 4 Sold
03-05-14 - 0 Sold
04-05-14 - 2 Sold
05-05-14 - 0 Sold

I can produce a table based on the data stored in the tables, but how do i manage the data based on dates that do no exist in the tables?

Would i be correct in assuming that i should produce a table with the dates first and then do a MySQL query based on each of those dates?

You already have the dates with 0 items, or are you trying to interpolate them from the dates with >0 items? — shree.pat18, Jun 02 '14 at 10:37
Could you confirm you're using a `date` type for your date, and not varchar or whatever. — scragar, Jun 02 '14 at 10:38
the data stored in the table sonly stores item sales. So on a day without trades, theres no entries for that date. I am trying to find the correct way to ensure if there no data for a date that the date still appears in the table but shows '0' for the total. @shree.pat18 — Derple, Jun 02 '14 at 10:39
yes, i am using `timestamp` as each transaction enters the DB @scragar — Derple, Jun 02 '14 at 10:39
what is the range of dates , i mean is it a month or year or so — ashok_p, Jun 02 '14 at 10:49
the range of dates will be daily, weekly and monthly, but each of those can be independent search terms. @ashok_p — Derple, Jun 02 '14 at 10:52
with PHP. The question is directed at 'the correct way' to achieve this result. I am guessing that producing the table withing PHP first and fetching results based on those dates by the MySQL is the right way, but i am querying if this is the correct & most efficient way? @Strawberry — Derple, Jun 02 '14 at 11:02
possible duplicate of [Mysql: Select all data between two dates](http://stackoverflow.com/questions/1080207/mysql-select-all-data-between-two-dates) — Marcus Adams, Jun 02 '14 at 13:05
@MarcusAdams Not really as its not at all the question. Perhaps you didnt read the question correctly. — Derple, Jun 02 '14 at 14:10

ashok_p · Accepted Answer · 2014-06-02T11:49:38.277

I think this may solve your problem,
use the dateRange function to get the list of date and use the

<?php

function dateRange( $first, $last, $step = '+1 day', $format = 'd-m-y' ) {

    $dates = array();
    $current = strtotime($first);
    $last = strtotime($last);

    while( $current <= $last ) {

        $dates[] = date( $format, $current );
        $current = strtotime( $step, $current );
    }

    return $dates;
}

//print_r( dateRange( '2010-07-26', '2010-08-05') );
?>

here is the code to print html table

 <table cellpadding="0" cellspacing="0" width="100%" class="sortable">

                            <thead>
                                <tr>
                                    <th>day</th>
                                    <th>no. items sold</th>

                                </tr>
                            </thead>

                            <tbody>
    <?php

            $dates=dateRange('2014-07-26','2014-07-30');\\dateRange('start date','end date')
          $result = mysqli_query("SELECT day,items FROM items_table");\\i just used a dummy table use your table here

 $output='';
          while ($row = mysqli_fetch_array($result)){
          if($dates[i]==$row['day'])
          {
            $output .= '
          <tr>
          <td>' . $row['day'] . '</td>
          <td>' . $row['items'] . '</td>                                                   
          </tr>';
          }
          else
          {
            $output .= '
          <tr>
          <td>' . $dates[i] . '</td>
          <td>0</td>                                                   
          </tr>';
          }
    $i++;
          echo  $output;
      }

    ?>
    </tbody>
    </table>

Data arranged by day MySQL

1 Answers1