Member Avatar for Kando_1

Hi Guys,

Im not sure how to word the title of this question, but i have a table called "daily_recyclables". Inside this table, i have multiple columns of recyclable materials (example copper, stainless steel, brass etc etc)

In my PHP application, i have a statistic card (like below) that is supposed to display the total weight of each material in that table based on the current month we are in.

Heres a screenshot of the daily_recylables page to give you an idea:

This is my attemp to achieve that which is not working at all, and i do not even know how to make it display based only on entries in the current month automatically.

 <?php
$hostname="localhost";
$username="EXAMPLE";
$password="EXAMPLE";
$db = "EXAMPLE";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT SUM(weight) 
FROM subgrade_bin WHERE subgrade = ?') as $row) {
echo "<tr>";
echo "<td>" . $row['SUM(weight)'] . "</td>". " KG";
echo "</tr>"; 
}
?>

Can someone guide this newby to some resources to assist me on how to do this?

My Table Columns are:
recID (INT AUTO INCREMENT), date,subgrade,castaluminium,copper,stainlesssteel,plastic,batteries,brass,lowgradePCB,mediumgradePCB,highgradePCB.

  • 2 Contributors
  • 1 Reply
  • 85 Views
  • 3 Hours Discussion Span
  • Latest Post Latest Post by Dani

Dani AI

Generated

posted a setup where each material is its own column and wants a per-material total for the current month; asked a good clarifying question about what each row represents. Two practical paths: a quick fix (keep the current columns and aggregate one column filtered to the current month) or a longer-term fix (normalize to a long table with one row per material entry — date, material_type, weight — which makes monthly/grouped reporting much easier).

A compact, index-friendly SQL pattern (use your actual table/column names in place of the placeholders) is to sum the material column over a month-range and return zero when nothing exists:

SELECT COALESCE(SUM(`material_column`), 0) AS total
FROM `your_table`
WHERE `date_column` >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
  AND `date_column` < DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 1 MONTH);

Range filters like the above are preferred for performance over wrapping the date column in functions. See MySQL documentation for aggregate behavior and date functions for details: MySQL SUM and aggregate notes and MySQL date/time functions.

PHP tips: use prepared statements and parameter binding, fetch the single aggregate with fetchColumn() (or equivalent), and ensure the weight column is a numeric type (DECIMAL is recommended). Treat NULL results with COALESCE in SQL or by casting to 0 in PHP. For maintainability and flexible reporting, plan a migration to a normalized material/weight table so a single GROUP BY query can produce all material totals per month.

Member Avatar for Dani

Hi,

I'm really good with MySQL, but I need some more clarification from you as for what you're trying to do. Unfortunately I woke up with a really bad headache, and so I'm having a hard time understanding while my head is throbbing.

So I'm going to include the screenshot here for everyone to see in case it gets taken down from ibb.co:

ecemsimage.jpg

So you have one table called daily_recyclables ... You say you have multiple columns of materials. I'm assuming you have the columns set up as: recID (INT AUTO INCREMENT), date,subgrade,castaluminium,copper,stainlesssteel,plastic,batteries,brass,lowgradePCB,mediumgradePCB,highgradePCB. Can you please clarify what each row of the table represents? What is a recID?

Your query says SELECT SUM(weight) FROM subgrade_bin WHERE subgrade = ? but what is subgrade_bin??

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.