MySql Query help

[shahzad429]

I have a table with fields Table ID and AccessDate when some one visit the page i add date to the database.

so now i want to display click per day or entries to the database per day.

 

 

In  AccessDate date is stored as 27-05-2012

and this field is varchar(255)

 

 

Please help me with this

 

Thanks in advance

 

[Barand]

Store dates as type DATE and 2012-05-27 format. You cannot correctly sort or compare dates using the format that you have.

 

The query you will then need is

 

SELECT accessdate, COUNT(*) as total FROM tablename GROUP BY accessdate

[shahzad429]

Thanks i will change to Date then.

 

what about time?? how i will store time in database? and will be the type of time?

 

 

 

Thank you again

[Barand]

there is a DATETIME type, format "2012-05-27 10:54:35"

[shahzad429]

Thanks Barand

[Barand]

MySQL Date and Time Types

[shahzad429]

Thanks bro it is working now.

 

i used below query

SELECT month(accessdate) as Month, COUNT(*) as Total FROM log where LinkCode='dcxha' GROUP BY Month

 

and result is good i can see

Month | Total

4            125

5            87

 

but now i am thinking to show even those month on which clicks are 0 like

is it possible??

 

Month | Total

1            0

2            0

3            0

4            125

5            87

6            0

7            0

8            0

9            0

10          0

11          0

12          0

 

[Barand]

create a temp table containing a row for each month

 

<pre>
<?php
include("testDBconnect.php");
error_reporting(-1);
mysql_query("CREATE TEMPORARY TABLE tempmonths (`month` int NOT NULL PRIMARY KEY)");

for ($m=1; $m<=12; $m++) mysql_query("INSERT INTO tempmonths VALUES ($m)") or die(mysql_error());

$sql = "SELECT m.`month`, COUNT(l.accessdate) as total
    FROM tempmonths m 
    LEFT JOIN log l ON m.`month` = MONTH(l.accessdate)
    GROUP BY m.`month`";
$res = mysql_query($sql) or die(mysql_error());

while (list($m, $tot) = mysql_fetch_row($res))  {
    printf ('%2d  %4d <br />', $m, $tot);
}
?>
</pre>

[shahzad429]

thank-you very much it is working perfectly now.

 

 

[shahzad429]

One last question barand 

 

where in below query i can write where LinkCode='dcxha'

 

 

SELECT m.month, COUNT(l.accessdate) as total
    FROM tempmonths m 
    LEFT JOIN log l ON m.month = MONTH(l.accessdate)
    GROUP BY m.month

 

as my query before this left join was

 

SELECT month(accessdate) as Month, COUNT(*) as Total FROM log where LinkCode='dcxha' GROUP BY Month

 

as i want to show count for particular LinkCode

 

Thanks again bro you save my life today by helping me this much

 

[Barand]

SELECT m.month, COUNT(l.accessdate) as total
    FROM tempmonths m 
    LEFT JOIN log l ON m.month = MONTH(l.accessdate) AND l.linkcode = 'dcxha'
    GROUP BY m.month

[shahzad429]

working like a charm

 

 

thanks again

[shahzad429]

barand today i am working on daily report so query i wrote with dummy table is

 

select d.days as Day, count(l.AccessDate) as Total
from tempdays d
left join log l
on d.days=day(l.AccessDate) and l.linkcode='dcxha' and month(l.AccessDate)=4
group by d.days

 

 

it is displaying date 0 to 31 with clicks working fine

but as i have passed month=4 so april have 30 days

 

so there is any way so that code know how many days to show in each month

 

 

 

thanks,

Shahzad

[Barand]

There is  a mysql function to give last day in a month

[shahzad429]

so where in this query i can write SELECT LAST_DAY(l.AccessDate)

 

and i have to remove 31 from temporary table right??

[Barand]

select d.days as Day, count(l.AccessDate) as Total
from tempdays d
left join log l
on d.days=day(l.AccessDate) and l.linkcode='dcxha' and month(l.AccessDate)=4
where d.days <= DAY(LAST_DAY('2012-04-01'))
group by d.days

[shahzad429]

Thanks bro it is working

[Barand]

Selecting month = 4 will only work while you have a single year's data in the table but will be incorrect once you have April data for two or more years.

 

The version below will make it future-proof and also has the advantage that only a single parameter is needed ie the date of the first day in the reporting month (2012-04-01 in this example)

select d.days as Day, count(l.AccessDate) as Total
from tempdays d
left join log l
on d.days=day(l.AccessDate) and l.linkcode='dcxha'
  and EXTRACT(YEAR_MONTH FROM l.AccessDate) = EXTRACT(YEAR_MONTH FROM '2012-04-01')
where d.days <= DAY(LAST_DAY('2012-04-01'))
group by d.days

[shahzad429]

nice thanks alot for this effort

 

yes later this database is going to have data for more that 2 years

 

thanks to save me ..

 

 

[shahzad429]

Barand I have added DATE_SUB(CURDATE(),INTERVAL 1 year) <= l.accessdate in my months query

so that it display months and clicks per month for last year.

 

it is displaying from 1 to 12

as it is May 2012 now so how can i display records from May 2011 to May 2012??

 

 

SELECT m.month, COUNT(l.accessdate) as total
    FROM tempmonths m 
    LEFT JOIN log l
    ON m.month = MONTH(l.accessdate) AND l.linkcode = 'dcxha'  and DATE_SUB(CURDATE(),INTERVAL 1 year) <= l.accessdate
    GROUP BY m.month

[Barand]

looks like you need to introduce a year column to the temp table  and order by m.year, m.month