Select a range around a specific row
Wild Bill Hiccup
I need to select a range of ten rows around a specific row such that the
specific row floats to the beginning or end of the set as the set
approaches the beginning or end of the (ordered) table.
So, for example, if I had a hundred rows with the values 1-100 in them,
in no particular order, and the specific row was 2, I would need the set
1 2 3 4 5 6 7 8 9 10. If the specific row was 20, I would need
something more like 17 18 19 20 21 22 23 24 25 26, and if the row was
100, I would need 91 92 93 94 95 96 97 98 99 100.
Make sense? Anyone have a suggestion as to how I would go about this?
Thanks,
wbh
Wild Bill Hiccup
strawberry
- whch would probably be necessary to make this work. It doesn't mean
it can't be done. It just can't be done in a single elegant SELECT
statement.
Bill Karwin
> Unfortunately, I don't think you can use variables in the LIMIT clause
Correct. I recall that LIMIT accepts only literal integer constants,
not expressions.
> - whch would probably be necessary to make this work. It doesn't mean
> it can't be done. It just can't be done in a single elegant SELECT
> statement.
On the other hand, there are probably several solutions possible in
application code languages that are more elegant than what could be
possible in SQL.
Once you have an array containing all the values of the key values, app
languages like Java, Perl, or PHP give you the ability to easily select
elements from the array by subscript, calculate proportional position
within the range, and display subsets of the array.
Regards,
Bill K.
strawberry
don't pretend that it's elegant. I don't even swear that it's right but
I'm showing my thinking too - in case I have gone wrong somewhere...
The figure below is a simplified drawing of a scrollbar.
A----------x--p------y-------------------------------------B
A and B represent the start and end of the scrollbar
x and y represent the beginning and end of the button on the
scrollbar.
(Coming back to your original enquiry x and y also represent the
two parts of the LIMIT STATEMENT, as in "SELECT * FROM TABLE LIMIT
x,y")
p is the position of the button on the line AB
Notice that p is nearer to x than it is to y. This is because p is
nearer to A than B.
We can say that Ap is to AB as xp is to xy
OR
xp = (Ap/AB) * xy
The position of x (or Ax) on line AB is found by subtracting xp
from Ap:
Ax = Ap - xp
where
Ap is a variable integer provided by you, the user
xp is rounded up or down to an integer value
xy is (at least in this example) a constant integer range (the 'y'
part of the above select statement)
also provided by you
and
AB is a variable found by examining the length of the scrollbar
(or the number of rows in a table).
To relate this to your original enquiry,
AB is the same as saying "SELECT COUNT(*) FROM TABLE;"
All of that preamble serves to illustrate the meaning of each of the
variables in the following script:
<?php
/* Program: pos.php
* Desc: A Point of Scale Display
*/
include('path/to/my/connection/script');
//Define all the key values Bill was talking about
$mytable = table //The data we want to use for the query
$query = mysql_query("SELECT COUNT(*) FROM $mytable");
//Refer to scrollbar diagram for meaning of each of the following.
$AB = mysql_result($query,0);
$Ap = 132;//A variable (between A & B) assigned by the user.
$xy = 10; //A range, currently a constant
$xp = round($xy*($Ap/$AB));
$Ax = $Ap - $xp;
/* Display results */
//And so to the resulting query...
$query = "SELECT * FROM $mytable LIMIT $Ax,$xy;";
$result=mysql_query($query) or die ("Couldn't do it.");
//Show the results in a bit of HTML
echo "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo "\t<tr>\n";
foreach ($line as $col_value) {
echo "\t\t<td>$col_value</td>\n";
}
echo "\t</tr>\n";
}
echo "</table>\n"
?>
strawberry
usually are! I've mucked around with a tiny bit more. It's still not
quite right - it's 1 row out I think!
<?php
/* Program: pos.php
* Desc: A Point of Scale Display
*/
include('connection/script');
//Define all the key values Bill was talking about
$mytable = table; //The data we want to use for the query
$query = "SELECT COUNT(*) FROM $mytable;";
$result = mysql_query($query) or die ("Couldn't perform 1st query.");
//Refer to scrollbar diagram for meaning of each of the following.
$AB = mysql_result($result,0);
$Ap = 139;//A variable (between A & B) assigned by the user.
$xy = 10; //A range, currently a constant
$xp = round($xy*($Ap/$AB));
$Ax = $Ap - $xp;
//And so to the resulting query...
$query = "SELECT * FROM $mytable LIMIT $xy OFFSET $Ax;";
echo "2nd Query = $query";
$result = mysql_query($query) or die ("Couldn't perform 2nd
query.");
Wild Bill Hiccup
(and thanks to Bill Karwin as well).
wbh
strawberry
currently displaying you could modify the query as follows...
//And so to the resulting query...
$set = "set @a = $Ax;";
$result = mysql_query($set) or die ("Couldn't perform set.");
$query = "SELECT @a := @a + 1 AS row_number, client_id,
client_name FROM $mytable LIMIT $xy OFFSET $Ax;";