7 Replies - 439 Views - Last Post: 14 October 2012 - 11:16 AM Rate Topic: ***** 1 Votes

#1 matt12345  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 08-June 12

Page showing earliest record instead of latest

Posted 14 October 2012 - 08:11 AM

I'm making a messaging system, like Facebook's. One can create a new message and view their interactions with other users in a conversation style interface (also like iOS). Hopefully you get the idea. On the inbox page I've written, it divides what one sees by conversation (user), but one issue is present. On the script, in my while loop, the 3rd and 4th if statements at $rek['MAX(message)'] are displaying the earliest message in a conversation. It's fine if the conversation is simply 1 message, but if it's more, I'd expect the latest to appear. I assume it has to do w/ my $feg query. Help w/ this would be greatly appreciated.


inbox.php:

 require("./connect.php");
                            $feg = mysql_query("SELECT MAX(message),con_id,id FROM `messages` GROUP BY con_id ORDER BY id DESC");
                            $rel = mysql_num_rows($feg);
                            
                          
                            echo "<center><br/><i>Inbox:</i><br/><br/></center>";
                            if($rel > 0){
                                $i=0;
                                
                                while($rek = mysql_fetch_array($feg)){
                                   
                                    
                                    
                                    if($rek['con_id'] > 1){
                                        $first = current(explode(".", $rek['con_id']));
                                        $second = next(explode(".", $rek['con_id']));
                                                if(($first === $userid) && ($second === $userid) ){
                                                    $i++;
                                                    if($i<2){
                                                        echo "<center><b>$username</b><br/><a href='./conversation.php?id=$rek[con_id]'>".$rek['MAX(message)']."</a><br/><br/></center>";
                                                    }
                                                }
                                                
                                                
                                    
                                                if(($first === $userid) && ($second !== $userid) ){
                                                    
                                                        $rt = mysql_query("SELECT * FROM `users` WHERE id='$second' GROUP BY id DESC");
                                                        
                                                        $rew = mysql_fetch_assoc($rt);
                                                        $nk = $rew['username'];
                                                                 
                                                            
                                                            $string = "<center><b>$nk</b><br/><a href='./conversation.php?id=$rek[con_id]'>".$rek['MAX(message)']."</a><br/><br/></center>";
                                                            echo $string;
                                                    
                                                     
                                                    
                                                }
                                                
                                                if(($first !== $userid) && ($second === $userid) ){
                                                      $rt = mysql_query("SELECT * FROM `users` WHERE id='$first' GROUP BY id DESC");
                                                        
                                                        $rew = mysql_fetch_assoc($rt);
                                                        $nk = $rew['username'];
                                                      
                                                            $string = "<center><b>$nk</b><br/><a href='./conversation.php?id=$rek[con_id]'>".$rek['MAX(message)']."</a><br/><br/></center>";
                                                            echo $string;
                                                 
                                                              
                                                }
                                            
                                        
                                    }   
                                }
                                    
                                  
                                
                            }else{
                                echo "<center>No messages.</center>";
                            }
                            
                    
                    
                    



mysql table setup:


CREATE TABLE IF NOT EXISTS `messages` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `sender_users_id` bigint(20) NOT NULL,
  `to_users_id` bigint(20) NOT NULL,
  `message` text NOT NULL,
  `date` text NOT NULL,
  `hide1` int(11) NOT NULL,
  `hide2` int(11) NOT NULL,
  `viewed1` int(20) NOT NULL,
  `viewed2` int(20) NOT NULL,
  `con_id` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4324324240 ;




Thanks.

This post has been edited by matt12345: 14 October 2012 - 08:15 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Page showing earliest record instead of latest

#2 CTphpnwb  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 2982
  • View blog
  • Posts: 10,297
  • Joined: 08-August 08

Re: Page showing earliest record instead of latest

Posted 14 October 2012 - 08:16 AM

Try it this way:
SELECT MAX(message) as last_message,con_id,id FROM `messages` GROUP BY con_id ORDER BY id DESC

Now would you expect $rek['last_message'] to be the first or last message?
Was This Post Helpful? 0
  • +
  • -

#3 matt12345  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 08-June 12

Re: Page showing earliest record instead of latest

Posted 14 October 2012 - 08:22 AM

View PostCTphpnwb, on 14 October 2012 - 09:16 AM, said:

Try it this way:
SELECT MAX(message) as last_message,con_id,id FROM `messages` GROUP BY con_id ORDER BY id DESC

Now would you expect $rek['last_message'] to be the first or last message?


