How to get results from database and display like this?

Hi all

Id like to get all the rows from a database table and display in html tables.

Heres my database table:
db-view

I want it outputting like the below:

So far Ive got a DISTINCT pulling the years and looping through these. But I guess within each iteration of the loop I need to get all the months and prices within that year and loop through those.

Could someone show me how to do this and echo it out to display like the html tables in the above screenshot?

Thanks

What code do you have so far?

Haha. Like I say - I havnt got too far. This is what I have:

$getYears = $db->query("SELECT DISTINCT year FROM standard_prices");
$getMonths = $db->query("SELECT month_numbers FROM standard_prices");
$getWeekPart = $db->query("SELECT part_of_week FROM standard_prices");
$getPrice = $db->query("SELECT price FROM standard_prices");

foreach ($getMonths as $month) {
	$monthNum = $month['month_numbers'];
	$monthName = date("F", strtotime('00-'.$monthNum.'-01'));
	echo $monthName . "<br>";
}
foreach ($getYears as $year) {
		echo "<h2>" . $year['year'] . "</h2>";
}

So Ive basically got as far as pulling data from table, a couple of loops, but thats it. :sleepy:

I would consider altering the database layout. Have a read up on database normalisation - basically once you start having multiple entries the same, you should look at storing them in a separate table and joining them in your query. Text strings like “MID WEEK” should be elsewhere, and so should “Mon - Thu”.

You may consider that it’s not worth thinking about at this stage, but consider this - you probably (maybe not now, but at some point) need to sort your results so that they are ordered by that “part_of_week” column. Right now, you can’t do it because if you sort them alphabetically, they’ll come out wrong. If you assigned values to those text strings and used those instead, suddenly you can sort them in either order, just by adding ORDER BY on the part_id column:

part_id    part_name
   1       Mon-Thu
   2       Fri
   3       Sat

Back to the query, though, you could combine those four queries into a single one, as the data is all coming from the same table at this point. There is no need to get each column separately, just list the columns you do want, with commas between them:

select id, firstname, lastname from people

Thanks. Unfortunately changing the schema is not an option. (it feeds an existing system).

I could be wrong, but I don’t think this is yet PHP. IMHO, from reading the image and queries, this is database at this stage.

I’m not seeing it, please explain the difference between the name and part_of_week fields. They look to always be the same pairings which doesn’t make sense to me.

Anyway, you want to display aggregates, groups and sums.

I usually start by listing the field names I need with table qualifiers if applicable. eg.

main th - year
colgrps - from month_numbers
rowgrps - from name or part_of_week
tds - sum of price

Some of the “heavy lifting” can be taken off of PHP and put on to the database.

@Bolton has said that the database layout is beyond their control. But you’re quite right, presumably the part_of_week column was added after lots of people asking what “Midweek” meant.

1 Like

Like Mitt says, most of that can be done via the database query. Do a search for PIVOT TABLE with your the DBMS you’re using and it should get you started.

Or you can do something like (off the top of my head) - sum may not be the right choice as it’s unclear what you’re looking for.

SELECT part_of_week
     ,  SUM (CASE WHEN month_numbers IN (1,2,12) THEN price ELSE 0 END) AS PriceOne
     ,  SUM (CASE WHEN month_numbers IN (3,10,11) THEN price ELSE 0 END) AS PriceTwo
     ,  SUM (CASE WHEN month_numbers IN (4,5,6, 9) THEN price ELSE 0 END) AS PriceThree
     ,  SUM (CASE WHEN month_numbers IN (7,8) THEN price ELSE 0 END) AS PriceFour
  WHERE year = 2020
  GROUP BY part_of_week

Or if the prices are the same and you’re just looking for a distinct price, then just use it without the sum

SELECT part_of_week
     ,  CASE WHEN month_numbers IN (1,2,12) THEN price ELSE 0 END AS PriceOne
     ,  CASE WHEN month_numbers IN (3,10,11) THEN price ELSE 0 END AS PriceTwo
     ,  CASE WHEN month_numbers IN (4,5,6, 9) THEN price ELSE 0 END AS PriceThree
     ,  CASE WHEN month_numbers IN (7,8) THEN price ELSE 0 END AS PriceFour
  WHERE year = 2020
  GROUP BY part_of_week
Edit:

Fixed typos because I’m an idiot

1 Like

That’s a very odd metric, and @Bolton may need to corroborate some info here.

Let’s take “Prices 2020” from your first picture.
Your first column is “January, February, and December, up to 13th Dec”.

  • Where did the “13th” come from? Your data is already aggregated to the month level.
  • What about data from the rest of December 2020? Is it included in the table somewhere? Does it get added into 2021 12? 2021 01? Or are there duplicate rows? An ‘additional month’?

Thanks for all your input so far guys.

Ill try and add a bit more details for you:

  1. I cant change the schema becuase the table is feeding another platform. (I inherited this site/platform so I wouldnt have set the schema up like this).

  2. The first image shows the table I need to get data out of (the table is much bigger, Ive cropped out loads of records); the second image shows how I want it displayed - it is NOT what is currently displayed form the database - its just a screenshot showing how I would like the data to be displayed.

  3. If this can all be done from 1 query then great. To my mind (and I may be entirely wrong) the code should do something like this: Loop through all DISTINCT years (and echo them out as H2’s). Within each year loop through all DISTINCT months (and echo them out as TH’s). Within each distinct month loop through all DISTINCT Week Parts (and echo them out as table rows with the relevant price in each cell). But Im hideously stuck on how to do this! :slight_smile:

Well the questions i posed were not to suggest changes to the schema, it was to understand your schema. If the ‘other half of December’ is in the database, we need to know A: Where it goes, and B: What it’s listed in the database as.

Also what engine is powering this database? Is it a MySQL Database, a SQL Server, etc… because that will change what database query options are available, and how streamlined it can be made.

Hi @m_hutley !

The database is MariaDB (and Im using phpMyAdmin).

Here is a bigger screenshot:


(Ive highlighted the fact that there are 6 more pages of rows)

Yeah, but i spy with my little eye, an issue that would have tripped Dave’s query up.

What is the column type on month_numbers?

If that’s the way it’s stored in the database, it’s IMPOSSIBLE to get the report as requested as the months are combined in a manner which doesn’t match the oddball month combinations in the report…

I should have perhaps included this in my OP:

Right, but here’s where we have a problem:
Take a look at row ID 13 in your table.

month_numbers = “6,7,8”…so June, July, August.
How much of that 3750 is June? Cause that’s supposed to go in the third column.
How much of it is July and August? Cause that’s supposed to go in the fourth column.
(This, by the way, is why people have been bringing up normalizing your database schema.)

1 Like

Good catch. Perhaps it would be clearer if @Bolton could show us the database table entries for the data that is shown in the example tables.

The tables in the images are from another website. Theyre not being fed from this database. Im just including them to show how I want them to look. (apologies if I didnt make that clear in my previous posts)

So… if you want a query that will give you your data in a way that is easy to parse into a given output… we kind of need to know what you ACTUALLY want your output to be.