asked on

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

Chris Stanyon

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

j8547

ASKER

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

Chris Stanyon

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.

j8547

ASKER

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

j8547

ASKER

Sorry i forgot my query is only pulling 2 months data....Jut not getting any error or output..

Chris Stanyon

Post up your full code here and I'll take a look

j8547

ASKER

I have attached the new one with your added code and my old one.
index.php
index---Copy.php

ASKER CERTIFIED SOLUTION

Chris Stanyon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

j8547

ASKER

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

j8547

ASKER

Silly mistake. Got it to work. Just didnt have <?php

Think I have been looking at it too long!! Thank you

Chris Stanyon

No worries. Glad you got it working :)