• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 41
  • Last Modified:

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
0
j8547
Asked:
j8547
  • 6
  • 5
1 Solution
 
Chris StanyonCommented:
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 StanyonCommented:
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
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial 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 StanyonCommented:
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 StanyonCommented:
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
 
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 StanyonCommented:
No worries. Glad you got it working :)
0
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.

Join & Write a Comment

Featured Post

Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now