Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9

Thread: sort by date

  1. #1
    Regular Coder
    Join Date
    Jul 2011
    Posts
    272
    Thanks
    63
    Thanked 1 Time in 1 Post

    sort by date

    i have the following code:

    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:

    Code:
    <?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)
    ?>

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    <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.

  • Users who have thanked Old Pedant for this post:

    markman641 (08-19-2011)

  • #3
    Regular Coder
    Join Date
    Jul 2011
    Posts
    272
    Thanks
    63
    Thanked 1 Time in 1 Post
    how does datetime work? i googled it and i couldnt find anything that worked

  • #4
    Regular Coder
    Join Date
    Jul 2011
    Posts
    272
    Thanks
    63
    Thanked 1 Time in 1 Post
    do i just go into phpmyadmin and change date value to datetime?
    Last edited by markman641; 08-19-2011 at 12:02 AM.

  • #5
    Regular Coder
    Join Date
    Jul 2011
    Posts
    272
    Thanks
    63
    Thanked 1 Time in 1 Post
    okay, i switched to to datetime, now it all says 0000-00-00 00:00:00 even with new entries

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    Well, sure, if you are still passing in *JUST* the data from your PHP code:
    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:
    Code:
    $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.

  • Users who have thanked Old Pedant for this post:

    markman641 (08-19-2011)

  • #7
    Regular Coder
    Join Date
    Jul 2011
    Posts
    272
    Thanks
    63
    Thanked 1 Time in 1 Post
    ok cool thanks!! now how would i sort by newest to oldest?

    Code:
    <?
    				$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"];
    ?>
    Last edited by markman641; 08-19-2011 at 01:43 AM.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    First of all, you now kill STR_TO_DATE.

    Code:
    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.]

  • Users who have thanked Old Pedant for this post:

    markman641 (08-19-2011)

  • #9
    Regular Coder
    Join Date
    Jul 2011
    Posts
    272
    Thanks
    63
    Thanked 1 Time in 1 Post
    thank you so much!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •