creating a previous and next button

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Da_Elf
Forum Commoner
Posts: 81
Joined: Mon Dec 29, 2008 12:31 pm

creating a previous and next button

Post by Da_Elf »

SOLUTION

Code: Select all

"SELECT CONCAT(fname,' ',sname) as meow FROM table WHERE CONCAT(fname,' ',sname) < '$getfullname' ORDER BY meow DESC LIMIT 1 "


this would be easy if i was doing it via the id number but i want to do it via two fields.
i have my databse setup with the first_name and last_name seperated. when i create a list of the names for a select i use SELECT * FROM table ORDER BY fname sname ASC.
that works fine. now i want that when ive for someone selected and ive got next to the select input i want to have a previous and next button which will look for the previous name thats less than the current one and a next name thats more.
lets say the names i have are "Adam Family, Bob Barker, Peter Parker, Peter Pipper, Thor Thunder and Zena Princess"
if peter parker is selected i want previous to say bob barker and next to say peter pipper. NOTE: their are not in that order in the database

i found this on a site for doing a previous and next search but thats doing it for only

Code: Select all

select * from mytable where id < 8 order by id desc limit 1
i tried to convert it to my needs

Code: Select all

select * from table WHERE (first_name < '$firstname') AND (last_name < '$lastname') ORDER BY first_name ,last_name desc limit 1
but this didnt seem to work. i know the WHERE clause is robust but i cant figure out the right wording
Last edited by Da_Elf on Tue May 07, 2013 9:16 pm, edited 1 time in total.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: creating a previous and next button

Post by requinix »

To put it confusingly, the new records are either (a) most definitely after the given name, or (b) only just barely after the name because the first part matches while the second part is after.

Code: Select all

WHERE
    first_name < '$firstname' /* definitely after */
    OR (first_name = '$firstname' /* this primary part matches */
        AND last_name < '$lastname' /* this secondary part is after */
    )
It's like comparing dates: June 20th is definitely after January 1st because June>January, and it's also after June 10th because while June=June, 20>10.
Da_Elf
Forum Commoner
Posts: 81
Joined: Mon Dec 29, 2008 12:31 pm

Re: creating a previous and next button

Post by Da_Elf »

that seems to only give the first name in the group.
maybe the words first and last are messing up the understanding of it. put it this way

Code: Select all

select * from table WHERE (given_name < '$gname') AND (surname < '$sname') ORDER BY given_name ,surname desc limit 1
so in this case $gname = 'Peter';$sname = 'Parker';
in the list if peter parker was one word then bob barker is < peter parker and peter piper > peter parker
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: creating a previous and next button

Post by Christopher »

Da_Elf wrote: that works fine. now i want that when ive for someone selected and ive got next to the select input i want to have a previous and next button which will look for the previous name thats less than the current one and a next name thats more.
lets say the names i have are "Adam Family, Bob Barker, Peter Parker, Peter Pipper, Thor Thunder and Zena Princess"
if peter parker is selected i want previous to say bob barker and next to say peter pipper. NOTE: their are not in that order in the database
Records are not in any real order in a database (other than maybe the order they were inserted). You need to use ORDER BY to put records in an order. Use ORDER BY to sort the records in alphabetical order and use LIMIT to specify the one record you want.

Code: Select all

SELECT * FROM table ORDER BY surname, given_name  LIMIT $position, 1;
Pass the $position you want for the next and previous records as a parameter in the URL. That is how next/previous pagination works.
(#10850)
Post Reply