8 Replies Latest reply on Dec 16, 2011 9:54 AM by DonMitchinson

    Reverse Datagrid Loading Order

    Jason Woodcock Level 1

      Currently I have a datagrid loading data from a MySQL database via PHP & ZendAMF. I need to show the last rows first (reversing the order, Row 1 -> Last Row & Last Row -> First Row).

       

      Any suggestions?

        • 1. Re: Reverse Datagrid Loading Order
          Claudiu Ursica Level 4

          you want the whole result set to be reversed or only the first and last records?

           

          Either way you can do it both server side or client side.

           

          Server side:

          1. mysql refine your query or/add add some additional logic.

          2. php leave the mysql as it is alter the result on php perform logic there.

           

          Client side.

          reverse collection before passing it to vie just after the service call returns it.

          alternatively you can write a custom sorting on the collection.

           

          Show some code if this is too much theory...

           

          C

          • 2. Re: Reverse Datagrid Loading Order
            Jason Woodcock Level 1

            Well, since I'm paging the data in increments of 50, probably not a good idea to attempt it on the front in side. So, the PHP side is probably going to be the best option I think.

             

            So here is my PHP:

            public function getProjects_full_paged($startIndex, $numItems) {

                   

                    $stmt = mysqli_prepare($this->connection, "SELECT * FROM $this->tablename LIMIT ?, ?");

                    $this->throwExceptionOnError();

                   

                    mysqli_stmt_bind_param($stmt, 'ii', $startIndex, $numItems);

                    mysqli_stmt_execute($stmt);

                    $this->throwExceptionOnError();

                   

                    $rows = array();

                   

                    mysqli_stmt_bind_result($stmt, $row->project_id, $row->dateAdded, $row->Active, $row->project_Name, $row->project_SNumber, $row->project_Street, $row->project_City, $row->project_State, $row->project_County, $row->project_Zip, $row->project_Permit, $row->project_ContactName, $row->project_ContactNumber, $row->project_BuildType, $row->project_Type, $row->project_Footage, $row->project_Status, $row->project_Cost, $row->project_Deadline, $row->builder_Name, $row->builder_Address, $row->builder_City, $row->builder_State, $row->builder_County, $row->builder_Zip, $row->builder_Phone, $row->builder_Website, $row->builder_Email, $row->builder_ContactName, $row->builder_ContactNumber, $row->builder_Username, $row->owner_Name, $row->owner_Address, $row->owner_City, $row->owner_County, $row->owner_State, $row->owner_Zip, $row->owner_Phone, $row->owner_Website, $row->owner_Email, $row->owner_ContactName, $row->owner_ContactNumber, $row->owner_Username);

                   

                    while (mysqli_stmt_fetch($stmt)) {

                      $rows[] = $row;

                      $row = new stdClass();

                      mysqli_stmt_bind_result($stmt, $row->project_id, $row->dateAdded, $row->Active, $row->project_Name, $row->project_SNumber, $row->project_Street, $row->project_City, $row->project_State, $row->project_County, $row->project_Zip, $row->project_Permit, $row->project_ContactName, $row->project_ContactNumber, $row->project_BuildType, $row->project_Type, $row->project_Footage, $row->project_Status, $row->project_Cost, $row->project_Deadline, $row->builder_Name, $row->builder_Address, $row->builder_City, $row->builder_State, $row->builder_County, $row->builder_Zip, $row->builder_Phone, $row->builder_Website, $row->builder_Email, $row->builder_ContactName, $row->builder_ContactNumber, $row->builder_Username, $row->owner_Name, $row->owner_Address, $row->owner_City, $row->owner_County, $row->owner_State, $row->owner_Zip, $row->owner_Phone, $row->owner_Website, $row->owner_Email, $row->owner_ContactName, $row->owner_ContactNumber, $row->owner_Username);

                    }

                   

                    mysqli_stmt_free_result($stmt);       

                    mysqli_close($this->connection);

                   

                    return $rows;

                }

             

            How would I reverse that?

            • 4. Re: Reverse Datagrid Loading Order
              DonMitchinson Level 2

              Why don't you just add an 'ORDER BY <fieldname> DESC' to your sql statement

               

              $stmt = mysqli_prepare($this->connection, "SELECT * FROM $this->tablename LIMIT ?, ?");
              

               

              Doesn't look like it's doing any sorting now.

              Is it just coming out in order of entry?

              • 5. Re: Reverse Datagrid Loading Order
                Claudiu Ursica Level 4

                http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

                 

                you have DESC or ASC to refine the query in mySQL

                 

                 

                1 person found this helpful
                • 6. Re: Reverse Datagrid Loading Order
                  DonMitchinson Level 2

                  Just a note in case you are having problems implementing it,

                  you need to insert the "ORDER BY clause before the LIMIT.

                   

                  As in (if you want to sort by project id descending order..

                   

                  $stmt = mysqli_prepare($this->connection, "SELECT * FROM $this->tablename ORDER BY project_id DESC LIMIT ?, ?");
                  
                  1 person found this helpful
                  • 7. Re: Reverse Datagrid Loading Order
                    Jason Woodcock Level 1

                    Forgot to mark this one as Answered! You two have been a huge help. Here's what I ended up utilizing and it worked perfectly:

                     

                     

                    $stmt = mysqli_prepare($this->connection, "SELECT * FROM $this->tablename ORDER BY project_id DESC LIMIT ?, ?");

                    • 8. Re: Reverse Datagrid Loading Order
                      DonMitchinson Level 2

                      Thanks - and glad to hear you're up and running.

                      I know these forums have been a great help to me so I always feel like I owe it something

                       

                      Have a good one