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?
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.
[RESOLVED] Select Previous or Next row
Reply With Quote
Bookmarks