SEO

#1 (**permalink**) 02-28-2008, 07:37 AM

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");
if ($row1=mysql_fetch_assoc($q1)) {
$a=array();
$q2=mysql_query("SELECT pid FROM pictures");
while ($row2=mysql_fetch_assoc($q2)) {
$a[]=$row2['pid'];
}
$i=array_search($row1['pid'], $a); //search for idx's pid in sea of
table'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?

#2 (**permalink**) 02-28-2008, 07:37 AM

Jim Michaels wrote:
> 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");
> if ($row1=mysql_fetch_assoc($q1)) {
> $a=array();
> $q2=mysql_query("SELECT pid FROM pictures");
> while ($row2=mysql_fetch_assoc($q2)) {
> $a[]=$row2['pid'];
> }
> $i=array_search($row1['pid'], $a); //search for idx's pid in sea of
> table'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?
>
>

Assuming the column name is pid, the current record has been selected into
$curpid and the rows are in ascending (although not necessarily consecutive) order:

mysql_query("SELECT pid FROM pictures WHERE pid > $curpid LIMIT 1);

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

#3 (**permalink**) 02-28-2008, 07:37 AM

On Sat, 22 Apr 2006 23:29:16 -0500, Jerry Stuckle wrote:
> Jim Michaels wrote:
>> 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"); if ($row1=mysql_fetch_assoc($q1)) {
>> $a=array(); $q2=mysql_query("SELECT pid FROM pictures");
>> while ($row2=mysql_fetch_assoc($q2)) { $a[]=$row2['pid']; }
>> $i=array_search($row1['pid'], $a); //search for idx's pid
>> in sea of table'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?
>>
>>

> Assuming the column name is pid, the current record has been selected
> into $curpid and the rows are in ascending (although not necessarily
> consecutive) order:
>
> mysql_query("SELECT pid FROM pictures WHERE pid > $curpid LIMIT 1);

Just as a outside item, it's fairly simple to implement a linked list
using a database as storage, in which case you can simply create a
column of next_pid that gives the pid of the next picture in the list.
Reasons for doing this might be to allow the images to be reordered
without breaking outside-the-database references to a particular image,
or faffing about trying to keep child records in other tables updated in
brittle code (since MyISAM is being used instead of InnoDB and table
constraints that could cascade pid updates).

--
100. Finally, to keep my subjects permanently locked in a mindless trance, I
will provide each of them with free unlimited Internet access.
--Peter Anspach's list of things to do as an Evil Overlord

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

select next id in table?