Get previous/next record from 1 column MySQL/PHP

[planbeta]

Hello!

I have a table with only one column in which contains the names all the different countries (1 name = 1 record). Say I was on a php page for the record United Kingdom and I want to query that database and get the record directly after the 'United Kingdom' and the record directly before the 'United Kingdom', is this possible?

This would be easy if I had another column in the table with a numeric id for each country, but in this situation I can only have the 1 column. Is there some function or clever method that will allow me to do this?

Cheers

Chris

[jatar_k]

what about grabbing the one before and the one after when you do the query. Pretty intensive though.

You can add a column to an existing table using ALTER TABLE [mysql.com]. You can probably add a

country_id int primary key auto_increment

I think it will allow you to do that. I think it will auto populate it too but can't remember.

[planbeta]

Hi Jatar :)

You said:

"what about grabbing the one before and the one after when you do the query. Pretty intensive though. "

How could I do this please?

Many thanks

Chris

[jatar_k]

Well without keys or some point of reference it would be difficult. You would have to select all the results and loop through them all every time.

If not equal to selected country
save as $previous
overwrite $previous until equal
if equal save as $country
loop once more
if isset($country) set $next
end loop

You end up with three vars $previous, $country and $next. It is really ugly though. If you need to go to the last one it could take a while and is definitely a rate limiting factor.

It will work though. There are a few variations to this that you could use. If you sort them alphabetically you could select all values that start with a small range of letters and it would speed it up.

[planbeta]

Thank Jakar :)

I have done what you have recommended and assigned an auto_increment id field, so each country has a unique numerical id associated with it.

And done this:

---------------------------------
$result = mysql_query("SELECT countryId FROM countries WHERE country = 'United Kingdom'",$db);
while ($row = mysql_fetch_array($result))
{
$thisId = $row["countryId"];
}

$lastId = $thisId - 1;
$nextId = $thisId + 1;
---------------------------------

So now I have my id's of the 2 countries I want, but how do I assign them to variables which I can use in the php code?

I have my query

---------------------------------
$result = mysql_query("SELECT country FROM countries WHERE countryId = '$nextId' OR countryId = '$lastId'",$db);
---------------------------------

I have tried several ways to try and assign the 2 results selected to variables,it's easy when there is only one result, but more than 1 and I can't get it to work.

Thanks for your patience and help

Chris

[planbeta]

Ok I think I've figured it out:

Firstly I should be using mysql_fetch_row NOT mysql_fetch_array

I'm not sure if this is the most efficient way of doing it

while ($row = mysql_fetch_row($result))
{
$myvars[] = $row[0];
}

echo $myvars[0]."<br>";
echo $myvars[1]."<br>";

If I understand correct when it, that the $row contains my array, but for some reason I had to transfer this array to a new one then call that to get my variables.

Anyway I think I'm there now, not quite sure why it works this way but thanks for all you help.

Chris

[jatar_k]

I would probably do this for your second part

$lastId = $thisId - 1;
$nextId = $thisId + 1;

$cq = "SELECT country FROM countries WHERE countryId in (" . $nextId . "," . $lastId . ")";
$result = mysql_query($cq);
while ($row = mysql_fetch_array($result)){
echo "<br>",$row['country'];
}