Display 2 months data - PHP

I want a webpage that displays 2 months of data. Current month and last month. So right now I would like it to display all of December in 1 table and all of January (currently to 10th and each day new data would add on until end of Jan). On Feb 1st, Dec will disappear and it will show Jan in 1 table and Feb in another and so on.....

I have inherited a web page were it used to show data and it would disappear at the end of the day. I don't know now how to change it to what i need. I have attached the file. Currently it only has 1 table with data. I thought if I figured out how to do current month then i could just add another table with current month -1...

Any help appreciated.
index.php
j8547Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jan LouwerensSoftware EngineerCommented:
You would have to do that in your query (the $sql variable).

I'm assuming you have some kind of date column in the Award table in your database. For the purpose of these examples, I'm using the name Award.[Award_Date] (replace this with the actual name of your date column).

To get the data for the current month, you'd use something like:
SELECT Award.[Number], Award.[Division], Award.[Amount] FROM Award WHERE
   Award.[Award_Date] >= DateSerial(Year(Date()), Month(Date()), 1)
ORDER BY Award.[Amount]

Open in new window


To get the data for the previous month, you'd use something like:
SELECT Award.[Number], Award.[Division], Award.[Amount] FROM Award WHERE
   Award.[Award_Date] >= DateSerial(Year(Date()), Month(Date()) - 1, 1) AND
   Award.[Award_Date] <  DateSerial(Year(Date()), Month(Date()), 1)
ORDER BY Award.[Amount]

Open in new window


Reference: https://www.experts-exchange.com/questions/28540085/Coding-first-and-last-day-of-current-month-in-Access.html
0
PatHartmanCommented:
I just answered this question phrased as ONE selection rather than two.  Looks like Jan offered the same solution given the different problem definition.
0
Julian HansenCommented:
I have taken your code and re-formatted it a bit and included suggestions from Jan above on the filtering.

This should do what you want.

Some notes.

1. I only used the following bit of Jan's code
Award.[Award_Date] >= DateSerial(Year(Date()), Month(Date()) - 1, 1)

Open in new window

I am assuming the data in the database only includes items up to the current date. If this is not the case simply add an AND that excludes items after today.

2. I cleaned up the code in the rendering of the table - there was some superfluous statements

3. I changed the colouring of the rows to use CSS rather than inline styling

4. I have assumed Award_Date is the column containing the date.

