View Single Post

   
  #4 (permalink)  
Old 02-28-2008, 08:35 AM
strawberry
 
Posts: n/a
Default 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"


?>

Reply With Quote