...

View Full Version : sort by date



markman641
08-18-2011, 10:47 PM
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
08-18-2011, 11:24 PM
<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
08-18-2011, 11:50 PM
how does datetime work? i googled it and i couldnt find anything that worked

markman641
08-18-2011, 11:58 PM
do i just go into phpmyadmin and change date value to datetime?

markman641
08-19-2011, 12:08 AM
okay, i switched to to datetime, now it all says 0000-00-00 00:00:00 even with new entries

Old Pedant
08-19-2011, 01:02 AM
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
08-19-2011, 01:26 AM
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
08-19-2011, 02:01 AM
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
08-19-2011, 02:06 AM
thank you so much!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum