Page 1 of 1

Changing value

Posted: Thu May 04, 2006 1:09 pm
by psurrena
I want to sort a list by date. My table has a month and day field but the month is not numerical. How would I go about saying "October = 10, November=11"?

Here is a small piece of the code I'm writing:

Code: Select all

<?php
		* connection				
						$date_m = $_GET['s_date_m'];			
						$date_d = $_GET['s_date_d'];
						
						$date 	= $date_m . $date_d;
						
						$query = "SELECT * FROM workshop ORDER BY '$date'";

               * loop and more things
?>

Posted: Thu May 04, 2006 3:22 pm
by timvw
My first advise would be to reconsider your database design...


Anyway, if you're using mysql you can look up the ORDER BY (with FIELD parameter) in the manual

Code: Select all

SELECT *
FROM table
ORDER BY FIELD(monthcolumn, ‘January’, ‘February’, ‘March’, ...)
Or a more standard SQL solution:

Code: Select all

SELECT *
FROM fruits
WHERE color = ‘red’ OR color = ‘blue’ OR color = ‘orange’
ORDER BY case
  when color = ‘red’ then 0
  when color = ‘blue’ then 1
  when color = ‘orange’ then 2
end

Posted: Fri May 05, 2006 3:50 am
by dibyendrah
In which format are you storing the date ?

Posted: Fri May 05, 2006 12:45 pm
by psurrena
I ended up changing the db as suggested. Now the date is in one cell and is formated like: 05062006
This allows me to sort by date and display the written month elsewhere.

I'm using substr to pull out the info:

Code: Select all

$sdatem = substr($s_date, 0,-6);
																								
if ($sdatem == 01) {
	$month = "January";
} elseif ($sdatem == 02) {
	$month = "February";
} elseif ($sdatem == 03) {
	$month = "March";
} elseif ($sdatem == 04) {
	$month = "April";
} elseif ($sdatem == 05) {
	$month = "May";
} elseif ($sdatem == 06) {
	$month = "June";	
} elseif ($sdatem == 07) {
	$month = "July";
} elseif ($sdatem == 08) {
	$month = "August";
} elseif ($sdatem == 09) {
	$month = "September";
} elseif ($sdatem == 10) {
	$month = "October";
} elseif ($sdatem == 11) {
	$month = "November";
} elseif ($sdatem == 12) {
	$month = "December";
}

$sdated = substr($s_date, 2, -4);												
$year = substr($s_date, 4);

$date  = $month . " " . $sdated;
Is this a rather efficient method? Or does anyone have a suggestion?

Posted: Fri May 05, 2006 1:38 pm
by timvw
What's wrong with storing the date as a DATETIME (or DATE/TIME) type? Or storing it as a an INT (representing a unix timestamp)?
This way you can use the Date and Time functions most SQL DBMS's have..

Posted: Fri May 05, 2006 1:47 pm
by Hurreman
By using strtotime() and date(), you can skip the long nested if's, as long as you store the date so that strtotime() can parse it.

Code:

Code: Select all

<?php
$str = "2005-12-31";
$timestamp = strtotime($str);
echo date('F d',$timestamp);
?>
Output:

Code: Select all

December 31

edit:

If you want month/day names and such in different languages, you can modify the above into:

Code: Select all

<?php
$str = "2005-12-31";
$timestamp = strtotime($str);
// Set language to swedish
setlocale(LC_ALL,"sve");
echo strftime('%B %d',$timestamp) . '<br/>';
// Set language to german
setlocale(LC_ALL,"deu");
echo strftime('%B %d',$timestamp) . '<br/>';
// Set language to finnish
setlocale(LC_ALL,"fin");
echo strftime('%B %d',$timestamp) . '<br/>';
?>
output:

Code: Select all

december 31
Dezember 31
joulukuu 31

Posted: Fri May 05, 2006 2:07 pm
by Christopher
psurrena wrote:I ended up changing the db as suggested. Now the date is in one cell and is formated like: 05062006
I would second storing the date in a date field in the database, then you can ORDER BY it and use an SQL formating function to format the output to be anything you want. If you must store it as a string like you showed, at least store it 'yyyymmdd' so you can sort on it.

Posted: Fri May 05, 2006 2:07 pm
by psurrena
Great! I changed the date format of "s_date" to 01-02-2006 and this code worked perfect:

Code: Select all

<?php																				
	include 'library/config.php'; 
	include 'library/opendb.php';		
												
	$query = "SELECT * FROM workshop";
	$result  = mysql_query($query) or die('Error : ' . mysql_error());  						

	while($row = mysql_fetch_array($result)) { 

		$s_date    = $row['s_date'];			
		$timestamp = strtotime($s_date);
		
		echo date('F d', $timestamp) . "<br />";
	}
	include 'library/closedb.php'; 
?>
Thanks to everyone!

Posted: Fri May 05, 2006 2:29 pm
by timvw
psurrena wrote:Great! I changed the date format of "s_date" to 01-02-2006 and this code worked perfect..
I hope you meant that you've changed the date TYPE to DATE... Using a (var)char/string for a date would still be a 'custom' approach trying to reinvent the wheel...

With the following query you can leave all the 'formatting' work to mysql...

Code: Select all

SELECT DATE_FORMAT(s_date, '%F %d') AS nicedate FROM workshop
Date and Time functions...

Posted: Fri May 05, 2006 2:31 pm
by psurrena
Yes, I just changed the field from varchar to date. I see it was a bit foolish from the start but this thread show an interesting progression. From doing something the wrong way - to patching it - to fixing in properly.

Posted: Fri May 05, 2006 3:30 pm
by Hurreman
timvw wrote: With the following query you can leave all the 'formatting' work to mysql...

Code: Select all

SELECT DATE_FORMAT(s_date, '%F %d') AS nicedate FROM workshop
Nice, seems I have to take some time reading through the mysql docs a bit more careful. Having MySQL pre-process data like could sure come in handy. Cheers timvw.