Complex select query question.

[mediamind]

This is the query i use at the moment, is it possible to have it print out all days of the particular month and have 0 for the days there are no results? instead of what it does now only the days when there are results.

mysql_query("SELECT page, COUNT(id) AS cnt, DATE_FORMAT(time_stamp,'%D') AS day FROM track_detail WHERE page='$page' AND DATE_FORMAT(time_stamp,'%c') = '$month' AND DATE_FORMAT(time_stamp,'%Y') = '$year' GROUP BY DATE_FORMAT(time_stamp,'%d') ORDER BY day ASC");

[shanx24]

Can you briefly share your table design, and explain your INTENDED RESULT a bit? Looks like it is possible but am not sure what you want to achieve.

In any case, I dont have the table, but could you tell me what's the result of running the following query:

select
page
,truncate(sum(case lcase(substring(dayname(tsr_date), 1, 3)) when 'mon' then id else null end), 1) Mon
,truncate(sum(case lcase(substring(dayname(tsr_date), 1, 3)) when 'tue' then id else null end), 1) Tue
,truncate(sum(case lcase(substring(dayname(tsr_date), 1, 3)) when 'wed' then id else null end), 1) Wed
,truncate(sum(case lcase(substring(dayname(tsr_date), 1, 3)) when 'thu' then id else null end), 1) Thu
,truncate(sum(case lcase(substring(dayname(tsr_date), 1, 3)) when 'fri' then id else null end), 1) Fri
,truncate(sum(case lcase(substring(dayname(tsr_date), 1, 3)) when 'sat' then id else null end), 1) Sat
,truncate(sum(case lcase(substring(dayname(tsr_date), 1, 3)) when 'sun' then id else null end), 1) Sun
from
track_detail
where
page='$page'
and date_format(time_stamp,'%c') = '$month'
and date_format(time_stamp,'%Y') = '$year'
group by
date_format(time_stamp,'%d')
order by
day ASC
;

If this works, then maybe you get the idea?

Shanx

[shanx24]

Er...try this SQL please.

select
page
,sum(case lcase(substring(dayname(time_stamp), 1, 3)) when 'mon' then id else null end) Mon
,sum(case lcase(substring(dayname(time_stamp), 1, 3)) when 'tue' then id else null end) Tue
,sum(case lcase(substring(dayname(time_stamp), 1, 3)) when 'wed' then id else null end) Wed
,sum(case lcase(substring(dayname(time_stamp), 1, 3)) when 'thu' then id else null end) Thu
,sum(case lcase(substring(dayname(time_stamp), 1, 3)) when 'fri' then id else null end) Fri
,sum(case lcase(substring(dayname(time_stamp), 1, 3)) when 'sat' then id else null end) Sat
,sum(case lcase(substring(dayname(time_stamp), 1, 3)) when 'sun' then id else null end) Sun
from
track_detail
where
page='$page'
and date_format(time_stamp,'%c') = '$month'
and date_format(time_stamp,'%Y') = '$year'
group by
date_format(time_stamp,'%d')
order by
day ASC
;

[[deleted]]

Tricky, because if the database has no records for a particular day, how can the query know it's supposed to give some sort of result for that day? 🙂

You could do it like shashank says, put a seperate statment in for each day, but at 31 days per month... ouch..

You could also create a new table that contains 365 records with just the month/day combinations of all the days of the year.
Then use a JOIN to combine the two tables matching on the month and the day. That way the query will always have at least one match in one table for each day of the month.

[mediamind]

DB table track_detail:
id varchar(128)
ip varchar(64)
time_stamp timestamp(14)
page varchar(64)
phpself varchar(128)

[kparker]

You could do all that, but a very simple control-break look can add the needed zeros at runtime. Just make sure the query retrieves the day-of-month (or the underlying full date so you can calculate it) then say:

$prev_date = 0;
while ( /your retrieve-rows statement here / )
  {
&nbsp;&nbsp;for ( $prev_date++; $prev_date < $date; $prev_date++)
&nbsp;&nbsp;&nbsp;&nbsp;output_date( $prev_date, ... );
&nbsp;&nbsp;output_date( $date, ... );
&nbsp;&nbsp;}

Or, it might be easier to turn the loop inside out and drive it by the days-of-month (do you always put out a full calendar?) and then inside the loop call the fetch statement.

[shanx24]

My humble 0.02 - I think thats very kludgish, Kirk. There is little you can do with the data. However if you follow an SQL like the following, you can get the values from the query (an array), and draw charts etc pretty straight-forwardly, for instance.

