selext next id in table?

[Jim Michaels]

I have 2 tables. 1 is supposed to contain the id of the 'next' item to be
viewed. from the other table, which has the data and also an id column.

my problem ishow do I make a set of statements that UPDATEs the id (we'll
call it pid) with the next row of the table, advancing it like a cursor,
wrapping back to the beginning of the db?
the DB is a version 4 database with MyISAM tables.

I don't even know if it can be done outside of a tablescan+PHP.
I have some broken code like this:
$q1=mysql_query("SELECT pid FROM idx", $link2);
if ($row1=mysql_fetch_assoc($q1)) {
$a=array();
$q2=mysql_query("SELECT pid FROM cpg133_pictures", $link2);
while ($row2=mysql_fetch_assoc($q2)) {
$a[]=$row2['pid'];
}
$i=array_search($row1['pid'], $a); //search for idx's pid in sea of
coppermine's pids
$n=$a[$i];
$i++; //next picture. sorry. tried random. doesn't work. get same pix
every time.
$i%=count($a);
mysql_query("UPDATE idx SET pid=$a[$i]", $link2) or
die("ERRUV2:".mysql_error());
....
}

Can this be done with queries?

[Jim Michaels]

"Jim Michaels" <NO***************@yahoo.com> wrote in message
news:Gc********************@comcast.com...

I have 2 tables. 1 is supposed to contain the id of the 'next' item to be
viewed. from the other table, which has the data and also an id column.

my problem ishow do I make a set of statements that UPDATEs the id (we'll
call it pid) with the next row of the table, advancing it like a cursor,
wrapping back to the beginning of the db?
the DB is a version 4 database with MyISAM tables.

I don't even know if it can be done outside of a tablescan+PHP.
I have some broken code like this:
$q1=mysql_query("SELECT pid FROM idx", $link2);
if ($row1=mysql_fetch_assoc($q1)) {
$a=array();
$q2=mysql_query("SELECT pid FROM cpg133_pictures", $link2);
while ($row2=mysql_fetch_assoc($q2)) {
$a[]=$row2['pid'];
}
$i=array_search($row1['pid'], $a); //search for idx's pid in sea of
coppermine's pids
$n=$a[$i];
$i++; //next picture. sorry. tried random. doesn't work. get same pix
every time.
$i%=count($a);
mysql_query("UPDATE idx SET pid=$a[$i]", $link2) or
die("ERRUV2:".mysql_error());
...
}

Can this be done with queries?

oops. wrong newsgroup. but if you think you can help, I'm open to
suggestions.

[Marcin Dobrucki]

Jim Michaels wrote:

oops. wrong newsgroup. but if you think you can help, I'm open to
suggestions.

Enable logging on MySQL, and see what queries are actually passed to
the DB? That will give you a clue where things are going wrong.