Web Analytics Made Easy -
StatCounter sort by date - CodingForum

Announcement

Collapse
No announcement yet.

sort by date

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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)
    ?>
    Ever want to see inside of a rave? Check out RedRoll and join our community of EDM lovers and rave fanatics.

  • #2
    <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.
    Be yourself. No one else is as qualified.

    Comment


    • #3
      how does datetime work? i googled it and i couldnt find anything that worked
      Ever want to see inside of a rave? Check out RedRoll and join our community of EDM lovers and rave fanatics.

      Comment


      • #4
        do i just go into phpmyadmin and change date value to datetime?
        Last edited by markman641; Aug 18, 2011, 07:02 PM.
        Ever want to see inside of a rave? Check out RedRoll and join our community of EDM lovers and rave fanatics.

        Comment


        • #5
          okay, i switched to to datetime, now it all says 0000-00-00 00:00:00 even with new entries
          Ever want to see inside of a rave? Check out RedRoll and join our community of EDM lovers and rave fanatics.

          Comment


          • #6
            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.
            Be yourself. No one else is as qualified.

            Comment


            • #7
              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; Aug 18, 2011, 08:43 PM.
              Ever want to see inside of a rave? Check out RedRoll and join our community of EDM lovers and rave fanatics.

              Comment


              • #8
                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.]
                Be yourself. No one else is as qualified.

                Comment


                • #9
                  thank you so much!
                  Ever want to see inside of a rave? Check out RedRoll and join our community of EDM lovers and rave fanatics.

                  Comment

                  Working...
                  X