|
|
Posted by Hilarion on 11/03/27 11:19
> I can't find a sample script how to create a tollbar to
> navigate record by record.
> I have a form to display data with a small tollbar. I can
> add, del records, etc.
>
> I also can move from rec to rec but only with the ID.
>
> Sample for next:
> <a href="adresse_edit.php?id=<?= $id+1 ?>">
> <img border="0" src="images/co_avant.png" width="32"
> height="24"></a>
>
> I like to move alphabetically.
> How can I do that. Do somebody have a sample script ?
I do not have a script but have an idea.
You could pass current ID and navigation direction (previous
or next). The script that gets this info (and knows used
sort style) is able to determine what record should be
displayed.
<a href="adresse_edit.php?id=<?php echo $id; ?>&direction=next">next</a>
<a href="adresse_edit.php?id=<?php echo $id; ?>&direction=previous">previous</a>
This was easy part. The hard part is how to use that info to retrieve next or
previous record. That strongly depends on DB engine you are using (eg. most MySQL
versions do not support subquery and you use LIMIT clause to get range of records
instead of popular - in other SQL engines - TOP clause).
One way is to retrieve the whole current record (id = $id) and use the data
to find the next or previous one. Another way is to do it in single SQL select.
If the table (called eg. "recrds") looks like this:
id - INT,
t1 - VARCHAR
t2 - VARCHAR
t3 - VARCHAR
and records are sorted by: t1 ascending, t2 ascending, t3 descending,
then the first method would be something like:
SELECT * FROM recrds WHERE id = $id
store the results in $t1, $t2 and $t3 and do next select (replace TOP
with LIMIT if you use MySQL) like this (when retrieving "next"):
SELECT TOP 1 *
FROM recrds
WHERE (t1 > $t1)
OR ((t1 = $t1) AND (t2 > $t2))
OR ((t1 = $t1) AND (t2 = $t2) AND (t3 < $t3))
OR ((t1 = $t1) AND (t2 = $t2) AND (t3 = $t3) AND (id > $id))
ORDER BY t1 ASC, t2 ASC, t3 DESC, id ASC
(you can ommit last OR statement if all (t1,t2,t3) sets are always unique).
To retrieve "previous" you have to change "<" to ">" and ">" to "<" and
reverse sort order:
SELECT TOP 1 *
FROM recrds
WHERE (t1 < $t1)
OR ((t1 = $t1) AND (t2 < $t2))
OR ((t1 = $t1) AND (t2 = $t2) AND (t3 > $t3))
OR ((t1 = $t1) AND (t2 = $t2) AND (t3 = $t3) AND (id < $id))
ORDER BY t1 DESC, t2 DESC, t3 ASC, id DESC
To do the retrieval in one step you'll probably have to use JOIN.
Maybe something like this (to retrieve "previous"):
SELECT TOP 1 r1.*
FROM recrds AS r1 JOIN recrds AS r2
ON (r1.t1 < r2.t1)
OR ((r1.t1 = r2.t1) AND (r1.t2 < r2.t2))
OR ((r1.t1 = r2.t1) AND (r1.t2 = r2.t2) AND (r1.t3 > r2.t3))
OR ((r1.t1 = r2.t1) AND (r1.t2 = r2.t2) AND (r1.t3 = r2.t3) AND (id < r2.id))
WHERE r1.id <> $id AND r2.id = $id
ORDER BY r1.t1 DESC, r1.t2 DESC, r1.t3 ASC, r1.id DESC
You can move whole WHERE clause to ON clause of JOIN or rather
exchange WHERE clause with ON clause (some SQL engines will
perform better with it).
Hope this helps.
Hilarion
PS.: You could also check if there will be any next or previous for the
record you finally retrieved (to check if you should show "next"
and / or "previous" links), but this is something you could do
by changing "TOP 1" to "TOP 2" and checking how many you got.
Navigation:
[Reply to this message]
|