Newbie Questions

[VMax]

Hey All,

These are probably pretty basic but I've looked around awhile and can't find the answers.

I need to do 2 things:

1. I need to be able to get the number of a queried row. I have a column with user id's but it's an auto increment field and when users are deleted the row numbers change, but not the user's id.

The users are input into the database in a specific order ie: their row. I need to be able to show them how many rows are above them. Once I can get the row number they're in I can use COUNT(id) to find out how many above.

2. I need to be able to show info for 20 rows above and below a given row of data. No idea what sort of query to use for this.

Thanks in advance for any help with this.

Vince

[laserlight]

For #1 you could select all the row ids less than the one given.
A count of the number of rows will also return the required number of rows.

For #2 using LIMIT in a SELECT query might help.

[VMax]

thanks for the response but that really doesn't mean anything to me....sorry 🙁

I'm going to need some code to look at so I can understand what you're talking about.

Thanks laserlight!

V

[nemonoman]

What you are trying to do is harder than it may first appear.

To put some code behind LaserLight's good recommendations

SELECT COUNT(*)
FROM mytable
WHERE id<$targetid

For the 20-above/20-below scenario, I think you'll have trouble doing this in 1 query.

Best would be to combine the array of results from 2 queries. USE ORDER BY and LIMIT to get 20 rows above and below the targe:

For 20 less than or = to target:
SELECT * FROM mytable WHERE id<=$targetid
ORDER BY ID DESC
LIMIT 20

For 20 more than target
SELECT * FROM mytable WHERE id>$targetid
ORDER BY ID
LIMIT 20

Take the results of the 2 returned arrays and sort them using the [man]sort[/man] function.

[Roger_Ramjet]

Just a note to nemonoman's post: a UNION query http://dev.mysql.com/doc/mysql/en/UNION.html will do what you need rather than the 2 queries and sorting an array

$sql = "SELECT * FROM mytable WHERE id>=(" . $targetid . "-20)
ORDER BY id 
LIMIT 20 
UNION
SELECT * FROM mytable WHERE id>" . $targetid . "
ORDER BY id
LIMIT 20"

[VMax]

You Guys are Fabulous!

Thanks so much!

Vince