A interesting DB / PHP problem need help

[havoc]

I have a db with serveral 100 records , what i am trying to do is have navigation based on the records..

eg (this isnt the code this is to get the idea of what i use the varibles on)

$next = $id+1;
$prev = $id-1;

$prevquery="SELECT record FROM table WHERE id = $prev";

Index ¦ $next ¦ $prev

the thing is when a record is DELETED and lets say $prev doesnt exist this obviously causes a problem.

is there a easy way of getting the next "available" record from mysql in php?

[RussellC]

you can just say

$qry = "SELECT record FROM table";
$qry2 = @mysql_query($qry);
while ($row = @mysql_fetch_array($qry2)) {
$record = $row["record"];
echo("$record<br />");
}

that will list all of the records you can format how they are displayed.

[RussellC]

Wait after looking at you problem I might have misunderstood you..let me know if that e.g. helped you.

[havoc]

I wrote a fix but im 2000% sure this could be optimised maybe suggest? I setup up 10 tries and it fails

<mysql query here>

$PrevCheck = mysql_num_rows($prevname);

while($PrevCheck == 0)
{
if ($fail < 10)
{
$prev--;
$prevquery="SELECT Record FROM Table WHERE id = $prev";
$prevname = mysql_query($prevquery);
$PrevCheck = mysql_num_rows($prevname);
$fail++;
}
else
{
$PrevCheck++;
$prevresult = "No Previous";
$prevexist=No;
}
}

[timster]

As your db ages, I imagine you'll get a lot of gaps. Maybe you want to just ask the db for the right record at the start, instead of coping with lots of fails.

$sql = "SELECT Record FROM Table where id >= $id order by Record";
# (You may want to throw in a "limit 2" at the end if that's your style)

# The second row is always the one you want (except if you're at last rec already)

And of course

$sql = "SELECT Record FROM Tablle where id <= $id order by Record DESC";'

# Again, the 2nd row has the goods

You can also extend this to sort your records as desired.

[jatar_k]

you could also add LIMIT 1 to timster's examples giving you only the next or previous record alone.

[timster]

jatar_k is right. The two rows are not necessary. But you would need to change:

>= $id
to
> $id

and likewise for <= to <.

(I'd better run get some more coffee...)

[havoc]

Thanks guys i knew someone would know .. its taked .003 seconds off execution which is really cool :)

i used the SELECT record, id FROM table WHERE id < $id ORDER BY id DESC limit 1

I am still not familar with mysql querying and optimising php code .. im getting there though . You guys are always a good help :)