I changed my query to what you said, and altered the $rek['MAX(message)'] spots to $rek['last_message'], but no luck. The first 2 if statements are fine though (expected). I'd want the LAST_MESSAGE to refer to the latest one in the table (highest ID number). Still not working properly-earliest message in conversation is what appears.

This post has been edited by matt12345: 14 October 2012 - 08:30 AM

Was This Post Helpful? 0
  • +
  • -

#4 CTphpnwb  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 2982
  • View blog
  • Posts: 10,297
  • Joined: 08-August 08

Re: Page showing earliest record instead of latest

Posted 14 October 2012 - 09:45 AM

Oh, sorry. I meant to say
MAX(id) as last_message

Was This Post Helpful? 0
  • +
  • -

#5 matt12345  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 08-June 12

Re: Page showing earliest record instead of latest

Posted 14 October 2012 - 10:50 AM

View PostCTphpnwb, on 14 October 2012 - 10:45 AM, said:

Oh, sorry. I meant to say
MAX(id) as last_message



After changing the code to what you said here, it now at least identifies the ID of the latest message. The only thing wrong is that it echoes the ID number of the latest message instead of the latest message itself! Help getting it to echo the proper thing would be great. Here is the code of my updated page:


 require("./connect.php");
                            $feg = mysql_query("SELECT MAX(id) as last_message,con_id,id FROM `messages` GROUP BY con_id ORDER BY id DESC");

                            $rel = mysql_num_rows($feg);
                            
                                
                            echo "<center><br/><i>Inbox:</i><br/><br/></center>";
                            if($rel > 0){
                                $i=0;
                                
                                while($rek = mysql_fetch_array($feg)){
                                   
                                    
                                    
                                    if($rek['con_id'] > 1){
                                        $first = current(explode(".", $rek['con_id']));
                                        $second = next(explode(".", $rek['con_id']));
                                                if(($first === $userid) && ($second === $userid) ){
                                                    $i++;
                                                    if($i<2){
                                                        echo "<center><b>$username</b><br/><a href='./conversation.php?id=$rek[con_id]'>".$rek['last_message']."</a><br/><br/></center>";
                                                    }
                                                }
                                                
                                                
                                    
                                                if(($first === $userid) && ($second !== $userid) ){
                                                    
                                                        $rt = mysql_query("SELECT * FROM `users` WHERE id='$second' GROUP BY id DESC");
                                                        
                                                        $rew = mysql_fetch_assoc($rt);
                                                        $nk = $rew['username'];
                                                            
                                                            $string = "<center><b>$nk</b><br/><a href='./conversation.php?id=$rek[con_id]'>".$rek['last_message']."</a><br/><br/></center>";
                                                            echo $string;
                                                            
                                                    
                                                }
                                                
                                                if(($first !== $userid) && ($second === $userid) ){
                                                      $rt = mysql_query("SELECT * FROM `users` WHERE id='$first' GROUP BY id DESC");
                                                        
                                                        $rew = mysql_fetch_assoc($rt);
                                                        $nk = $rew['username'];
                                                      
                                                            $string = "<center><b>$nk</b><br/><a href='./conversation.php?id=$rek[con_id]'>".$rek['last_message']."</a><br/><br/></center>";
                                                            echo $string;
                                                 
                                                              
                                                }
                                            
                                        
                                    }   
                                }
                                    
                                  
                                
                            }else{
                                echo "<center>No messages.</center>";
                            }
                            
                    



Thanks.

This post has been edited by matt12345: 14 October 2012 - 10:50 AM

Was This Post Helpful? 0
  • +
  • -

#6 CTphpnwb  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 2982
  • View blog
  • Posts: 10,297
  • Joined: 08-August 08

Re: Page showing earliest record instead of latest

Posted 14 October 2012 - 11:01 AM

Ok, then why are you using DESC? That's going to show the latest first and end with the first item.
Was This Post Helpful? 0
  • +
  • -

#7 matt12345  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 08-June 12

Re: Page showing earliest record instead of latest

Posted 14 October 2012 - 11:08 AM

View PostCTphpnwb, on 14 October 2012 - 12:01 PM, said:

Ok, then why are you using DESC? That's going to show the latest first and end with the first item.


I just removed "DESC." Still, the issue of the latest messages' ID numbers appearing instead of the messages themselves still is present.
Was This Post Helpful? 0
  • +
  • -

#8 CTphpnwb  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 2982
  • View blog
  • Posts: 10,297
  • Joined: 08-August 08

Re: Page showing earliest record instead of latest

Posted 14 October 2012 - 11:16 AM

What are you using MAX() for? If you want to show the id, show it. You're already getting it as part of the query.

BTW, you really should be using prepared statements.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1