Wednesday, September 19, 2012

Display Mysql records in reverse order with descending order.




Category: MySql, PHP, CodeIgniter and Core PHP

Description:  
                       While working with social media website I came across the scenario of displaying comments in reverse order. Such as, display the most recent comments at the end.


Example:

 

 Development view: 

                     MySql displays records either in ascending or descending order. Therefore, results would display,

  • the most recent record in bigining or
  • the oldest record in biginning.

The list will be in ascending or descending order as represented the following,


But our requirement is to display records in reverse order.  such as,







For Core PHP users:


    $out='';

    $sql = mysql_query('SELECT `id` FROM `tbl_name` ORDER BY `id` DESC LIMIT 0,3');
    
    if(mysql_num_rows($sql)!=0) {
    
            //REVERSE THE RECORD LIST
    
            while($out=mysql_fetch_array($sql))  {
                            
                      $arr_list = (array) $sql;  //convert object arrays into arrays
    
                      // consider only 'result_array' element in an array :
                      $result_array=($arr_list['result_array']); 
    
                      $reved_rows_arry = array_reverse($result_array);  //Reverse the array elements
                    
            }
    
           //DISPLAY THE COMMENTS IN REVERSE
    
           foreach($reved_rows_arry as $row) {// Display the resulting array
    
                      $comment_text = mysql_query('SELECT * FROM `tbl_name` WHERE `id`='.$row['id']);
    
                       $out=mysql_fetch_row($comment_text);
    
                                   
    
           }
    
    }
    else {
    $out .="No records foind!";
    }
    
    echo $out;


And, following code involves PHP - MySql in CodeIgniter framework:

 
CommentsContoller.php


public function disp_comments() {
                $this->load->model("commentsmodel");
                $this->commentsmodel->dispcomments();
}

This is the controller file with disp_comments() controller, which loads the Comments model and calls the dispcomments() method of the model.


CommentsModel.php

public function dispcomments() {
                $outout='';
                $comment = $this->db->query('SELECT `content_id` FROM `tbl_comment` ORDER BY `comment_id` DESC LIMIT 0,3');      
if($comment->num_rows()!=0) {
                                foreach($comment->result_array() as $out)  {
                                                //convert object arrays into arrays
                                                $comment_1 = (array) $comment;
//consider only 'result_array’ element  in an array
                                                $comment_array=($comment_1['result_array']);
                                                //Reverse the array elements
$comment_rev = array_reverse($comment_array);
                                }
                                foreach($comment_rev as $row) { // Display the resulting array
                                                $comment_text = $this->db->query('SELECT * FROM `tbl_comment_content` WHERE `content_id`='.$row['content_id']);
                                                $text=$comment_text->row();
                                                $outout.='<!-- (Commentor Image) (Comment ) -->';
                                }
} else $outout.="No records foind!";
echo $outout;
}


CommentsViewPage.php
$.ajax({
type: "POST",
url: "<?php echo site_url().'/addpostcomment/disp_comments'; ?>",
                success: function(return_data,textStatus){
                                $(".comments_output").html(return_data);
                }
});