www.webdeveloper.com
Results 1 to 3 of 3

Thread: [RESOLVED] Select Previous or Next row

  1. #1
    Join Date
    Jul 2010
    Posts
    135

    resolved [RESOLVED] Select Previous or Next row

    Hi,
    few day I try to find how to do this. I have a form into which I load some data with AJAX. I have 8 select boxes there to filter (4 boxes) or to order columns (4 boxes). When I prepare SQL query I know id of actual row and values of columns of actual row that I are concerned. And I need to find how to get previous row and next row from selection, which is filtered by condition and which is ordered by condition.

    For example we have id, product, type, date, color, desc ...
    I want to filter and order by product, type, date, color. And I want to LIMIT them LIMIT 0,1

    Where condition to filter could be
    Code:
    WHERE product='car' AND type='trucker' AND date '01-01-2000' AND color="white'
    To find previous row I need to define next part of WHERE condition and here I am not sure if I do it right or how should it be right. I would try to substitute concerned values from actual row like this:

    Code:
    WHERE product<$product AND type<$type AND date<$date' AND color=$color AND
    But this means that I would not find category "car", but different. Something like "banana" or so. And type to be find in this way not that I look for, but different one, and so on. So this is incorerect

    Then I could use this condition:

    Code:
    WHERE product='car' AND type='trucker' AND date '01-01-2000' AND color="white' AND id<50'
    This would find all products that are car, trucker and made at 01-01-2000 and are white but if the products have bigger id than this one, then no result I have found. I don't know if this logic is correct. I try this principle to test loading Previous and Next record and also First and Last record. But nothing doesn't works correctly.

    For example I have record id=24. I press Next. I got id=34. I pres Prev. I have 23. I press Next, I have 34.

    Or this: I have 24, I press Next (35), Next (36), Next (37), Next (38), Next (39), Next (40), Next (52). Then I press back Previous and I god 23!

    When I use Previous button, I can got to id=1.

    When I use First or Last record search, it always retuns to id=23. And First record had none WHERE condition just ORDER BY columnx, columny, columnz, id ASC. For Last record search I use `id`=(select MAX(`id`))

    Can somebody help me to find correct WHERE cause for this queries?
    Last edited by crazy boy; 09-15-2011 at 03:59 AM.

  2. #2
    Join Date
    Jul 2010
    Posts
    135

    solved

    I found a solution.

    First to create a mysql View like this:

    Code:
    CREATE VIEW test_view AS SELECT a. * , b. *
    FROM basic a
    JOIN details b ON a.`id` = b.`basic_id`
    a and b are aliases to tables that contain data to be loaded to one form. This creates a view for all columns of my table.

    Then I have PHP code for AJAX. This code is not absolutely finished, but the Prev. and Next feature is working already. If somebody does know how to solve the Home/First and End/Last please let me know. So when you click on a tag, the AJAX command for loading Pervious or Next record will happen.

    This php code solves preparing ORDER BY and WHERE command
    Code:
       $where = " y.`id` = ".((int) $args->post['rid'])." AND";
       $order .= "x.`cat`, x.`id` "; // TO ORDER BY CATEGORY AND THEN BY ID
       if ($args->got['home']==1){ // FAILS: FIND FIRST RECORD
        $whererow .= $where;    
        $whererow .= "
        ( x.`cat` = (select MIN(y.`cat`) ) OR 
        ( x.`cat` = y.`cat` AND x.`id` < y.`id`))";           
        $order = $order."DESC ";
        }
       elseif ($args->got['end']==1){ // FAILS: FIND LAST RECORD
        $whererow .= $where;    
        $whererow .= "
        ( x.`cat` = (select MAX(y.`cat`) ) OR 
        ( x.`cat` = y.`cat` AND x.`id` > y.`id`))"; 
        $order = $order."ASC  ";
        }
       elseif ($args->got['prev']==1){ // WORKS: FIND PREVIOUS
        $whererow .= $where;    
        $whererow .= "
        ( x.`cat` < y.`cat` OR 
        ( x.`cat` = y.`cat` AND x.`id` < y.`id`))";           
        $order = $order."DESC ";
        }
       elseif ($args->got['reload']==1){
        $whererow .= $where;    
        $whererow .= " x.`id`=".((int) $args->post['rid'])." AND ";
        $order = $order."ASC ";
        }
       elseif ($args->got['next']==1){ // WORKS: FIND NEXT
        $whererow .= $where;    
        $whererow .= "
        (x.`cat` > y.`cat` OR 
        (x.`cat` = y.`cat` AND x.`id` > y.`id`))";           
        $order = $order."ASC ";
        }
       else die(json_encode( array ( "error" => "Incorrect url action.")));
       $limit = " LIMIT 0,1 ";
       $all_cols = ' x.* ';
       $order = "ORDER BY ".$order;
       $sqlBasic->where = " $whererow $order $limit "; // SAVE WHERE STRING
    //  die($sqlBasic->where);
       $result["result"] =  $mysql->query("SELECT_BY_AJAX", $all_cols, null, array($sqlBasic, $sqlDetails));  // pass information for tables 'basic' and 'details', there are preset info like db name, table name, where condition etc
    Next code is method query()
    Code:
    public function query($command, $columns, $value, $sqlTokenObj) {
    ... some preparing lines ....
    switch ($command):
          case "SELECT_BY_AJAX":
          $query = "SELECT x.* FROM  test_view x, test_view y       $where";
          $result = mysql_query($query);
    break;
    }
    Results in queries:

    Previous:
    Code:
    SELECT x.* FROM  test_view x, test_view y WHERE   y.`id` = 13 AND
        ( x.`cat` < y.`cat` OR 
        ( x.`cat` = y.`cat` AND x.`id` < y.`id`)) ORDER BY x.`cat`, x.`id` DESC   LIMIT 0,1
    Next:
    Code:
    SELECT x.* FROM  test_view x, test_view y WHERE   y.`id` = 13 AND
        (x.`cat` > y.`cat` OR 
        (x.`cat` = y.`cat` AND x.`id` > y.`id`)) ORDER BY x.`cat`, x.`id` ASC   LIMIT 0,1
    I hope this will help to anybody, and if anybody knows hows to finish the Home and End feature would help I would be pleased. I am very poor if it comes to mysql.
    Last edited by crazy boy; 09-20-2011 at 10:49 AM.

  3. #3
    Join Date
    Jul 2010
    Posts
    135
    Yet two more repairs in ORDER BY and WHERE cond.

    Code:
      foreach ($selects['order'] as $col => $column):
        $order .= " x.`$column`"; // COLUMN TO ORDER
       if ($args->got['home']==1){
        $order .= " ASC,";
        }
       elseif ($args->got['end']==1){
        $order .= " DESC,";
        }
       elseif ($args->got['prev']==1){
        $order .= " DESC,";
        }
       elseif ($args->got['reload']==1){
        $order .= " ASC,";
        }
       elseif ($args->got['next']==1){
        $order .= " ASC,";
        }
       endforeach;
    
       $where = " y.`id` = ".((int) $args->post['rid'])." AND"; // u = mus&#237; b&#253;t druh&#253; alias, ne prvn&#237;, jiank to nefunguje!
       if ($args->got['home']==1){
        $whererow .= "x.`id` > -1 ";
        $order = $order." x.`id` ASC";
        }
       elseif ($args->got['end']==1){
        $whererow .= "x.`id` > -1 ";
        $order = $order." x.`id` DESC";
        }
       elseif ($args->got['prev']==1){
        $whererow .= $where;
        $whererow .= "
        ( x.`cat` < y.`cat` OR 
        ( x.`cat` = y.`cat` AND x.`id` < y.`id`))";           
        $order = $order." x.`id` DESC";
        }
       elseif ($args->got['reload']==1){
        $whererow .= $where;
        $whererow .= " x.`id`=".((int) $args->post['rid'])." AND ";
        $order = $order." x.`id` ASC";
        }
       elseif ($args->got['next']==1){
        $whererow .= $where;
        $whererow .= "
        (x.`cat` > y.`cat` OR 
        (x.`cat` = y.`cat` AND x.`id` > y.`id`))";           
        $order = "x.`cat` ASC, x.`id` ASC";
        }
       else die(json_encode( array ( "error" => "Incorrect url action.")));
       $limit = " LIMIT 0,1 ";
       $all_cols = ' x.* ';
       $order = "ORDER BY ".$order;
       $sqlBasic->where = " $whererow $order $limit ";
       $result["result"] =  $mysql->query("SELECT_BY_AJAX", $all_cols, null, array($sqlBasic, $sqlDetaily));   
      die();
    Now looking for solution for multiple columns to order

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles