Select a range around a specific row

[Wild Bill Hiccup]

Hi,

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]

Hi,

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

[strawberry]

Unfortunately, I don't think you can use variables in the LIMIT clause
- 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]

strawberry wrote:

> 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]

Taking Bill's suggestions to heart, here's my take on the problem. I
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]

There might be a couple of mistakes in the previous script. Well, there
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
*/
//run the connection script
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.");

//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"

?>

[Wild Bill Hiccup]

Very cool. I can definitely run with that. Thanks so much for the help
(and thanks to Bill Karwin as well).

wbh

strawberry wrote:

> There might be a couple of mistakes in the previous script. Well, there
> 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
> */
> //run the connection script
> 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.");
>
> //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]

Your welcome. BTW, if you wanted to show which number rows you were
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;";
echo "2nd Query = $query";
$result = mysql_query($query) or die ("Couldn't perform 2nd query.");