Sorting by Date in MySQL

[imarkdesigns]

good day every one...

im kinda confused about this stuff... on how to sort it by in order of Month and later on the Date...

here's the code:

[PHP]$sql="SELECT * FROM $sched ORDER BY date;";[/PHP]

infact it will sort it in alphabetical arrangement... my point is, how the hell can sort this kind of Date "ei: May 5, 2007 up to December 30, 2007" in my coding where the first month descending to last month?

for the result, here's the link: Schedule

anyone can help me about this stuff?

[pbmods]

Heya, imarkdesigns.

I'm surprised that MySQL doesn't complain about that query since 'date' is a reserved word (you can use it as a column name, but you must generally enclose it in backticks, e.g., `date`).

What type is your `date` column?

[imarkdesigns]

Heya, imarkdesigns.

I'm surprised that MySQL doesn't complain about that query since 'date' is a reserved word (you can use it as a column name, but you must generally enclose it in backticks, e.g., `date`).

What type is your `date` column?

um, sorry.. i don't get what you mean...

anyway, i got again the answer for this...
if you want [for all users] to arrange your date format like

January 1, 2007
February 1, 2007

down to

December 31, 2007

..and again

use this code:

[PHP]$sql="SELECT * FROM $sched ORDER BY MONTHNAME('0000-00-00') ;";[/PHP]

and it will arranged your date sorting to Month Category Format...

well, hope that this code will help anyone who need to Categorize their Dates Months etc...

[pbmods]

Interesting.

Well, glad to hear you got it working! Good luck with your project, and if you ever need anything, post back anytime :)

[imarkdesigns]

im back again for this thing.... "duh!"

in my experience here, i wanted to arrange the schedule page format by this...



and when i use this code:
[php]$sql="SELECT * FROM $sched ORDER BY MONTHNAME('0000-00-00);";[/php]this would be the output image/source



