Changing value

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

Changing value

Post 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
?>
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post by dibyendrah »

In which format are you storing the date ?
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

Post 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?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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..
User avatar
Hurreman
Forum Commoner
Posts: 61
Joined: Sat Apr 29, 2006 8:42 am

Post 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
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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.
Last edited by Christopher on Fri May 05, 2006 2:08 pm, edited 1 time in total.
(#10850)
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

Post 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!
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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...
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

Post 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.
User avatar
Hurreman
Forum Commoner
Posts: 61
Joined: Sat Apr 29, 2006 8:42 am

Post 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.
Post Reply