Member Avatar for shamsidah

hello... i have a question about my final year project..
in my database i want to find 5 maximum values from total of defects from car's body...
and the data that i'm found must valid for one year, and after one year (next year) maybe the 5 max values may changed. My problem now, how the data can fixed for one year only and the next year can be a another values.

my coding here

<?php
include 'connection.php';
$j = "SELECT defect_code,total FROM grandTotal ORDER BY total DESC LIMIT 5";
$h = mysql_query($j);
while ( $rws = mysql_fetch_array($h)){
echo $rws['defect_code']." = ".$rws['total'];
echo "<br/>";}


?>
  • 3 Contributors
  • 4 Replies
  • 108 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by Member #120589

Dani AI

Generated

Short answer: each record needs a date so you can decide which year it belongs to. As pointed out, you can’t reliably pick “top 5 for 2011” if there’s no date attached to the rows. If grandTotal is an aggregate (no per-event timestamps), either start storing per-defect events with timestamps from now on, or take yearly snapshots of the aggregates so you can “freeze” the top-5 for each year.

If you store each defect occurrence, keep a timestamp column and compute the top 5 by year with a GROUP BY and a date-range WHERE clause (this lets MySQL use indexes). Example query for the current year:

SELECT defect_code, SUM(quantity) AS total
FROM defect_events
WHERE occurred_at >= CONCAT(YEAR(CURDATE()), '-01-01')
  AND occurred_at <  CONCAT(YEAR(CURDATE()) + 1, '-01-01')
GROUP BY defect_code
ORDER BY total DESC
LIMIT 5;

Make the timestamp automatic so you don’t rely on the user PC clock: add a DATETIME/TIMESTAMP column with DEFAULT CURRENT_TIMESTAMP (or set the value from PHP using server time). Example:

ALTER TABLE defect_events
  ADD COLUMN occurred_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

Performance notes and practical options: avoid wrapping the date column in functions like YEAR(occurred_at) in the WHERE clause (that prevents index use). Prefer date-range checks or add a small indexed year column that you populate on insert (or use a generated/stored column on supported MySQL versions). If you cannot change historical data, create an annual_top5 snapshot table and schedule a cron job or MySQL event to store each year’s top five so past years remain “fixed.” This also solves the “fixed for one year” requirement without relying on client clocks.

If you want, use the above approach and show how your current grandTotal maps to either a per-event table or an annual snapshot. As observed, without some date reference you can’t check by year.

Recommended Answers

All 4 Replies

Member Avatar for kevindougans

you'll need to add some time/date columns to the database so you can do something like:

$j = "SELECT defect_code,total FROM grandTotal WHERE year = '2011' ORDER BY total DESC LIMIT 5";
Member Avatar for shamsidah

if i dont want to add field date/time, its cant be run or not? i want to generate it automatically..based on PC time.. it is flexible?

Member Avatar for kevindougans

if i'm understanding the question correctly then no. regardless if its pc time or server time each record will have to have a date associated with it, it's not like you can right click the entries and look at the properties and it will show a date. it would have to be a column in the database.

Member Avatar for Member #120589

How do you propose to check the table without a date reference?

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.