sort by date

[markman641]

i have the following code:

<?
				$get_name = mysql_query("SELECT * FROM `lastcredited` ORDER BY STR_TO_DATE(date, '%m/%d/%y') DESC");
 while($namerow = mysql_fetch_array($get_name)) {
  	$name1 = $namerow["name"];
	$countr = $namerow["country"];
	$id = $namerow["id"];
	$date = $namerow["id"];
?>
 <a href="offer<?=$id?>.php"><?=$name1?> (<?=$countr?>)</a><br><br>
           <? }?>

and the problem is when i have 2 entries with the same date, it puts the wrong one on top. It is supposed to be the new entry i put in is on top, Here is the code of the script i use to ADD the things into the database:

<?php

$con = mysql_connect("localhost","xxxxxxxx","xxxxxxxxx");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("moneyeve_money4every1", $con);

$sql="INSERT INTO lastcredited (id, name, date, country)
VALUES
('$_POST[id]','$_POST[name]','$_POST[date]','$_POST[country]')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  } 
echo "Database updated with: <a href='offer$_POST[id].php'>$_POST[name]($_POST[country])$_POST[date]
<br> <a href='addoffers.php'>add another</a>";

mysql_close($con)
?>

[Old Pedant]

<shrug>If you are only inserting the date, and not the date plus time, then MySQL has now way of distinguishing between 8/18/2011 and 8/18/2011.

If you don't have an AUTO_INCREMENT field on the table, and you only have the one date-only value, then you are stuck.

If the date field is always today's date, then the best thing to do would be to (a) stop using a text datatype for that field (use a DATETIME field) and, (b) when you want to insert a record, let MySQL choose the date+time instead of passing it in.

[markman641]

how does datetime work? i googled it and i couldnt find anything that worked

[markman641]

do i just go into phpmyadmin and change date value to datetime?

[markman641]

okay, i switched to to datetime, now it all says 0000-00-00 00:00:00 even with new entries

[Old Pedant]

Well, sure, if you are still passing in *JUST* the data from your PHP code:

$sql="INSERT INTO lastcredited (id, name, date, country)
VALUES ('$_POST[id]','$_POST[name]','$_POST[date]','$_POST[country]')"

And entries that previously were given only a date will of course have only 0:00:00 as the time.

*IF* you want the *current date and time* in that field, then change your query to:

$sql="INSERT INTO lastcredited (id, name, date, country)
VALUES ('$_POST[id]','$_POST[name]',NOW(),'$_POST[country]')"

But if you need/want the date and time to come from the <form> posting, then you will have to ask the user to enter both a date and time.

[markman641]

ok cool thanks!! now how would i sort by newest to oldest?

<?
				$get_name = mysql_query("SELECT * FROM `lastcredited` ORDER BY STR_TO_DATE(date, '%y/%m/%d') DESC");
 while($namerow = mysql_fetch_array($get_name)) {
  	$name1 = $namerow["name"];
	$countr = $namerow["country"];
	$id = $namerow["id"];
?>

[Old Pedant]

First of all, you now kill STR_TO_DATE.

SELECT * FROM lastcredited ORDER BY `date` DESC

date is a built-in function in MySQL, so you really should put the back-tick marks around that name to make sure it doesn't get confused with the function. [It shouldn't, anyway, but let's play it safe.]

[markman641]

thank you so much!