Page 1 of 1

creating a previous and next button

Posted: Tue May 07, 2013 6:46 pm
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

Re: creating a previous and next button

Posted: Tue May 07, 2013 7:54 pm
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.

Re: creating a previous and next button

Posted: Tue May 07, 2013 8:07 pm
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

Re: creating a previous and next button

Posted: Wed May 08, 2013 8:20 am
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.