Change display of records in Table - PHP

This is the last question regarding this webpage. I have records from a database for the last 2 months being displayed on a webpage. It shows the older month on top. I tried to put tables side by side which worked but only 1 header would display for some reason. I am ok with the tables being display 1 on top of the other I would like the most recent month on top. Any time i try and change the display the code has an error and displays nothing. I am not sure if I have to break the data out when displaying. I have attached code and screenshot. I would like Jan on top and then December as there will be a lot of records so will probably have to scroll. Help is always appreciated.
Before.jpg
index.php
j8547Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Chris StanyonWebDevCommented:
You'll need to manipulate your data once you've retrieved it from the Db.

Probably the easiest way to do this is to build a new array to represent the data in a manner that suits your needs. I would loop through your records, creating a new key, based on the date, and inside that key, add the collection of records. You can then sort the new array by the key (the date) to get your months in the correct order. Another advantage of this is that you will automatically have an array that represents your data in a hierarchical way, so you don't need to check the dates to open and close the tables.

Have a look through this and see if it makes sense:

<?php
    $ans = $dbh->query($sql);
    $rows = $ans->fetchAll(PDO::FETCH_ASSOC);

    $collection = array(); // Prep the new array
    
    foreach ($rows as $record): // Build a new array using the records from your Db
        $dateKey = DateTime::createFromFormat('Y-m-d', $record['Award Date'])->format('Y-m');
        $collection[$dateKey][] = $record;
    endforeach;
    
    krsort($collection); // Sort the new array in descending order by key (the date)
?>

<?php foreach ($collection as $date => $records): // Loop through the new array to output the records ?>

    <h2><?php echo DateTime::createFromFormat('Y-m', $date)->format('F') ?></h2>

    <table>
        <?php doHeaders($records[0]); ?>

        <?php foreach ($records as $record): ?>
        <tr>
            <td><?php echo $record['Award Date'] ?></td>
            <td><?php echo $record['Job Number'] ?></td>
            <td><?php echo $record['Company'] ?></td>
            <td><?php echo $record['Amount'] ?></td>
        </tr>
        <?php endforeach; ?>
    </table>

<?php endforeach; ?>

Open in new window

0
j8547Author Commented:
Will this not pull everything and sort by date. I just want to always only show current and previous month in 2 tables...as screenshot
0
Chris StanyonWebDevCommented:
The query against your DB stays exactly as you already have it. The code I posted just takes those returned records and re-arranges them so they're grouped by month. You end up with an array something like this:

$collection = array(
    '17-12' = array(
        13th December, 1598.3.2, Company 2, 1500
        20th December, 1256.3.5, Company 1, 2500
    ),
    '18-01' = array(
        3rd January, 1254.0.2, Company 3, 20
        3rd January, 4525.3.1, Company 4, 50
        4th January, 6465.2.1, Company 1, 100
        11th January, 7412.1.2, Company 4, 500
        18th January, 1252.3.1, Company 2, 50
        23rd January, 1522.3.2, Company 1, 100
    )
);

Open in new window

Each key in the index gives you the month for your <H2> elements, and each key contains the records for that month, so you can create your separate tables.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

j8547Author Commented:
Sorry I am a bit confused. If I have 5 months will it not then pull all 5 months instead of the last 2. Does this code replace my code after the query. I have done that and no error, just nothing appears...
0
j8547Author Commented:
Sorry i forgot my query is only pulling 2 months data....Jut not getting any error or output..
0
Chris StanyonWebDevCommented:
Post up your full code here and I'll take a look
0
j8547Author Commented:
I have attached the new one with your added code and my old one.
index.php
index---Copy.php
0
Chris StanyonWebDevCommented:
OK. My guess is the Date stuff is probably off. I don't have access to Access so can't test it properly. Couple of things of note. Firstly, I would turn on error reporting. Right at the very top of your page, add in this:

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);
?>

Open in new window

Also, take a look at this line:

$dateKey = DateTime::createFromFormat('Y-m-d', $record['Award Date'])->format('Y-m');

It basically assumes the date coming out of your Database is in the format of Y-m-d (2018-01-16). If your date is coming out in a different format, then you'll need to adjust the createFromFormat argument (or drop it entirely). Maybe something like this would work:

$dateKey = date('Y-m', strtotime($record['Award Date']));

I would then var_dump the $collection after you've built it just to see what's going on:

foreach ($rows as $record): // Build a new array using the records from your Db
    $dateKey = DateTime::createFromFormat('Y-m-d', $record['Award Date'])->format('Y-m');
    $collection[$dateKey][] = $record;
endforeach;

var_dump($collection);

krsort($collection); // Sort the new array in descending order by key (the date)

Open in new window

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
j8547Author Commented:
it seems to be working except for error Fatal error: Call to undefined function doHeaders(). The tables are printing out but not the headers....Thank you for getting me to this stage. appreciated
index.php
0
j8547Author Commented:
Silly mistake. Got it to work. Just didnt have <?php

Think I have been looking at it too long!! Thank you
0
Chris StanyonWebDevCommented:
No worries. Glad you got it working :)
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
Databases

From novice to tech pro — start learning today.