Next and previous MySQL row based on name

Question

I have a staff details table. I would like to create a Next / Previous link based on an individual last name. Since the staff was not added alphabetically, selecting the next or previous row based on its identifier does not work.

This is a massive table - the corresponding fields are id, name_l and name_f. I would like to order by name_l, last name of individuals.

How do I solve this problem?

Thank!

Edit This will be used on the staff details page, the result will generate links to the next / previous record in the database (sorted by last name) based on the current row. For example, if I look at Joe Hammer, the Next link will link to Frank Ingram.

Final code

Thanks to Daniel, here's what I finally got to work:

First, I set the increment to 0: $ i = 0 . Then, sorting through the records with the while loop , I increased this by 1 = $ i ++ . Then I made a link to the details page for this particular entry:

<a href="details.php?id=<?php echo $member['id'];?>&amp;row=<?php echo $i;?>">Details</a>

On the Details page, I used the following SQL to select the following entry:

$row = $_GET['row'];
$getNext = mysql_query("SELECT * FROM members ORDER BY name_l, id LIMIT ".$row.", 1");
$next = mysql_fetch_assoc($getNext);
$nextLink = $row + 1;

Finally, the link:

<a href="member_details.php?id=<?php echo $next['id'];?>&amp;row=<?php echo $nextLink;?>"><?php echo $next['name_l'] . ", " . $next['name_f'];?></a>

Answer 1

First of all, make sure your column is name_lindexed. Then you can simply use the sentences ORDER BYand LIMITas follows:

SELECT * FROM personnel ORDER BY name_l, id LIMIT 0, 1;

0 LIMIT, . LIMIT 1, 1, , LIMIT 2, 1 ..

name_l, CREATE INDEX:

CREATE INDEX ix_index_name ON personnel (name_l);

:

CREATE TABLE personnel (
   id int not null primary key, 
   name_l varchar(10), 
   name_f varchar(10)
);

CREATE INDEX ix_last_name_index ON personnel (name_l);

INSERT INTO personnel VALUES (1, 'Pacino', 'Al');
INSERT INTO personnel VALUES (2, 'Nicholson', 'Jack');
INSERT INTO personnel VALUES (3, 'De Niro', 'Robert');
INSERT INTO personnel VALUES (4, 'Newman', 'Paul');
INSERT INTO personnel VALUES (5, 'Duvall', 'Robert');

:

SELECT * FROM personnel ORDER BY name_l, id LIMIT 0, 1;
+----+---------+--------+
| id | name_l  | name_f |
+----+---------+--------+
|  3 | De Niro | Robert |
+----+---------+--------+
1 row in set (0.00 sec)

SELECT * FROM personnel ORDER BY name_l, id LIMIT 1, 1;
+----+--------+--------+
| id | name_l | name_f |
+----+--------+--------+
|  5 | Duvall | Robert |
+----+--------+--------+
1 row in set (0.00 sec)

SELECT * FROM personnel ORDER BY name_l, id LIMIT 2, 1;
+----+--------+--------+
| id | name_l | name_f |
+----+--------+--------+
|  4 | Newman | Paul   |
+----+--------+--------+
1 row in set (0.00 sec)

---

1st UPDATE: ( )

, , , , , , , , .. x x , , , .

, , .

, , . $index = 0 ... LIMIT $index, 1. , $index 1, , $index 1.

, , , ( ), (php). , :

+----+-----------+--------+
| id | name_l    | name_f |
+----+-----------+--------+  // [Hidden info]
|  3 | De Niro   | Robert |  // Row 0 
|  5 | Duvall    | Robert |  // Row 1
|  4 | Newman    | Paul   |  // Row 2
|  2 | Nicholson | Jack   |  // Row 3
|  1 | Pacino    | Al     |  // Row 4
+----+-----------+--------+

, Newman Paul, row=2 , . , , , Newman Paul (row=2). x LIMIT x, 1 row - 1 row + 1 :

-- Previous 
SELECT * FROM personnel ORDER BY name_l, id LIMIT 1, 1;
+----+--------+--------+
| id | name_l | name_f |
+----+--------+--------+
|  5 | Duvall | Robert |
+----+--------+--------+
1 row in set (0.00 sec)


-- Next 
SELECT * FROM personnel ORDER BY name_l, id LIMIT 3, 1;
+----+-----------+--------+
| id | name_l    | name_f |
+----+-----------+--------+
|  2 | Nicholson | Jack   |
+----+-----------+--------+
1 row in set (0.00 sec)

---

2nd UPDATE:

, MySQL, , . , , , .

, .

:

SELECT p.id, p.name_l, p.name_f, o.record_number
FROM   personnel p
JOIN   (
        SELECT    id,
                  @row := @row + 1 AS record_number
        FROM      personnel
        JOIN      (SELECT @row := -1) r
        ORDER BY  name_l, id
       ) o ON (o.id = p.id)
WHERE  p.name_l = 'Nicholson' AND p.name_f = 'Jack';

:

+----+-----------+--------+---------------+
| id | name_l    | name_f | record_number |
+----+-----------+--------+---------------+
|  2 | Nicholson | Jack   |             3 |
+----+-----------+--------+---------------+
1 row in set (0.00 sec)

, WHERE p.id = 2, , p.name_l = 'Nicholson' AND p.name_f = 'Jack'.

record_number, 3, , LIMIT 2, 1 LIMIT 4, 1 . :

:

SELECT * FROM personnel ORDER BY name_l, id LIMIT 2, 1;
+----+--------+--------+
| id | name_l | name_f |
+----+--------+--------+
|  4 | Newman | Paul   |
+----+--------+--------+
1 row in set (0.00 sec)

:

SELECT * FROM personnel ORDER BY name_l, id LIMIT 4, 1;
+----+--------+--------+
| id | name_l | name_f |
+----+--------+--------+
|  1 | Pacino | Al     |
+----+--------+--------+
1 row in set (0.00 sec)

Answer 2

SQL SELECT:

SELECT * FROM tblPersonnel ORDER BY name_l ASC

: http://w3schools.com/sql/sql_orderby.asp