select
page
,count(page) AccessCount
,sum(case date_format(time_stamp,'%e') when 1 then 1 else 0 end) Day1
,sum(case date_format(time_stamp,'%e') when 2 then 1 else 0 end) Day2
,sum(case date_format(time_stamp,'%e') when 3 then 1 else 0 end) Day3
,sum(case date_format(time_stamp,'%e') when 4 then 1 else 0 end) Day4
,sum(case date_format(time_stamp,'%e') when 5 then 1 else 0 end) Day3
,sum(case date_format(time_stamp,'%e') when 6 then 1 else 0 end) Day6
,sum(case date_format(time_stamp,'%e') when 7 then 1 else 0 end) Day7
,sum(case date_format(time_stamp,'%e') when 8 then 1 else 0 end) Day8
,sum(case date_format(time_stamp,'%e') when 9 then 1 else 0 end) Day9
,sum(case date_format(time_stamp,'%e') when 10 then 1 else 0 end) Day10
,sum(case date_format(time_stamp,'%e') when 11 then 1 else 0 end) Day11
,sum(case date_format(time_stamp,'%e') when 12 then 1 else 0 end) Day12
,sum(case date_format(time_stamp,'%e') when 13 then 1 else 0 end) Day13
,sum(case date_format(time_stamp,'%e') when 14 then 1 else 0 end) Day14
,sum(case date_format(time_stamp,'%e') when 15 then 1 else 0 end) Day15
,sum(case date_format(time_stamp,'%e') when 16 then 1 else 0 end) Day16
,sum(case date_format(time_stamp,'%e') when 17 then 1 else 0 end) Day17
,sum(case date_format(time_stamp,'%e') when 18 then 1 else 0 end) Day18
,sum(case date_format(time_stamp,'%e') when 19 then 1 else 0 end) Day19
,sum(case date_format(time_stamp,'%e') when 20 then 1 else 0 end) Day20
,sum(case date_format(time_stamp,'%e') when 21 then 1 else 0 end) Day21
,sum(case date_format(time_stamp,'%e') when 22 then 1 else 0 end) Day22
,sum(case date_format(time_stamp,'%e') when 23 then 1 else 0 end) Day23
,sum(case date_format(time_stamp,'%e') when 24 then 1 else 0 end) Day24
,sum(case date_format(time_stamp,'%e') when 25 then 1 else 0 end) Day25
,sum(case date_format(time_stamp,'%e') when 26 then 1 else 0 end) Day26
,sum(case date_format(time_stamp,'%e') when 27 then 1 else 0 end) Day27
,sum(case date_format(time_stamp,'%e') when 28 then 1 else 0 end) Day28
,sum(case date_format(time_stamp,'%e') when 29 then 1 else 0 end) Day29
,sum(case date_format(time_stamp,'%e') when 30 then 1 else 0 end) Day30
,sum(case date_format(time_stamp,'%e') when 31 then 1 else 0 end) Day31

from
track_detail
where
date_format(time_stamp,'%c') = '4'
and date_format(time_stamp,'%Y') = '2001'
group by

page

order by
AccessCount desc
;

Hope this helps posterity. 🙂
<Shanx/>

[[deleted]]

Another complex way of doing it :-)
Remember we are talking about months, so it's not always 31 days.
Now PHP would have to figure out how many days to put in, and change the
query accordingly.
What's more, the results would also need to be interpreted differently depending on the number of days.

Kirks PHP solution is probably simplest. Just get the days that are in the database and 'manually' print the ones that aren't in there.

PS: this is MySQL we are talking about so the example you posted won't work.

[shanx24]

Would it really matter if you chose february, and it showed that there are 0 results for the 29th, 30th and the 31st of that month?

Of course, if the correct no. of days in a month is very important, then that can easily be added in the SQL that is sent to the server itself..(remember that ultimately this SQL will be composed within the program as well).

Well...different ways of doing the same thing I guess. I prefer to have the load at the database level, if possible, so that the database cache etc takes care of "high voltage" parsing/calculations, and at the app level, only the "doing something with results" is necessary...

If the results are going to be used for something other than mere display (for instance, graphing etc), I'd still think that a standardized SQL is the most maintainable way to go!

My 0.02,
S

PS:
Vincent, had never thought of that 365 day join idea, what an intriguingly weird approach! 🙂 Do I love these boards or what!

[shanx24]

Oh, and the thing I forgot to mention. While helping gerry through email, I figured out that the DB doing the formatting is also good in terms of displaying itself - the script already has sorting by various days, by the total count of each page, by page title etc...sort of cumbersome if I mess around with the results at the app level.

Just a thought..

S

[shanx24]

The above example is straight from MySQL!

S

[[deleted]]

Well it would look rather silly if you reported zero events on february 31st. :-)

Sure, quite often it pays to have as many things as possible done to the data before it leaves the database, but other times you may want to have a more flexible output that you can play with in php.
In the case of a calendar, you are already going to print a formatted list of days that have events, so the loop is already there.
Then adding in a check to get the correct flield from the database is not that difficult.

Your method makes PHP process the query, 'our' method makes PHP process the results. Not that much difference really :-)