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!
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"?
<?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
?>
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
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.
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..
<?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/>';
?>
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.
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...
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.
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.