Re: Select a range around a specific row 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"
?> |