[RESOLVED] Monthly Archiving

middleman666

Hey people,

Im looking for help with my monthly archiving.

I have a database with numerous fields, one of which being:

Date text,

The date is stored in this field, for example: Thu 1st June, 2006

at the moment, im using this query to group the dates together which are the same:

SELECT Date FROM $TableName GROUP BY Date ASC

But this isn't quite what I want.
I want to be able to define just the month and year from the Date field. so from Thu 1st June, 2006 I just want June, 2006.

Is there a way of:

reading just the month and year from the date text.
putting the months and years in an array.
then having a query to say select date from table where date is like that of one from the array and then grouping the different dates together?

hopefully that made sense...
if any one can point me in the right direction of give me some help I would be thankfull.

Thanks.

MarkR

Don't store dates as a text string "Thu 1st June, 2006". Store dates in the database as a DATETIME. You know it makes sense.

Once the database is actually treating your dates like dates rather than strings, ORDER BY and WHERE on those columns will be able to be used sensibly.

Mark

bogu

Do it like MarkR told you, but if your not looking in changing the field type u can always check by the string, but u are limited ....

middleman666 wrote:
I want to be able to define just the month and year from the Date field. so from Thu 1st June, 2006 I just want June, 2006.

$query = "SELECT `Date` FROM {$TableName} WHERE `Date` LIKE '%".date('F, Y')."%'";

middleman666

your right, ive gone about this the wrong way, I should have used DATE in the first place :S

could you explain how date actually works, i know you write DATE to the Date field which is set as a Date field, but how does it show the date in the database if its not as a string? and how do you read it out?

thanks for your help.

bogu

U know, that I dont need to show u that, u could just simply create a field date into your table and u see how it looks ...

date field 'YYYY-MM-DD'

But u have DATE_FORMAT() mysql function to format your date as u want ...

BTW click here

middleman666

sorry for being a noob but im getting lost...

I now have a field in my form that echos the date into it like 06-02-2006.
When the forms submitted, that field is put into the Date field in my database (which is now set to DATE).

Problem is.. my database is reading the 06-02-2006 as 2006-02-20 which is wrong.

Why is it doing that?

im formatting the date like this:

<?php echo date('m-d-Y'); ?>

is that not right?

middleman666

sorry that was idiotic...
sorted that problem...

Im now having problem with date_format()

How do I use it to read out the date in a different format.

ive tried:

"SELECT date_format(Date, '%d-%m-%Y') FROM $TableName"

but no such luck...

can anyone say where im going wrong?

EDIT: sorted, used: echo date("d /M /Y", strtotime($row[Date]));

now ive just to work out how to group months and years together to make a monthly archive. loadsa fun.

EDIT:

SELECT * FROM $TableName WHERE Date LIKE '".date('m Y')."' GROUP BY Date ASC

how do you write that properly..?

bogu

DATE_FORMAT(date,format)

SELECT DATE_FORMAT(`Date`,"%b %Y") as monthly_date
FROM $TableName  

GROUP BY monthly_date
ORDER BY `Date` ASC

To extarct all the months from your table ...

middleman666

that only seems to be extracting the entries made this month.

heres my code at the mo:

// Build SQL Query  

$query = "SELECT DATE_FORMAT(Date,'%b %Y') as monthly_date FROM $TableName GROUP BY monthly_date ASC";

 $result = mysql_query($query) or die("Couldn't execute query");

// now display the results returned
while ($row= mysql_fetch_array($result)) {

  echo '<a href="index.php?menu=showhistory&id='.$row['id'].'">'.date("d /m /Y", strtotime($row[Date])).'</a><br>';

 }

any idea where its going wrong?

bogu

Have no idea, I tested and it extracts all the months ...

bogu

date("d /m /Y", strtotime($row[Date])) <-- u dont need to show the day because u extract only the months and the date is already formated from the mysql just echo $row['Date'] ...

And what $row['id'] do u use, there u dont extract the id, u need to pass the month and the year on GET to extract the result from that month, think man, think ...

middleman666

date("d /m /Y", strtotime($row[Date])) is wrong.. that just displays todays date.

I changed that to just $row['Date'] then it displays nothing.

I changed SELECT to SELECT *, DATE_FORMAT(... etc.

Now I have, a list of dates, grouped by month.

I need to know how to format the date when reading it out because at the moment its just like 2006-05-29.

$row[id] isnt important at the mo, in fact, the id will have to actually be the month instead of entry id.

could you show me how to format a date outside of, and reading from the database?

bogu

U dont echo row['Date'], u need to echo row['monthly_date'] that is the date formated ...

middleman666

ah ha... I didn't realise monthly_date was just a name... I thought it was actually doing something.

Thanks for your help and for putting up with my lack of learning skill 🙂

bogu

No problem, u have time to learn if your willing too...

When u encounter problems phpbuilder is here ... 😃