select query help

sd1

hello all
i try to build a query that will give me range of rows.
i post with a form 3 variables in PHP:

rowID
numRowsBefore
numRowsAfter

what should be the syntax to get the number of rows in the range? do i need more than one query?

bogu

What numRowsAfter means?

sd1

it means that i need the next rows (the num that is in the vairables) in the database

bogu

I think I understand, u know the rowID and need to retreive the numbers of row befor that Id and after that Id, how are the records sorted? by rowId?

sd1

yes, that's exactly what i need.
yes, the table is sorted by row id

bogu

Use 2 queries;

SELECT COUNT() FROM table WHERE rowId<$rowID = numRowsBefore
SELECT COUNT() FROM table WHERE rowId>$rowID = numRowsAfter

sd1

ohh. 10x. but sorry, i didn't mean that
i don't need the num of rows, i need the rows

for exmaple:
let say i have this rows:
id: 99
id: 100
id: 101
id: 102
id: 103
id: 104
id: 105
id: 106
id: 107

now, let say that i post this info:
rowID = 103
numRowsBefore = 2
numRowsAfter = 1

the query should output rows:
101
102
103
104

bogu

SELECT * FROM table WHERE rowID<=$rowID LIMIT $numRowsBefore = numRowsBefore and the row itself ...
SELECT * FROM table WHERE rowId>$rowID LIMIT $numRowsAfter = numRowsAfter

sd1

SELECT * FROM table WHERE rowID<=$rowID LIMIT $numRowsBefore = numRowsBefore

i don't understantd this phrase: "$numRowsBefore = numRowsBefore "

i don't have a numRowsBeofre field in my database, i have only id field

leatherback

Hi,

read bogu's solution like:

SELECT * FROM table WHERE rowID<=$rowID LIMIT $numRowsBefore

ALthough it might be prudent to add a bit:

SELECT * FROM table WHERE rowID<=$rowID LIMIT $numRowsBefore order by rowID asc

this will:
Select all fields from table
where the RowID is less then or equal to the selected
You limit the number of recors to be retrieved to a maximum of $numrowsbefore
and you roder the records in the database by rowID in ascending order

bogu

sd1 wrote:
i don't understand this phrase: "$numRowsBefore = numRowsBefore "

That equal represents: that select will extract the rows before and I represented as = numRowsBefore, so your query is like leatherback wrote, I forgot about ORDER BY ...

SELECT * FROM table WHERE rowID<=$rowID LIMIT $numRowsBefore order by rowID asc

Dont write the "= numRowsBefore" it represents only the results that your select will return ...