Home > Archive > PHP SQL > July 2005 > Previous and Next ID ?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Previous and Next ID ?
|
|
|
| Hello
Could somebody help me to resolve this problem?
I have a fist php script where I make a query on table.
In this script I display the selected query (table) by
name.
When I click on a record I call a second php script where I
display this record in details.
// Query from the second PHP script:
$query = "SELECT DATE_ENR, NO_ADR, FAMILLE, POLITESSE, NOM,
ADR1, ADR2,RUE,CD_PAYS, PAYS, NP, CANTON, LIEU, TEL, FAX,
TEL_OPT, TEL_OPT_NO,INFO1, INFO2, INFO3, GROUPE1, GROUPE2,
GROUPE3, GROUPE4,GROUPE5, GROUPE6, GROUPE7, GROUPE8,EMAIL,
WEB FROM adresse WHERE ID=$id order by nom";
I like also to know the next and previous ID from the
selected table (ID). But alphabetically
Sample:
ID NOM
145 Armand <-- previous
123 Dupont <-- selected ID
111 Martin <-- next
How can I do that ?
Many thanks in advance for your help.
Regards
Otto
| |
| Stefan Rybacki 2005-07-24, 8:34 pm |
| Otto wrote:
> Hello
>
> Could somebody help me to resolve this problem?
>
> I have a fist php script where I make a query on table.
> In this script I display the selected query (table) by
> name.
> When I click on a record I call a second php script where I
> display this record in details.
>
> // Query from the second PHP script:
> $query = "SELECT DATE_ENR, NO_ADR, FAMILLE, POLITESSE, NOM,
> ADR1, ADR2,RUE,CD_PAYS, PAYS, NP, CANTON, LIEU, TEL, FAX,
> TEL_OPT, TEL_OPT_NO,INFO1, INFO2, INFO3, GROUPE1, GROUPE2,
> GROUPE3, GROUPE4,GROUPE5, GROUPE6, GROUPE7, GROUPE8,EMAIL,
> WEB FROM adresse WHERE ID=$id order by nom";
>
> I like also to know the next and previous ID from the
> selected table (ID). But alphabetically
>
> Sample:
> ID NOM
> 145 Armand <-- previous
> 123 Dupont <-- selected ID
> 111 Martin <-- next
>
> How can I do that ?
>
Maybe this way:
previous:
SELECT * FROM adresse WHERE nom<='Dupont' AND id<>123 ORDER BY nom DESC LIMIT 1
next:
SELECT * FROM adresse WHERE nom>='Dupont' AND id<>123 ORDER BY nom ASC LIMIT 1
Regards
Stefan
> Many thanks in advance for your help.
>
> Regards
>
> Otto
>
>
>
| |
| Hilarion 2005-07-24, 8:34 pm |
| > Maybe this way:
>
> previous:
> SELECT * FROM adresse WHERE nom<='Dupont' AND id<>123 ORDER BY nom DESC LIMIT 1
>
> next:
> SELECT * FROM adresse WHERE nom>='Dupont' AND id<>123 ORDER BY nom ASC LIMIT 1
This will work OK only if "nom" values are unique. If they are not, then you'll
always get other "duplicate" of current record as previous and as next.
In that case you should use something like:
previous:
SELECT *
FROM adresse
WHERE (nom < 'Dupont') OR (nom = 'Dupont' AND id < 123)
ORDER BY nom DESC, id DESC
LIMIT 1
next:
SELECT *
FROM adresse
WHERE (nom > 'Dupont') OR (nom = 'Dupont' AND id > 123)
ORDER BY nom ASC, id ASC
LIMIT 1
I used column "id" to make the entries distinct AND ordered. You could
use some another column or columns (the whole set of coulmns used
has to give unique entries). The more columns you use, the more
complex the query gets.
If "nom" values are unique, then you could use simplier queries:
previous:
SELECT *
FROM adresse
WHERE nom < 'Dupont'
ORDER BY nom DESC
LIMIT 1
next:
SELECT *
FROM adresse
WHERE nom > 'Dupont'
ORDER BY nom ASC
LIMIT 1
Hilarion
| |
| Stefan Rybacki 2005-07-24, 8:34 pm |
| Hilarion wrote:
>
>
> This will work OK only if "nom" values are unique. If they are not, then
> you'll
> always get other "duplicate" of current record as previous and as next.
> In that case you should use something like:
>
No, thats wrong. Since I used the constraint of id<>123!
> previous:
> SELECT *
> FROM adresse
> WHERE (nom < 'Dupont') OR (nom = 'Dupont' AND id < 123)
> ORDER BY nom DESC, id DESC
> LIMIT 1
Who says that the previous item has an id that is lower than the current id?
>
> next:
> SELECT *
> FROM adresse
> WHERE (nom > 'Dupont') OR (nom = 'Dupont' AND id > 123)
> ORDER BY nom ASC, id ASC
> LIMIT 1
same here.
>
>
>...
>
> Hilarion
Regards
Stefan
| |
|
| Hello Hilarion
> This will work OK only if "nom" values are unique. If they are not, then you'll
> always get other "duplicate" of current record as previous and as next.
> In that case you should use something like:
>
Many thanks four your quick answer.
Yes you are right, they can have duplicate records. In fact I like to now the
next ID and previous ID than I can make a skip and skip -1 button at the bottom
of the windows.
Someting like:
+---------------------+
¦ Nom : xxxxx ¦
¦ Prénom : xxxxx ¦
¦ ... ¦
¦ ¦
¦ ¦
+---------------------+
< > NEW SAVE DEL <-- Buttons
Until yet I did it like that (button to display the next record):
<a href="adresse_edit.php?id=<?= $id+1 ?>">
<img border="0" src="images/co_avant.png" width="32" height="24"></a>
But this solution do not skip to the next record alphabetikally!..
| |
| Hilarion 2005-07-24, 8:34 pm |
| >>> Maybe this way:
>
> No, thats wrong. Since I used the constraint of id<>123!
You are wrong. If there are two "Dupont" entries, one with ID 123 and
another with for example 321 then your "previous" select would return
the record with ID 321, and your "next" select would also return
ID 321. If the current record was 321, then "previous" and "next"
would return 123.
>
> Who says that the previous item has an id that is lower than the current id?
I said. To make "next" and "previous" work one have to decide what is the
order of records which identical "nom" value. I decided that I'll use
ID in ascending order. I also wrote that:
'You could use some another column or columns (the whole set of coulmns used
has to give unique entries). The more columns you use, the more complex the
query gets.'
What I did not wrote is that you can choose ordering of those column values
as you want. It was easier for me to use same ordering direction as for
"nom" column.
>
> same here.
Same here.
Regards
Hilarion
| |
| Stefan Rybacki 2005-07-24, 8:34 pm |
| Hilarion wrote:
>
>
> You are wrong. If there are two "Dupont" entries, one with ID 123 and
> another with for example 321 then your "previous" select would return
> the record with ID 321, and your "next" select would also return
> ID 321. If the current record was 321, then "previous" and "next"
> would return 123.
>
yes sorry, didn't recognized this case. Whatever adding "AND id <> previousid" should do
the trick or the use of your queries ;)
>
>...
>
> I said. To make "next" and "previous" work one have to decide what is the
> order of records which identical "nom" value. I decided that I'll use
> ID in ascending order. I also wrote that:
> 'You could use some another column or columns (the whole set of coulmns
> used
> has to give unique entries). The more columns you use, the more complex the
> query gets.'
> What I did not wrote is that you can choose ordering of those column values
> as you want. It was easier for me to use same ordering direction as for
> "nom" column.
>
>
Thats fine.
> Regards
> Hilarion
Regards
Stefan
| |
| Hilarion 2005-07-24, 8:34 pm |
| >>>>> Maybe this way:
>
> yes sorry, didn't recognized this case. Whatever adding "AND id <> previousid"
> should do the trick or the use of your queries ;)
Nope :).
Adding "AND id <> previousid" will stil be not enough.
If there are two "Dupond" entries, then always the other one will appear
as "previous". So clicking "previous" link the user will see only those
two records.
If there are three "Dupont" entries then always other two are "previous"
and "next". Which means that if user visits one of them, he will not be
able to get to other (not "Dupond") entries (using "previous" or "next"
links).
The only way to make "previous" and "next" work properly is to use
column subset which has unique values. This whole subset has to be used
in ORDER BY clause and in WHERE clause (the more columns, the more
complex this clause gets). Example for three columns (again all ordered
ascending to make things simplier):
next:
SELECT *
FROM some_tables
WHERE (col1 > $curr_col1)
OR (col1 = $curr_col1 AND col2 > $curr_col2)
OR (col1 = $curr_col1 AND col2 = $curr_col2 AND col3 > $curr_col3)
ORDER BY col1 ASC, col2 ASC, col3 ASC
LIMIT 1
previous:
SELECT *
FROM some_tables
WHERE (col1 < $curr_col1)
OR (col1 = $curr_col1 AND col2 < $curr_col2)
OR (col1 = $curr_col1 AND col2 = $curr_col2 AND col3 < $curr_col3)
ORDER BY col1 DESC, col2 DESC, col3 DESC
LIMIT 1
Hilarion
| |
| Stefan Rybacki 2005-07-24, 8:34 pm |
| Hilarion wrote:
>
>
> Nope :).
> Adding "AND id <> previousid" will stil be not enough.
> If there are two "Dupond" entries, then always the other one will appear
> as "previous". So clicking "previous" link the user will see only those
> two records.
> If there are three "Dupont" entries then always other two are "previous"
> and "next". Which means that if user visits one of them, he will not be
> able to get to other (not "Dupond") entries (using "previous" or "next"
> links).
oh yes, damn ;)
>
> The only way to make "previous" and "next" work properly is to use
> column subset which has unique values. This whole subset has to be used
> in ORDER BY clause and in WHERE clause (the more columns, the more
> complex this clause gets). Example for three columns (again all ordered
> ascending to make things simplier):
>
yes I agree to this.
> next:
> SELECT *
> FROM some_tables
> WHERE (col1 > $curr_col1)
> OR (col1 = $curr_col1 AND col2 > $curr_col2)
> OR (col1 = $curr_col1 AND col2 = $curr_col2 AND col3 > $curr_col3)
> ORDER BY col1 ASC, col2 ASC, col3 ASC
> LIMIT 1
>
> previous:
> SELECT *
> FROM some_tables
> WHERE (col1 < $curr_col1)
> OR (col1 = $curr_col1 AND col2 < $curr_col2)
> OR (col1 = $curr_col1 AND col2 = $curr_col2 AND col3 < $curr_col3)
> ORDER BY col1 DESC, col2 DESC, col3 DESC
> LIMIT 1
>
>
> Hilarion
| |
| Hilarion 2005-07-25, 9:28 am |
| >> This will work OK only if "nom" values are unique. If they are not, then you'll
>
> Many thanks four your quick answer.
>
> Yes you are right, they can have duplicate records. In fact I like to now the
> next ID and previous ID than I can make a skip and skip -1 button at the bottom
> of the windows.
>
> Someting like:
> +---------------------+
> ¦ Nom : xxxxx ¦
> ¦ Prénom : xxxxx ¦
> ¦ ... ¦
> ¦ ¦
> ¦ ¦
> +---------------------+
> < > NEW SAVE DEL <-- Buttons
>
> Until yet I did it like that (button to display the next record):
> <a href="adresse_edit.php?id=<?= $id+1 ?>">
> <img border="0" src="images/co_avant.png" width="32" height="24"></a>
>
> But this solution do not skip to the next record alphabetikally!..
It would also not work OK after deleting some records, because you
would have gaps in ID order and "$id + 1" could hit such gap.
In my opinion the better way to do "next" and "previous" is
to pass current ID and action information ("previous" or "next" or
some abbreviation of those). Why? Because if you calculate previous
and next ID on the current record page and two users work in the
same time then one of them could delete record which was calculated
as next or previous for the current record of other user and this
way the link would not work.
The best way is to check if there is any previous and next and
use link which passes current ID and action. This doubles the
amount of data retrieved from SQL database, but gives least error
situations (only one is when I display record and see "next" or
"previous" link because there are next/previous records and some
other user deletes all next or previous records, then when I click
the link, there's no record to display).
Hilarion
| |
|
| Hello Hilarion
> It would also not work OK after deleting some records, because you
> would have gaps in ID order and "$id + 1" could hit such gap.
>
You are right. The delete function do not call the same script!
But I will try the mentioned solutions at the top of this thread,
who should give me the next and previous ID in the alphabetic order.
I'm learning PHP with MySQL and I like this forum. Thanks to all who
helped me.
If you like I can send you this script after my holidays to speak
about it.
Regards
Otto
| |
| Hilarion 2005-07-25, 9:28 am |
| >> It would also not work OK after deleting some records, because you
>
> You are right.
> The delete function do not call the same script!
This does not matter.
> But I will try the mentioned solutions at the top of this thread,
> who should give me the next and previous ID in the alphabetic order.
It'll solve problem with ID gaps but will not protect you from
problems when two users acting on the data at the same time.
Let's say that in alphabetical order the record with ID 321 is just
after record with ID 123. Two users have entered the page which
displays the record with ID 123. The page is identical for both
users and contains link:
<a href="adresse_edit.php?id=321">
<img border="0" src="images/co_avant.png" width="32" height="24"></a>
which leads to next record (with ID 321). One user clicks the
link and gets the page which displays the record 321 (the other
user is still watching the 123), and he clicks "DEL" and deletes
the record 321 (does not matter if it's the same script or not).
The second user stops watching 123 and clicks "next" button and
gets to URL "adresse_edit.php?id=321". Unfortunately there's no
321 record, so he gets error message or empty page or something
like that.
One of the solutions is to replace "next" (and "previous") link (which
is now calculated alphabeticaly when the current record is displayed):
<a href="adresse_edit.php?id=<?= $next_id ?>">
<img border="0" src="images/co_avant.png" width="32" height="24"></a>
with link:
<a href="navigate.php?id=<?= $id ?>&action=next">
<img border="0" src="images/co_avant.png" width="32" height="24"></a>
And the "navigate.php" would calculate (alphabetically) the ID
of the next (or previous - according to "action" parameter) record
and redirect to "adresse_edit.php" with proper ID.
> If you like I can send you this script after my holidays to speak
> about it.
OK, but I'll be off the Internet from saturday (for two w s), so
do not expect quick reply :)
Hilarion
| |
|
| Hello Hilarion
> <a href="navigate.php?id=<?= $id ?>&action=next">
> <img border="0" src="images/co_avant.png" width="32" height="24"></a>
> And the "navigate.php" would calculate (alphabetically) the ID
> of the next (or previous - according to "action" parameter) record
> and redirect to "adresse_edit.php" with proper ID.
>
Once again many thanks for your help. I see that I have to learn a lot
about muti-user programming with php-MySql.
Aktually I'm make a very simple program for myself. But I have to think
about it.
OK, now It's time to go on holidays. I will take some book about php
with me.
Regards
Otto
|
|
|
|
|