To solve a problem like yours I ussualy do the following:
First you need to use a deterministic order criteria when displaying the
results, this means that according to the order columns you provide, MySQL
will not have to decide how to order two rows that have the same values for
this columns. For example, if you order an users table by the first name of
the user, there might be two users with the same name, and MySQL will order
them the way it founds more convenient.
One way to do this is using an uniquely indexed column in the order
criteria. The primary key would be a nice idea. You don't need to enforce an
order criteria to the listing, you can simply add the primary key as the
last column in the order by and that will be enough.
Then, look for the record you need and store the values it has for the
columns used in the order. With that info you can build a query which will
return how many records there are, in the listing, before the one you sought
before:
SELECT COUNT(*)
FROM table
WHERE order_column1 < 'order_column1_value'
OR (order_column1 = 'order_column1_value' AND order_column2 <
'order_column2_value')
OR (order_column1 = 'order_column1_value' AND order_column2 =
'order_column2_value' AND order_column3 < 'order_column3_value')
...etc...
If the order direction of one column is descending instead of ascending, you
should use > instead of < for that specific column.
If you're worried about performance you should add indexes to the columns of
the order criteria, which you probably have done so anyway. Also you should
consider splitting this query in many queries:
SELECT COUNT(*)
FROM table
WHERE order_column1 < 'order_column1_value'
SELECT COUNT(*)
FROM table
WHERE order_column1 = 'order_column1_value'
AND order_column2 < 'order_column2_value'
SELECT COUNT(*)
FROM table
WHERE order_column1 = 'order_column1_value'
AND order_column2 = 'order_column2_value'
AND order_column3 < 'order_column3_value'
etc...
and sum all the results. It seems that sometimes this run faster with a
MySQL server, anyway, give it a try if you're worried about the query
performance.
The result will be the number of records before the one you sought, with
that info getting the page of the record is piece of cake.
2006/12/23, T.J. Mahaffey <tj@xxxxxxxxxxxxxx>:
I see now that I did not explain myself adequately.
I think "jump to record" was the wrong way to put it. So, here goes.
I already have excellent paging functionality working well, based on
a nice tutorial at PHPFreaks.
My problem is that when a user performs a search, I need to display
the page on which their search string is found, but still display ALL
records within the paging of the entire database.
I've since discovered the core of what I need to do:
1. I can find the record I need through a simple query.
2. I can easily determine WHICH page this record is on by counting
BACKWARDS from the found record to the FIRST record, totaling the
number of records from record 1 to the found record. Then, by
performing a bit of division, I can determine which page that record
appears on and direct the user to "...page=8" via $_GET.
SO, my question is: how might I have MySQL tell me how many records
came BEFORE the found record?
(FYI: there is currently no auto-incrementing ID on these records, so
that obviously easy solution would be unavailable.)
Thanks in advance for any insight.
--
T.J. Mahaffey
tj@xxxxxxxxxxxxxx
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
