cannot get date in ascending

A

Anonymous

Guest
Hello;
have 3 dropdowns Date, City and Agency. When a value is clicked it brings up results.php pop up. City and Agency are in alphabetical ascending order. Date is not in alpha-numeric ascending. How can I get all three in ascending?
Help is greatly appreciated. :)
Here is the dropdown code, index.php:

Code: 
$data   = mysql_query("SELECT date, agency, city FROM agencies");
if (!$data) 
  Die(mysql_error());                        // display MySQL error message on error 
$agencies = Array(); 

while($row = mysql_fetch_array($data)) {     // assign results into arrays 
  $dates[]    = $row["date"]; 
  $agencies[] = $row["agency"]; 
  $cities[]   = $row["city"];
} 

$dates = Array_Unique($dates);               // remove duplicate values 
$agencies = Array_Unique($agencies); 
$cities = Array_Unique($cities); 

Sort($dates);                                // sort arrays 
Sort($agencies); 
Sort($cities); 

$date_out   = "<select name='date'   onchange=\"window.open('results.php?action=date&value='+this.value, 'agencyWin', 'location=yes,left=20,top=20');\">"; 
$agency_out = "<select name='agency' onchange=\"window.open('results.php?action=agency&value='+this.value, 'agencyWin', 'location=yes,left=20,top=20');\">"; 
$city_out   = "<select name='city'   onchange=\"window.open('results.php?action=city&value='+this.value, 'agencyWin', 'location=yes,left=20,top=20');\">"; 

$date_out   .= "<option>-- select date ---</option>";

$agency_out .= "<option>-- select agency ---</option>";

$city_out   .= "<option>-- select city ---</option>"; 

forEach ($dates as $value) 
  $date_out   .= "<option value='$value'>$value</option>"; 
forEach ($agencies as $value) 
  $agency_out .= "<option value='$value'>$value</option>"; 
forEach ($cities as $value) 
  $city_out   .= "<option value='$value'>$value</option>"; 
$date_out   .= "</select>\n";
$agency_out .= "</select>\n";
$city_out   .= "</select>\n"; 
echo $date_out.""; 
echo $agency_out.""; 
echo $city_out."<BR>";

?>
 
one quick way would be to sort your query (sql) by date asc and when it is returned, it would already be sorted.

SELECT date, agency, city FROM agencies ORDER BY date ASC;

I think you could also use a function like usort(), but that would require you to write a little compare function to use with usort.
 
I used the select statement in your post the dates are not in order. I changed them all to the format xx/xx/xxxx in phpmyadmin
For some reason the date and agency are ok, but the date is out of order. Can you give me an idea of why?
Thank you for any help
 
please use the YYY-mm-dd H:i:s format or the datetime format for the mysql field, it is very easy for mysql then to sort the date fields
 
I want the dropdowns to be easily read by senior citizens(it is for flu clinics) Should I change the phpmyadmin records to 2004-12-2?
Please clarify..
Thanks
 
I would put the date in YYYY-mm-dd H:i:s like ruturajv suggested. The date will then be stored in the database in that format, which will allow for easier sorting. When you display the date to the user, you can use a function like:
date() to display it an another format.

ie.
date('m/d/Y',$row['date']);
This would display the date result in $row['date'] as mm/dd/yyyy format.

good luck
 
thank you. I guess the code:date('m/d/Y',$row['date']);
you suggested should go after the main query in the results?
 
I was wrong in last post-it should go in index w/dropdowns...but where?
Tried a few places-I am new to this..please help if you can..

// perform query
Code: 
$data   = mysql_query("SELECT date, agency, city FROM agencies ORDER BY date, agency, city ASC; 
");
[b]date('m/d/Y',$row['date']); [/b]

if (!$data) 
  Die(mysql_error());                        // display MySQL error message on error 

$agencies = Array(); 

while($row = mysql_fetch_array($data)) {     // assign results into arrays 
  $dates[]    = $row["date"]; 
  $agencies[] = $row["agency"]; 
  $cities[]   = $row["city"];
}
 
Skeletor;
can you show me where to put the code you suggested..date('m/d/Y',$row['date']);
I have tried several times around the code:
Code: 
while($row = mysql_fetch_array($data)) {     // assign results into arrays 
  $dates[]    = $row["date"]; 
  $agencies[] = $row["agency"]; 
  $cities[]   = $row["city"];

from my previous post but it does not work..
 
After you put the order by date ASC in your sql statement and changed the format date is stored in the database, is your date now in order? If not, try running a sort on the date array like you do the others and see if it now works with date being in a different format. I think you will want to use the date() function within your foreach when producing your dropdown box.

something like the below may work.

forEach ($dates as $value)
$date_out .= "<option value='$value'>".date('m/d/Y',strtotime($value))."</option>";
 
yes, the date is in order-appreciate that. I just want it in a easier format. Can you show me, using the larger code from previous code, where I should put the ForEach?
 
You must log in or register to reply here.
Back
Top