[Sorted order by MONTHNAME('0000-00-00);] and when the owner update his schedule, u will noticed the red line [down below] the inputed schedule and the sort order was still arranged and which i don't want to happen.

now... when i use this code:
[php]$sql="SELECT * FROM $sched ORDER BY `date` DESC";[/php]this would be the output image/source



[Sorted order by `date` DESC] and when the owner update his schedule, u will noticed that the red line is arranged by the month but not the date [look the arrow position of the dates] and also which i don't want to happen again...

well, in my coding is there any way and possibilities that i can mixed that MONTHNAME and date DESC to fixed the problem???

hope someone gotmy idea here...

here's my codes from phpMyadmin and PHP

phpMyadmin ---//
[code=mysql]
CREATE TABLE `schedule` (
`id` int(9) NOT NULL auto_increment,
`date` varchar(65) NOT NULL default '',
`time` varchar(65) NOT NULL default '',
`events` varchar(65) NOT NULL default '',
`venue` varchar(65) NOT NULL default '',
`locations` varchar(65) NOT NULL default '',
`name` varchar(200) NOT NULL,
`size` INT NOT NULL,
`type` VARCHAR(30) NOT NULL,
`path` VARCHAR(60) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT= 1 ;
[/code]
PHP --//

[PHP]
<?php

include ("dbconnecti on/connect.php");
include ("dbconnecti on/database.php");

$sql="SELECT * FROM $sched ORDER BY date ";

while($rows=mys ql_fetch_array( $result)){

?>
[/PHP]
[PHP]
<tr>
<td class="rowdate" > <? echo $rows['date']; ?> </td>
<td class="rowtime" > <? echo $rows['time']; ?> </td>
<td class="rowevent s"> <? echo $rows['events']; ?> </td>
<td class="rowvenue "> <? echo $rows['venue']; ?> </td>
<td class="rowlocat ions"> <? echo $rows['locations']; ?> </td>
<td class="rowsaved isk"> <? include ("admin/scripts/verifile.php"); ?> </a> </td>
</tr>
[/PHP]
[PHP]
<?php
} mysql_close();
?>
[/PHP]

Note: i just want to happen to categorized my Date arrangement to
January
February
March
April
... and so on, but not to sort the date numbers like

July 29,
July 28,
July 22,
July 20,
July 14.... and i just want to happen even if the Months are sorted correctly but ofcourse the date numbers will do sorted base to the arrangement of the startard date.. [look at the image # 3] and when you input another date, it will automatically sorted to dedicated position not below of the set date like in image # 2...

please, can someone correct this? any help would be a big thing for me...

[pbmods]

Heya, imarkdesigns.

How much work would it be to change your `Date` column from a VARCHAR into a DATE type?

P.S., Ahhhhh, getting programming help at 3 AM on a Saturday. Isn't it great having no life?

[imarkdesigns]

hehe... sorry about that...

anyway, i tried to change it in DATE instead of VARCHAR...

but in the end, it shows the date in format like 0000-00-00 or 2007-07-28

um, is there any way to recode this in July 28, 2007 ??

bytheway, here's my test code... but not working...

[PHP]
$sql="SELECT * FROM $sched ORDER BY date ";

while($rows = mysql_fetch_ass oc($result)) {

$date_string = $line['date'];
$date_formatted = date('F jS h\:i A',strtotime($d ate_string));

[/PHP]

[gregerly]

Check out this link. I think it's what your looking for. Some simple sql is the answer:

MySQL DATE_FORMAT()

basically it would look like:

[PHP]$sql="SELECT DATE_FORMAT(som eTable.date,'%W %M %Y') AS mydate FROM someTable;[/PHP]

Just look up the proper codes for the portion after the comma in the DATE_FORMAT() function to get the formatting you want.

Good luck!

[imarkdesigns]

Check out this link. I think it's what your looking for. Some simple sql is the answer:

MySQL DATE_FORMAT()

basically it would look like:

[PHP]$sql="SELECT DATE_FORMAT(som eTable.date,'%W %M %Y') AS mydate FROM someTable;[/PHP]

Just look up the proper codes for the portion after the comma in the DATE_FORMAT() function to get the formatting you want.

Good luck!

um, just confirming my code if it is correct, pls reply to this...

[PHP]
$sql = "SELECT *, DATE_FORMAT(sch ed_date.date, '%M %e %Y') AS mydate FROM `shcedule` ORDER BY sched_date DESC";
[/PHP]

um, coz when i do this it shows some error on my codes...

[kovik]

um, just confirming my code if it is correct, pls reply to this...

[PHP]
$sql = "SELECT *, DATE_FORMAT(sch ed_date.date, '%M %e %Y') AS mydate FROM `shcedule` ORDER BY sched_date DESC";
[/PHP]

um, coz when i do this it shows some error on my codes...

Well, firstly, you misspelled "schedule." Secondly, I would advise against using MySQL's DATE_FORMAT() function. You don't want to give any unnecessary load to the database, as PHP will run faster.

What did you mean by "here's my test code... but not working...?" What about it wasn't working? And why did you escape the colon in the code? That's unnecessary.

I'm surprised that MySQL doesn't complain about that query since 'date' is a reserved word (you can use it as a column name, but you must generally enclose it in backticks, e.g., `date`).

I believe that "date" isn't considered a reserved word in the WHERE clause as it couldn't be used for anything other than a table / column name.

[imarkdesigns]

in my posted recode, i tries all that kinds of codes, but in the end im still not satisfied with the result... however, if you'll take a look at my code above.. you will see what i need to do or what am i supposed to mean....

either this: 0000-00-00 or 2007-07-28 are not my date format to post in the schedule... this is what i need: July 28, 2007... now, when i filled the months of July it will sort by date from 28, 27, 26, down to 1 instead of 1, 2, 3 down to 30 and so on...

[kovik]

I think you are misunderstandin g.

In your database, ALL of your dates have to follow a strict YYYY-MM-DD format, or they will not be ordered correctly.

Then, in PHP, you can display it however you want using the date() function and the strtotime() function.

[imarkdesigns]

I think you are misunderstandin g.

In your database, ALL of your dates have to follow a strict YYYY-MM-DD format, or they will not be ordered correctly.

Then, in PHP, you can display it however you want using the date() function and the strtotime() function.

yeah buddy, i guess i misunderstood the processing....
hmmm... anyway, i will try again...

;)

[pbmods]

Heya, imarkdesigns.

Ok. Let's try this to convert your fields to dates:

First, we'll write a quick program to save all your dates to a file:
[code=php]
<?php
// Connect to the database
mysql_connect(. ..);
mysql_select_db (...);

$result = mysql_query('
SELECT
`id`,
`date`,
`time`
FROM
`schedule`');

$values = '';

while($row = mysql_fetch_ass oc($result))
{
$values .= $row['id'] . '|' . date('Y-m-d H:i:s', strtotime($row['date'] . ' ' . $row['time'])) . "\n";
}

file_put_conten ts('datetime.tx t', $values);
?>
[/code]

This will load your dates and times from the database and save them to a file.

Next, alter your table:

ALTER TABLE
		`schedule`
	DROP
		`time`,
	CHANGE
		`date`
			`datetime`
				TIMESTAMP
				NOT NULL
				DEFAULT CURRENT_TIMESTAMP,
	ADD
		KEY
			`datetime`
				(`datetime`);

Moderator note: Code language intentionally dropped to fix capitalization issues with MySQL language parser.

This will remove the `time` field and replace it with a timestamp that you can use to store both your date and time.

Next, we need to re-import your datetime info. So we need to write another quick program:
[code=php]
<?php
// Connect to the database
mysql_connect(. ..);
mysql_select_db (...);

// Load data
$values = file('datetime. txt');

foreach($values as $line)
{
$split = explode('|', $line, 1);
mysql_query("
UPDATE
`schedule`
SET
`datetime` = '{$line[1]}'
WHERE
`id` = '{$line[0]}'
LIMIT 1;");
}
?>
[/code]

This will run back through your saved values and re-import them.

Once you have verified that your changes have been saved, you can delete the datetime.txt file.

[imarkdesigns]

whoa...

thanks buddy for the source! it's a big help!

thanks again!

You ROCK!