count queries

[chriscloyd]

say you have a database for a blog and you have posted on it 80 times in a three month span

so example of database set up

cols - header blog timeposted month

 

I want to do a archive type of thing where it searches the data bases for each month example below

March

April

May

 

then it list how many post are from each of those months

March (30)

April (29)

May (21)

 

any help please?

 

and kinda want to add year to so if it goes on for three years so it has years next to the months

[chriscloyd]

I have the blog system already set up just workin on the archive and asking advice you dont have to do it for me just lead me in the right direction

[redarrow]

You need to add the date to the database i recommend a unix time stamp,

 

Then select the time stamp and count the entry month by month.

 

[chriscloyd]

thats why i have month in there arg nevermind u dont understand lol

[redarrow]

what about this then each time a user inserts a blog you add a 1 to a month table..

 

 

 

read this mate was for fun theo.....

<?php

//database field

//month_hits

//month_1 month_2 month_3 month_4 month_5 month_6 month_7 month_8 month_9 month_10 //month_11 month_12

switch($month_hits){

case "1": 

$month_1="month_1";

$hits=$month_1;

break;

case "2": 

$month_2="month_2";

$hits=$month_2;

break;

case "3": 

$month_3="month_3";

$hits=$month_3;

break;

case "4": 

$month_4="month_4";

$hits=$month_4;

break;

case "5": 

$month_5="month_5";

$hits=$month_5;

break;

case "6": 

$month_6="month_6";

$hits=$month_6;

break;

case "7": 

$month_7="month_7";

$hits=$month_7;

break;

case "8": 

$month_8="month_8";

$hits=$month_8;

break;

case "9": 

$month_9="month_9";

$hits=$month_9;

break;

    case "10": 

$month_10="month_10";

$hits=$month_10;

break;

case "11": 

$month_11="month_11";

$hits=$month_11;

break;

case "12": 

$month_12="month_12";

$hits=$month_12;

break;	

}

$hits=$_POST['hits'];

$sql="UPDATE what_ever SET month_hits=$hits+1 WHERE month_hits='$hits'";

$res=mysql_query($sql)or die(mysql_error());

?>



<?php 

// form info

echo "<input type='text' name='month_hits' value='".date('m')."'>"; ?>

 

[chriscloyd]

you gave me an idea

well urs but ya create another table example archive

with cols such aid bid month year

then when i inster a blog it insterts a new one

 

[redarrow]

SELECT [NameField], Month([DateField]), Format([DateField], "mmm"),

Count([iD]) as CountOfID

FROM yourTable

GROUP BY [NameField], Month([DateField]), Format([DateField], "mmm")

ORDER BY [NameField], Month([DateField])

 

This would give you a list of all of the Names, the appropriate months (but

would not give you the months where there were no records for a particular

name), the three letter month, and the count of the # of records for that

name, in that month (regardless of year). Another way to do this, which

would result in a single row for each name, and columns for each of the

months (like a spreadsheet) would be to use a crosstab query. If you create

the crosstab query, drag the [NameField], [DateField], and [iD] values into

the query grid. In the crosstab row, select "Row Heading" for the

[NameField], "Column Heading" for the formatted date field, and "Value" for

the [iD] field (also indicate that this is a Count, not a GroupBy, in the

Totals row of the grid. When you are done with that , run the query. You

should get what you are looking for, but the columns will be sorted

alphabetically. To get it in the right order, go into the SQL view and add

the final IN clause from the row below that starts with PIVOT. The way you

actually do this in your query is to right click in the area above the query

grid, select properties, and then add the column headers. These must match

exactly what show up in the query or you will end up with columns that are

NULL.

 

TRANSFORM Count(tbl_NameDates.ID) AS CountOfID

SELECT tbl_NameDates.NameField

FROM tbl_NameDates

GROUP BY tbl_NameDates.NameField

PIVOT Format([DateField],"mmm") In

("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

 

[redarrow]

your getting there mate......