Here is the source. I can't provide a working sample as my server does not support Access.
<html>
    <head>
    <title>Awards</title>
        <style>
            h1 {text-align: center;}
            td {font-size:100%; }
            /* tr.rowColor{background-color: #e2e2e2} */
            body {font-family: Arial;}
      tr:nth-child(odd) {background-color: #e2e2e2}
        </style>
        <script>
function startTime() {
    var today=new Date();
    var h=today.getHours();
    var m=today.getMinutes();
    var s=today.getSeconds();
    m = checkTime(m);
    s = checkTime(s);
    h = twhr(h)
    document.getElementById('time').innerHTML = h+":"+m+":"+s;
    document.getElementById('date').innerHTML = today.toDateString();
    var t = setTimeout(function(){startTime()},500);

}

function checkTime(i) {
    if (i<10) {i = "0" + i};  // add zero in front of numbers < 10
    return i;
}
function twhr(i) {
    if (i>12) {i = i-12};  // add zero in front of numbers < 10
    return i;
}


</script>
        <meta http-equiv="refresh" content="300">
    </head>
    <body onload="startTime()" style="text-align: center;">
        <div style="padding: 20px 0 0 10px; margin: 0px; position: absolute; top:0px; left:0px;"><img  src="foreground3.png" height="150px" alt=""  /></div>
         <h1 id="date" style="font-size: 150%; padding:5pt 0px 0px 0px;"></h1>
        <h1 style="font-size: 300%; margin:10pt 0 10pt 0;">Job Awards</h1>
</br></br></br></br></br></br>

<?php
$connStr =
        'odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};' .
        'Dbq=e:\projects\ee\t2904.accdb;';

$dbh = new PDO($connStr);

// REFORMAT QUERY TO BE MORE READABLE USING HEREDOC NOTATION
$sql = <<<QUERY
SELECT
  Award.[Award_Date],
  Award.[Number],
  Award.[Division],
  Award.[Amount]
FROM
  Award
WHERE
  Award.[Award_Date] >= DateSerial(Year(Date()), Month(Date()) - 1, 1)
ORDER BY
  Award.[Award_Date],
  Award.[Amount];
QUERY;

$ans = $dbh->query($sql);
$row = $ans->fetchAll(PDO::FETCH_ASSOC);
/* DON'T NEED THESE ANYMORE
$firstLoop = TRUE;
$coloredRow = FALSE;
*/

// CREATE A HEADING WITH THE MONTH NAME
echo "<h2>" . date('F', strtotime($row[0]['Award_Date'])) . "</h2>";

// SUGGESTION: MOVE THE ATTRIBUTES TO CSS
echo '<table width="100%" border="2" cellspacing="0" cellpadding="4">';

// OUTPUT THE HEADERS
doHeaders($row[0]);

echo '<tbody>';

// FIRST OF THIS MONTH. THIS IS THE DATE WHERE WE 
// CLOSE OFF THE LAST TABLE AND OPEN THE NEXT
$cutoff = date('Y-m-01');

// ITERATE OVER THE ROWS
foreach($row as $rowArray){
  // IF THIS IS TRUE WE ARE AT THE END OF THE PREVIOUS MONTH
  // SO WE CLOSE THE TABLE AND OPEN A NEW ONE FOR THE NEXT MONTH
  if ($rowArray['Award_Date'] >= $cutoff) {
    // SET CUTOFF FOR NEXT MONTH
    $cutoff = date('Y-m-01', strtotime('+1 month'));
    
    // CREATE OUR HEADING
    $heading = date('F', strtotime($rowArray['Award_Date']));
	
	// CLOSE CURRENT TABLE AND OPEN THE NEXT ONE
    echo <<< HTML
    </table>
    
    <h2>{$heading}</h2>
    
    <table width="100%" border="2" cellspacing="0" cellpadding="4">
HTML;
  
    // OUTPUT THE HEADERS
    doHeaders($row[0]);
	
    echo '<tbody>';
  }
  
  // DUMP THE ROW
  // RATHER USE CSS TO STYLE ALTERNATE ROWS.
  echo '<tr>';
  
  // WE KNOW WHICH FIELD IS THE DATE FIELD SO JUST FORMAT IT HERE
  // RATHER THAN CLOGGING THE LOOP WITH UNNECESSARY CODE
  
  $rowArray['Award_Date'] = date('l dS F Y h:i:s A', strtotime($rowArray['Award_Date']));

  // DATE FIELD HAS ALREADY BEEN FIXED SO WE CAN USE implode() TO DO THE DUMP
  echo '<td>' . implode('</td><td>', $rowArray) . '</td>';

  echo "</tr>";
}

echo "</table>";

// FUNCTION: doHeaders($data)
// Dumps headers for a table
// $data: row containing columns for table.
function doHeaders($data)
{
  echo <<< HTML
    <thead>
      <tr>
HTML;
  foreach($data as $column => $data) {
    echo "<th>{$column}</th>";
  }
  
  echo <<< HTML
      </tr>
    </thead>
HTML;

}
?>
        </body>
</html>

Open in new window

Sample Output
ss144.jpg
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

j8547Author Commented:
This works perfectly. Just 1 more question.... Can I get the tables side by side??

Also can you remove the output as i didn't realize the logo name would appear. Thanks
0
j8547Author Commented:
I also wanted to add a table at the top with the total amounts for each division (1 to 4). This would be the totals from the database from start to present not just last 2 months. I can post another questions if that is the best way. Thanks
0
Julian HansenCommented:
I have updated the post to remove the logo and name.

To make them side by side you can use something like Bootstrap which allows you to do that.
Or you could simply put each table in a div with style
div.table-box
{
    box-sizing: border-box;
    width: 50%;
    float: left;
}
div.table-box:after {
   display: table;
   content: " ";
   clear: both;
}

Open in new window

I would put the table code into a function and call it in each block.
As for the other question about another table - best to open another question and refer to this one.
0
j8547Author Commented:
I know this questions is closed but just because you helped with the code. Instead of putting tables side by side I kept them on top of each other. But I want current month on top. Is it easy to manipulate your code to do this or do I need to change it alot as in split out the records as i call them from the database...?
0
Julian HansenCommented:
You would need to change the query to order the the dates descending and then change the logic around the cutoff to be > than the last day of the previous month.
So cutoff would become

$cutoff = date('Y-m-t', strtotime('-1 month');

Open in new window


If you need more detail then open another question and refer to this one.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.