This is the example table:
Column | 1st record | 2nd record | 3rd record | 4th record | etc<br />
id (primary) | 1 | 5 | 8 | 12 | etc<br />
name | name 1 | name 2 | name 3 | name 4 | etc<br />
date | date 1 | date 2 | date 3 | date 4 | etc<br />
callValue (unique) | val1 | val2 | val3 | val4 | etc
I select one row that is the data to show (for example: row with callValue: val3). But I cannot find a solution for this:
I need to select previous and next row. So, in this example, I need to get data from rows callValue: val4 and callValue: val2, or id: 5 and id: 12.
It cannot be done with id
=id
+-1 because id
doesn't have to be continuous because of deleting rows.
To select rows using selection symbols for character or graphic data, use the LIKE keyword in a WHERE clause, and the underscore and percent sign as selection symbols. You can create multiple row conditions, and use the AND, OR, or IN keywords to connect the conditions.
You can use UNION to get the previous and next record in MySQL. Insert some records in the table using insert command. Display all records from the table using select statement.
Try this:
select * from test where callValue = 'val3'
union all
(select * from test where callValue < 'val3' order by id desc limit 1)
union all
(select * from test where callValue > 'val3' order by id asc limit 1)
or
select * from test where id = 8
union all
(select * from test where id < 8 order by id desc limit 1)
union all
(select * from test where id > 8 order by id asc limit 1)
Once you have the id 8
, you should be able to do a variation on:
select * from mytable
where id < 8
order by id desc
limit 1
and:
select * from mytable
where id > 8
order by id asc
limit 1
for the previous and next record.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With