Member Avatar for ethan.david.376

How can I generate a weekly report given an end date and a start date? How should the query be?
I try this one but it's returning an error.
"SELECT * FROM table WHERE date >= startdate AN date<='enddate GROUP BY WEEK BETWEEN startdate AND enddate"

  • 3 Contributors
  • 3 Replies
  • 179 Views
  • 10 Hours Discussion Span
  • Latest Post Latest Post by veedeoo

Dani AI

Generated

The original SQL posted by had a few syntax and logic problems (mis-typed AND, quoted the column as a string, and tried to put BETWEEN into GROUP BY). was headed in the right direction by using a date-range, and 's question about quoting is important — identifiers (column names) are not the same as string/date literals.

A robust approach is to group rows by an ISO week label and use a half-open date range so DATETIME values on the end date are not accidentally dropped. Example SQL (parameterized):

SELECT DATE_FORMAT(`date_col`, '%x-%v') AS week_label,
       COUNT(*) AS row_count,
       SUM(amount) AS total_amount
FROM your_table
WHERE `date_col` >= :start_date
  AND `date_col` < DATE_ADD(:end_date, INTERVAL 1 DAY)
GROUP BY week_label
ORDER BY week_label;

Prefer prepared statements rather than interpolating variables. Example PDO flow (sketch):

$pdo = new PDO(...);
$stmt = $pdo->prepare("...SQL above...");
$stmt->execute([':start_date'=>$start, ':end_date'=>$end]);
$weeks = $stmt->fetchAll(PDO::FETCH_ASSOC);

Troubleshooting / notes:

  • If the column name is literally date, quote it as an identifier with backticks: `date`; do not put it in single quotes.
  • DATE_FORMAT('%x-%v') yields a year-week label that handles year boundaries correctly (ISO weeks). YEARWEEK() is an alternative but watch the mode number.
  • For pure DATE columns BETWEEN can work, but for DATETIME prefer the half-open range shown above.
  • Test the final SQL in phpMyAdmin or the mysql client to confirm grouping and ordering before wiring it into PHP.

Recommended Answers

All 3 Replies

Member Avatar for veedeoo

Something like this... I am not 100% sure, but please try running it on the phpMyAdmin or the command line and see.
The date is a table column.

 $beg_date = '2014-03-01';
 $end_date = '2014-03-07';

"SELECT * FROM YOUR_TABLE_NAME WHERE 'date' BETWEEN $beg_date AND $end_date ";
Member Avatar for Member #120589

Does it need single quotes around the vars?

Member Avatar for veedeoo

oh yeah, please wrap those vars with single quotes. Got carried away with the dumb laws thread at the geek lounge :).

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.