Organized record date listing.....

Anon

Ok, lets say I have a list of records that go back as far as a year. I set up a script that allows me to view these records, but instead of viewing all of them at once I'd rather have an organized list according to date.

Here is a sample of what I'm talking about :

Year 2002 <-first organize by year
January <-then by month
February
March
November
Year 2001 <-loop to the next year and start again and so on.
July

This information is stored in a MySQL database. The values of the date fields are timestamps using PHP's time(); function at record generation.

I'm no new to PHP/MySQL, but I am new to using this method of organized listings, I've racked my brains out over this. Perhaps you more advanced life forms could help me out.

van__

and "ORDER BY dateColumn" doesn't work?

Anon

Oh, sorry, what I'm trying to do is to 'dynamically' create a list only using the time stamps in the records. If there are 50 records created in March in the year 1999 then it should look like this:

Year 1999 <-dynamically generated
March <-any records that lie within an individual month are represented by the single month name.

See, what I'd like to do is generate this list and then have the month link the user to the actual list of records.

Kinda like what you see on some news scripts/bloggers that allow people to see archived records.

van__

hmmm...
well better querying is the way to go here.
I say, just grab only those for the month and the year selected.

Here the user provides the year and the month...

$stmt = "SELECT * FROM myCalendar WHERE YEAR(dateColumn) = '$year' and MONTH(dateColumn) = $month ORDER BY dateColumn";

That gives you one full month... set it as a session var:

for($i=0;$i<mysql_num_rows($result);$i++) {
$GLOBALS['month_subj'][$i] = mysql_fetch_array($result, MYSQL_ASSOC);
}

@session_register('month_subj');
print_r($GLOBALS['month_subj']);
...now that can pass that month between pages and only reset it when they choose a new month to view.

van__

I should add that if you're wanting to grab the ENTIRE damn calendar and have it be sorted and live...

You'll have to code for that.
But, a query can speed it up...
$stmt = "SELECT ..., ..., YEAR(dateColumn) as YEAR, MONTH(dateColumn) as MONTH FROM myCalendar WHERE YEAR(dateColumn) = '$year' and MONTH(dateColumn) = $month ORDER BY dateColumn";

the you loop for output...
for($i=0;$i<mysql_num_rows($result);$i++) {
$rows[$i] = mysql_fetch_array($result, MYSQL_ASSOC);
}

and now you can sort your array directly...
foreach ($rows as $key=>$val) {
$sorted[$rows[$key]['YEAR']][$rows[$key]['month']][] = $rows[$key];
}

print_r($sorted);

Anon

Here is part of the query I was working on. It's 'supposed' to create a simple list of dates in a year format where each of the records were created. It looks like it 'should' work, but I fail to see what I'm doing wrong here:

$yearStop = "2002";

for ($loop_year=date("Y"); $loop_year>=$yearStop; $loop_year--) {
$sql="SELECT ID FROM cyber_news WHERE YEAR(news_date)='$arc_year' LIMIT 1";
$querycounter++;
$sqlResult=mysql_query($sql);
if (mysql_num_rows($sqlResult)) {
echo "<b>$arc_year</b><br>";
}
}

van__

Had some probs with tht last post...

here's the edit with your new info in there :
<?
$stmt = "SELECT ID, YEAR(news_date) as YEAR, MONTH(news_date) as MONTH FROM cyber_news;

//then you loop for output...
for($i=0;$i<mysql_num_rows($result);$i++) {
$rows[$i] = mysql_fetch_array($result, MYSQL_ASSOC);
}

//sort your array directly...
foreach ($rows as $key=>$val) {
$sorted[$rows[$key]['YEAR']][$rows[$key]['MONTH']][] = $rows[$key];
}

print_r($sorted);
?>

That should give you a hash table you can call as:
$sorted[2002][1] //will give you an array for Jan 2002

van__

as for this...

I don't see a setting of $arc_year...

}

Anon

Excellent! I really appreciate all your help.

Anon

I took the code from my project and changed it around so it could be read a bitter easier. Looks like I forgot to rename some variables. bl00ps. 😉