Question about 'ordering by date'

[adamjones]

Hi.

I have a news oage with the following code;

 

	<?php
require_once('config.php');
$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
if(!$link) {
	die('Failed to connect to server: ' . mysql_error());
}

$db = mysql_select_db(DB_DATABASE);
if(!$db) {
	die("Unable to select database");
}

$DB_Query = @mysql_query("SELECT * FROM `uknews` ORDER BY date DESC LIMIT 5") OR die('MySQL error: '.mysql_error()); 

while ($data = mysql_fetch_array($DB_Query)) {
$data['Message'] = nl2br($data['Message']);
echo "
		  <div class='start'>{$data['Title']}</div>
    <div id='latest-news' class='box'>
<p>{$data['Message']}</p>
<p><b>Posted by {$data['Name']}</b></p>
</div>
	  <div class='end'></div>";
}

?>

This quiries the database, and gets my news items, then organizes them by their date submitted. My problem, however, is that, for example, there are two news articles submitted on the same date, then it no longer organizes them on that date.

 

...so I would need to also add a time function, to check the time submitted too, correct?

 

How would i go around doing this? This is table layout;

`ID` int(11) NOT NULL auto_increment,
  `Title` text NOT NULL,
  `Date` date NOT NULL default '0000-00-00',
  `Message` text NOT NULL,
  `Name` text NOT NULL,

Cheers,

Adam.[/code]

[allworknoplay]

 

 

...so I would need to also add a time function, to check the time submitted too, correct?

 

 

 

that sounds about right. When I do my dates in mysql, I just make the columns INT(10) and put in

the timestamp using PHP's time() function.

 

Then when I pull the data out, I use the date() function to properly display the date.

 

By using timestamp, you cover everything, the date as well as time...

 

[adamjones]

 

 

...so I would need to also add a time function, to check the time submitted too, correct?

 

 

 

that sounds about right. When I do my dates in mysql, I just make the columns INT(10) and put in

the timestamp using PHP's time() function.

 

Then when I pull the data out, I use the date() function to properly display the date.

 

By using timestamp, you cover everything, the date as well as time...

 

 

Ok, so I've added a time field to the database, but I'm not really sure how I would quiery the database to do it for the date and time?

 

Would it be;

 

$DB_Query = @mysql_query("SELECT * FROM `uknews` ORDER BY date AND time DESC LIMIT 5") OR die('MySQL error: '.mysql_error()); 

 

No, that has to be wrong, lol!

[allworknoplay]

 

 

...so I would need to also add a time function, to check the time submitted too, correct?

 

 

 

that sounds about right. When I do my dates in mysql, I just make the columns INT(10) and put in

the timestamp using PHP's time() function.

 

Then when I pull the data out, I use the date() function to properly display the date.

 

By using timestamp, you cover everything, the date as well as time...

 

 

Ok, so I've added a time field to the database, but I'm not really sure how I would quiery the database to do it for the date and time?

 

Would it be;

 

$DB_Query = @mysql_query("SELECT * FROM `uknews` ORDER BY date AND time DESC LIMIT 5") OR die('MySQL error: '.mysql_error()); 

 

No, that has to be wrong, lol!

 

No problem, can you show me your table structure?

 

 

[adamjones]

Sure;

 

 `ID` int(11) NOT NULL auto_increment,
 `Title` text NOT NULL,
 `Date` date NOT NULL default '0000-00-00',
 `Time` time NOT NULL default '00:00:00',
 `Message` text NOT NULL,
 `Name` text NOT NULL,
 PRIMARY KEY  (`ID`)

 

Also, how would I have the date as 'Thu-19-2009', instead of it coming up as '2009-02-19', if you know?

 

Cheers.

[allworknoplay]

 

Sure;

 

  `ID` int(11) NOT NULL auto_increment,
  `Title` text NOT NULL,
  `Date` date NOT NULL default '0000-00-00',
  `Time` time NOT NULL default '00:00:00',
  `Message` text NOT NULL,
  `Name` text NOT NULL,
  PRIMARY KEY  (`ID`)
[code]

Also, how would I have the date as 'Thu-19-2009', instead of it coming up as '2009-02-19', if you know?

Cheers.

 

You can do it that way but I'm unfamiliar with using date as the column type in MYSQL. that's why i said I use int(10) and then put int he date as a unix timestamp value.

 

As for displaying dates, that's easy:

 

'Thu-19-2009' = date("D-d-Y")

 

So the correct format is:

 

$mydate = date("D-d-Y",$timestamp_from_db);

 

 

 

[sasa]

ORDER BY date DESC, time DESC

[adamjones]

 

Sure;

 

  `ID` int(11) NOT NULL auto_increment,
  `Title` text NOT NULL,
  `Date` date NOT NULL default '0000-00-00',
  `Time` time NOT NULL default '00:00:00',
  `Message` text NOT NULL,
  `Name` text NOT NULL,
  PRIMARY KEY  (`ID`)
[code]

Also, how would I have the date as 'Thu-19-2009', instead of it coming up as '2009-02-19', if you know?

Cheers.

 

You can do it that way but I'm unfamiliar with using date as the column type in MYSQL. that's why i said I use int(10) and then put int he date as a unix timestamp value.

 

As for displaying dates, that's easy:

 

'Thu-19-2009' = date("D-d-Y")

 

So the correct format is:

 

$mydate = date("D-d-Y",$timestamp_from_db);

 

 

 

Hmm, ok.

When I tried changing the date to int(10), and timestamp, I get this error;

 

"SQL query:

 

ALTER TABLE  `uknews` CHANGE  `Date`  `Date` INT( 10 ) ON UPDATE CURRENT_TIMESTAMP NOT NULL

 

MySQL said:

 

#1294 - Invalid ON UPDATE clause for 'Date' column "