help sorting output by date

[kirks]

Background: I have a script that I had someone else to create for me that is not quite working right. I no longer have access to the person who created the script and it’s not quite outputting the information correctly. The database contains events that consist of one or more days.

The script outputs records grouped by year then by month then orders records by the day of the month (first day of the event if the event spans more than one day).

The script is grouping the records by year and month okay but not by date. I am a graphics person and only have a basic understanding of PHP. I’ve tried various ways of formatting the output of the beginning date (details_date) in the order by statement but the results are never what I want.

Here’s some of the database field structure:
details_id int(11) NOT NULL auto_increment,
details_end_date date default NULL,
details_date date NOT NULL default ‘0000-00-00’,

Here’s the script:
[php]

<?php include("globals.php"); include("opendb.php"); $currentDate=date("Y-m-d"); $query="SELECT distinct(DATE_FORMAT( `details_date` , '%Y' )) AS years from itn_details WHERE details_date >='$currentDate' group by details_date" ; $Res=mysql_query($query, $connection); while($row=mysql_fetch_assoc($Res)) { echo ("

" . $row['years'] . " Itinerary

"); $sql1="SELECT Distinct(DATE_FORMAT( `details_date` , '%M' )) AS Month from itn_details WHERE details_date >='$currentDate' and DATE_FORMAT( `details_date` , '%Y' )='".$row['years']."' group by Month order by details_date"; $Res1=mysql_query($sql1, $connection); while($row1=mysql_fetch_assoc($Res1)) { echo ("

" . $row1['Month'] . "

"); $sql2="SELECT *, DATE_FORMAT(`details_date`, '%M %d') AS start_date, DATE_FORMAT(`details_end_date`, '%d') AS end_date, DATE_FORMAT(`details_date`, '%Y') AS start_year FROM itn_details WHERE ((details_end_date IS NULL AND details_date >='$currentDate') OR (details_end_date >= '$currentDate')) and DATE_FORMAT( `details_date` , '%Y' )='".$row['years']."' and DATE_FORMAT( `details_date` , '%M' )='".$row1['Month']."'"; $Res2=mysql_query($sql2, $connection); while($getRecord=mysql_fetch_assoc($Res2)) { $getRecord['details_date']; $breakDate=explode("-", $getRecord['details_date']); echo ("

" . $getRecord['start_date']); if($getRecord['end_date'] > "00") { echo (" - " . $getRecord['end_date']);}; echo (", " . $getRecord['start_year'] . "
"); echo (" " . $getRecord['details_church']); echo "
"; echo (" " . $getRecord['details_city_state']); echo "
"; if($getRecord['details_pastor'] > " ") { echo (" " . $getRecord['details_pastor']); echo "
";}; if($getRecord['details_phone'] > " ") { echo (" " . $getRecord['details_phone']); echo "
";}; if($getRecord['details_link'] > " ") { echo (" " . $getRecord['details_link']); echo "";}; echo ("

 

"); } } } include("closedb.php"); ?>

[/php]

Here’s the results: http://www.sansom.net/scripts/itinerary.php

As you can see, some events are appearing at the end of the group for that month when they should be at the beginning. It’s obvious I’m missing something simple or just too ignorant to understand what I’m doing wrong but appreciate any help on this subject.

[Analog]

Try ordering the events by details_date:

[php]$sql2=“SELECT *,
DATE_FORMAT(details_date, ‘%M %d’) AS start_date,
DATE_FORMAT(details_end_date, ‘%d’) AS end_date,
DATE_FORMAT(details_date, ‘%Y’) AS start_year
FROM itn_details
WHERE ((details_end_date IS NULL AND details_date >=’$currentDate’) OR (details_end_date >= ‘$currentDate’))
and DATE_FORMAT( details_date , ‘%Y’ )=’”.$row[‘years’]."’
and DATE_FORMAT( details_date , ‘%M’ )=’".$row1[‘Month’]."’
ORDER BY details_date";[/php]

The code looks like it’s doing a lot of queries to get the same data from the same table. Is there any reason you couldn’t just select all the events in one go?

[kirks]

Ah-h-h yeah! Of course! I figured it was something simple! I’m just not a programmer so it’s a stretch for me.

As for the redundancy, I wouldn’t have a clue how to reduce the query’s to get the same results!

Thanks!