Tricky Query

Reformed

I have hit a brick wall as far as ideas for a query I need to do. I have an application with an address book type of a function. Users will be looking at a detailed view of a person and a the top of the screen the will have the option (I hope) to click a "previous" or "next" button and see a detailed view of the next record in alphabetical order. Sounds easy enough.

Here's what I need to do. If the user is looking at the record of Mr. Johnson and clicks next, I need to somehow perform a query that looks for the next record alphabetically after Johnson, but my records are not ordered alphabetically, they are ordered by user_id's.

Is there some way to query a database with say a last name and compare it to the next alphabetical value of a last name in the database?

LordShryku

Ahhhh....there are a couple more AZ people on this board! Assuming you're using MySQL, you can use a greater than statement with ORDER BY (name), LIMIT 1 to grab what you want. Using greater than/less than on strings will go alphabetically.

Reformed

Okay, this seems to help solve part of the problem. I didn't know that greater than and less than statements where alphabetical.

I still have the problem of not knowing what the next record will be, so I don't have anything to compare ( < > ) with the current last name.

In order to compare the the current last name I have to somehow figure out what the next or previous name is. Once I figure out how to do that your advice will certainly apply.

LordShryku

Well since you know the current, you can pull the previous and next.

$current = "Whatever";
$getNext = mysql_query("SELECT name FROM table WHERE name > '".$current."' ORDER BY name ASC LIMIT 1");
$getPrevious = mysql_query("SELECT name FROM table WHERE name < '".$current."' ORDER BY name DESC LIMIT 1");

Reformed

You rock! I don't know why that haden't occured to me, but thank you.

LordShryku

No worries. Mark it as resolved. The heat is finally coming down 🙂

Reformed

The suggested solution to this problem works great with last names, but if I have two poeple in my database with the same last name, this method only shows one of the names. Any suggestions on how to modify this query to work with more than one last name that is the same, but with different first names?

maxpup979

Order by lastname,firstname

LordShryku

Still won't work if you're using LIMIT 1. Maybe up the limit and compare the names in PHP