Return results per week using start date PHP

Question

I'm trying to grab SQL data via PHP with a tally for case types each week to display like so:

Week 1 | Date From | Volume
Week 2 | Date From | Volume
Week 3 | Date From | Volume

and so on... without having to manually for each week. I have week number variables set as the business Year starts in July, so Week 1 is the first week in July. Ideally I'd like to use the company weeks but will settle for start of normal year. I've started with this:

    SELECT YEARWEEK(date) as weekNum, MIN(sr_mob.`date`) as start_date, 
    count(*) as numRecords 
    FROM sr_mob
    WHERE outcome='Escalated'
    GROUP BY YEARWEEK(date)

This gives me the return data, but the start_date varies depending on when first entry was that week.

Is there any way to define a week in PHP then query the table (which doesn't contain the week numbers) to get what I'm after? Or does this sound like I'll manually have to request each week...

I can run a single query with say:
$Week1 ($week1=20180731-7;)

I guess what I am looking for is a way of doing a for each or while, using the $week variable, without having to write out 52 variables, if that makes sense.

See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Jul 31 '18 at 15:27

score 0 · Accepted Answer · edited Jul 31 '18 at 15:32

0

Using reference from: https://stackoverflow.com/a/30373395/2397717

SELECT 
    YEARWEEK(date) as weekNum, 
    STR_TO_DATE(CONCAT(YEARWEEK(date),' Monday'), '%X%V %W') as start_date, 
    count(*) as numRecords 
FROM sr_mob 
WHERE outcome='Escalated' 
GROUP BY YEARWEEK(date)

edited Jul 31 '18 at 15:32

Jochem Kuijpers

1,770
3
17
34

answered Jul 31 '18 at 15:29

Sookie Singh

1,543
11
17

@Jochem Kuijpers is there anyway to specify the year start as another date. In this query the year starts on 1st Jan, can that be changed so the year starts 1st Jul? So weekNum1 would be 1st week in July (currently showing as week 26)? If not, that's fine I can work with this, thanks for your help, much appreciated. – gdr39 Jul 31 '18 at 16:00
2

I always think it's asking for trouble to fail to include in the GROUP BY a non-aggregated column from the select - even where there's a seemingly clear functional dependency – Strawberry Jul 31 '18 at 16:27
1

@gdr39 I merely formatted the answer. I haven't checked it for correctness. As @Strawberry points out; this query has undefined behavior because it groups without using an aggregate function. The MySQL documentation specifies that an arbitrary value will be used to represent the group (use `MIN(date)` to use the minimum value, for example). If you want a fully correct answer, you're going to have to give us more details. What does your database schema look like, etc. See Strawberry's initial comment on your question. – Jochem Kuijpers Aug 01 '18 at 12:03

Return results per week using start date PHP

1 Answers1