Ordering Problem

[Andy11548]

Hello,

 

I've got a query which is:

$replyQ = mysql_query("SELECT * FROM `forum_replys` WHERE `sub_cat_id`='".$subsF['id']."' ORDER BY `datetime` DESC");

 

I want it to show the latest reply, going by the most recent date and time field which looks like:

2011-07-11 10:23:03

 

However, the query, doesn't work. It doesn't display the lastest one, even if I changed it from 'DESC' to 'ASC', no matter what, it doesn't show the lastest replys.

 

Any ideas?

[teynon]

What type is datetime? Try sorting by a completely different field and see if it orders it at all.

 

Can we see more of your script?

[Andy11548]

I've got one page working, but I think, I've got another way of odering, what part of the code would you need? seeing as its a query problem.

 

The field type is "datetime".

[teynon]

I prefer to see all of the code because a lot of times there are other contributing factors that we are never fully aware of. Just don't include passwords and things like that.

[Andy11548]

Well, I know its not another problem, because I tried it using "ids" which is what I'm using now, but sure, have fun reading through my messy code, haha .

 

<?php
include './includes/header.php';
include './includes/scripts/shoutbox.php';

$catsQ = mysql_query("SELECT * FROM `forum_cats` ORDER BY `id` ASC");
$catsF = mysql_fetch_assoc($catsQ);
?>

<div id="forumContainer">
   <div id="forumHolder">
   <br />
      <?php
         $catsQ = mysql_query("SELECT * FROM `forum_cats`");
         $catsF = mysql_fetch_assoc($catsQ);
         
         do
         {
            echo '<div class="forumCats">'.$catsF['name'].'</div>';
            
            $subsQ = mysql_query("SELECT * FROM `forum_subs` WHERE `cat`='".$catsF['name']."'");
            $subsF = mysql_fetch_assoc($subsQ);
            
            do
            {
               $topicQ = mysql_query("SELECT * FROM `forum_topics` WHERE `sub_cat_id`='".$subsF['id']."'");
               $topicF = mysql_fetch_assoc($topicQ);
               $topicR = mysql_num_rows($topicQ);
               
               $replyQ = mysql_query("SELECT * FROM `forum_replys` WHERE `sub_cat_id`='".$subsF['id']."' ORDER BY `id` DESC");
               $replyF = mysql_fetch_assoc($replyQ);
               $replyR = mysql_num_rows($replyQ);

               echo '<div class="forumSubs">
                        <div class="subsLeft"><a href="view.php?id='.$subsF['id'].'&currentpage=1">'.$subsF['name'].'</a><br /><span>'.$subsF['description'].'</span></div>
                        <div class="subsRightH">
                           <div class="subsRLeft">Posts: '.$replyR.'<br />Topics: '.$topicR.'</div>
                           <div class="subsRRight">';
                           
                           if($replyR > 0)
                           {
                              echo 'Last post by: '.$replyF['username'].'<br />in: '.$replyF['topic_name'].'<br />';
                              if($replyF['date'] == date('Y-m-d'))
                              {
                                 echo '<strong>Today</strong> at '.$replyF['time'];
                              } else {
                                 echo 'on '.$replyF['date'].' at '.$replyF['time'];
                              }
                           } else {
                              echo 'No posts';
                           }
               echo        '</div>
                        </div>
                        <div class="clearfloat"></div>
                     </div>
                     ';
            } while($subsF = mysql_fetch_assoc($subsQ));

         } while($catsF = mysql_fetch_assoc($catsQ));
      ?>
   </div>
</div>
<?php
include './includes/footer.php';
?>

[LeadingWebDev]

SELECT *, date_format(datetime, %m/%e/%Y) as datetime FROM `forum_replys` WHERE `sub_cat_id`='".$subsF['id']."' ORDER BY `datetime` DESC

not sure, but this should help you

[teynon]

What happens if you put LIMIT 1 on that query?

[Andy11548]

I'll try it on my next page, and let you know, for now, the way I've done it using id's works just fine.

 

Thanks for helping though,

Andy.

[LeadingWebDev]

Andy, you can try using timestamp

[Andy11548]

May I ask what the difference between "Timestamp" and "datetime" is?

 

They both show up exactly the same in the database, so what is the actual difference?

 

Thanks,

Andy.