I want to make a simple bar chart of some statistics in my database. More specifically: it's a family tree, and I want to show the birth frequency per month. I managed to create the SQL query to get a list of the months with their respective numbers of births. Now I want to add a coloured bar, where the highest number is 100% width, and the others are proportional.
To calculate the percentage, I need to get the largest number from the query result. I do not, however, need to display that. I only need the number to use it to calculate the width of the bar.
I have tried different solutions with MAX(), but these were all set to display only the maximum value of the COUNT(). As mentioned, I want to display the full result, and only need the MAX() internally for calculations. I do not need to display it.
It is easy to retrieve the number by doing a SELECT() query ordering by the COUNT() and limiting to the first result. As the needed MAX() result is only one number, I cannot do a UNION with the other SELECT query that gets me the numbers per month. Second problem is that the results to be displayed are not ordered by COUNT(), obviously, but by month. So at this time the MAX() number would be the third result, though that can change when I add to my database.
<?php
require_once(ABSPATH . 'wp-settings.php');
$connection = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
mysqli_select_db($connection, DB_NAME);
$query = "SELECT
MONTHNAME(birthdatetr) AS Month_of_Birth,
MONTH(birthdatetr) AS number_of_month_of_birth,
COUNT(*) AS Total
FROM
tng_people
WHERE
MONTH(birthdatetr) > 0
GROUP BY
number_of_month_of_birth
ORDER BY
number_of_month_of_birth";
$result = mysqli_query($connection, $query);
$barwidth = floor($Total / $birthmax * 100);
?>
<?php foreach ($result as $row){
echo "{$row['Month_of_Birth']} ({$row['Total']})
<td class=\"bar-holder\"><div style=\"width:{$barwidth}%;\" class=\"bar rightround\"></div></td>";
}
?>
This is the code I have to display my list of months. I have named the max-variable "birthmax", I just don't know how to assign a value to it. The value should be the largest result of the "Total". "Barwidth" is the calculated size of the coloured bar I want to display. With the code as is, so lacking a value for birthmax, the bars, months and numbers are displayed, but the bar is at full width, saying that the value assigned to barwidth is not a number (NAN).
If you're using MYSQL 8.0 and above, you can use WITH to create two tables, the table you already made and a table that only has the max value. Then cross join the two tables to add the max value to each row.
You can also select Total / max_total to get a percentage between 0 and 1.
WITH query_data AS (
SELECT
MONTHNAME(birthdatetr) AS Month_of_Birth,
MONTH(birthdatetr) AS number_of_month_of_birth,
COUNT(*) AS Total
FROM
tng_people
WHERE
MONTH(birthdatetr) > 0
GROUP BY
number_of_month_of_birth
),
max_count AS (
SELECT MAX(Total) AS max_total FROM query_data
)
SELECT query_data.*, max_count.max_total
FROM query_data CROSS JOIN max_count
ORDER BY
query_data.number_of_month_of_birth
For mysql < 8.0 you can join the two tables like this:
SELECT query_data.*, max_count.max_total
FROM (
SELECT
MONTHNAME(birthdatetr) AS Month_of_Birth,
MONTH(birthdatetr) AS number_of_month_of_birth,
COUNT(*) AS Total
FROM
tng_people
WHERE
MONTH(birthdatetr) > 0
GROUP BY
number_of_month_of_birth
) query_data
CROSS JOIN (
SELECT MAX(tmp_max.Total) AS max_total FROM
(
SELECT
MONTHNAME(birthdatetr) AS Month_of_Birth,
MONTH(birthdatetr) AS number_of_month_of_birth,
COUNT(*) AS Total
FROM
tng_people
WHERE
MONTH(birthdatetr) > 0
GROUP BY
number_of_month_of_birth
) tmp_max
) max_count
ORDER BY
query_data.number_of_month_of_birth