mysql autoincrement question

sol01

Ok say I have added entries 1 - 15 in my database.

10
11
12
13
14
15

when I delete 12 my database goes to

10
11
13
14
15

Is there a way to have the 13 turn into 12 and the 14 to 13 and the 15 to 14??

so it will be 10 11 12 13 14 ---??

Thanks in advance for any help.

tasistro

Well I should warn you that autoincrement fields are usually used as primary keys. If your field is a primary key, beware. It is of bad karma to mess around with primary keys. Data consistency problems ahead.

Having said that

GIVEN ID is your autoincrement field

SQL1 : DELETE FROM %table% WHERE ID=%row_to_delete%;

SQL2 UPDATE %table% SET ID=ID-1 WHERE ID>%row_to_delte%

Saludos
Gerardo

sol01

ok, see my problem is I have the following database table

---id---img---thumb---descript---
1 1.jpg 1.jpg my dog pat
2 2.jpg 2.jpg my new porsche
3 3.jpg 3.jpg Dads new house

Its an image gallery and anyhow I have a script that grabs a random image based on the amount of entries in the database.

$result = mysql_query( "SELECT * FROM photo_gallery" );
$num_rows = mysql_num_rows( $result );

The above gives me the amount of rows......now if I am never going to remove/edit images/entries then all is well.

My script then does this

srand ((double) microtime() * 1000000);
$a = rand(1,$num_rows);
print " <a href='gallery/index.php?id=$a&thumb=1' style='text-decoration: none'><img border=0 src = 'gallery/thumbs/$a.jpg'> </a>";

However say I remove entry #2 in my database then it will look like this:

---id---img---thumb---descript---
1 1.jpg 1.jpg my dog pat
3 3.jpg 3.jpg Dads new house

So the above script will try and grab entry 2 -- however it now does not exist. I hope this is understandable ... LOL!!! Anyone got some ideas for a work around?

Thanks for all your help people!!!

tasistro

Well if you already have the number of rows on the table then you can do something like this

SELECT * FROM %table% LIMIT %random_number%, 1

where random number is in the range [0,numrows-1] your $a

I'm considering the fact that

$num_row is given by something like
SQL : SELECT count(ID) as numrows FROM %table%
// after the mysql query $result hold the info
$sql_result_count=mysql_fetch_array($result);
$num_row=$sql_result_count["numrows"];

// your SQL takes up too many resources
// dont use this
$result = mysql_query( "SELECT * FROM photo_gallery" );
$num_rows = mysql_num_rows( $result );

then later
your post would be like

index.php?id=$a&num_rows=$num_rows&thumb=1

"SELECT * FROM %table% LIMIT $num_rows,1"

Mysql will automatically select the row no matter the ID

Saludos
Gerardo

sol01

Thats a very good idea. I like it a lot. I did want to interject another factor. See what I have been doing is having 3 random images at once.....check out my code, you should be able to see what I am doing.

srand ((double) microtime() * 1000000);
$a = rand(1,$num_rows);
print " <a href='gallery/index.php?id=$a&thumb=1' style='text-decoration: none'><img border=0 src = 'gallery/thumbs/$a.jpg'> </a>";

$b = rand(1,$num_rows);

while ($b == $a)
{
$b = rand(1,$num_rows);
}
print " <a href='gallery/index.php?id=$b&thumb=1' style='text-decoration: none'><img border=0 src = 'gallery/thumbs/$b.jpg'> </a>";

$c = rand(1,$num_rows);

while ($c == $b || $c == $a)
{
$c = rand(1,$num_rows);
}
print " <a href='gallery/index.php?id=$c&thumb=1' style='text-decoration: none'><img border=0 src = 'gallery/thumbs/$c.jpg'> </a>";

This is nice cause I have not had to do much database lookup......this makes it a fair bit faster.

However it does run into problems with the database. Do you think it would be fairly streight forward to do the above through the mysql random feature.....I would still need to do the while loops so that I dont get the same picture more then once. Any ideas???

tasistro

Yea sure

$a=rand(0,$numrows-1);
$b=rand(0,$numrows-2);
$c=rand(0,$numrows-3);

SQL for image a

SELECT * FROM %table% LIMIT $a,1

SQL for image b

SELECT * FROM %table% WHERE ID!=%ID image a% LIMIT $b,1

SQL for image c

SELECT * FROM %table% WHERE ID!=%ID image a% AND ID!=$ID image b% LIMIT $c,1

That should do it.

Saludos
Gerardo

sol01

I created the following however it seems to use quite a bit of SQL resources. Can you tell me if the below is why I get the "Fatal error: Maximum execution time of 30 seconds exceeded " everytime I load the page this script is on?? Thanks for the help!!

$result = mysql_query( "SELECT * FROM photo_gallery order by rand() limit 1" );

while ( $a_row = mysql_fetch_array( $result ))
{
$a = $a_row[fldimaget];
$ab = $a_row[flduid];
}

print " <a href='gallery/index.php?id=$ab&thumb=1' style='text-decoration: none'><img border=0 src = 'gallery/thumbs/$a'> </a>";

$result = mysql_query( "SELECT * FROM photo_gallery order by rand() limit 1" );

$bc = $ab;

while ($bc == $ab)
{
while ( $a_row = mysql_fetch_array( $result ))
{
$b = $a_row[fldimaget];
$bc = $a_row[flduid];
}
}

print " <a href='gallery/index.php?id=$bc&thumb=1' style='text-decoration: none'><img border=0 src = 'gallery/thumbs/$b'> </a>";

$result = mysql_query( "SELECT * FROM photo_gallery order by rand() limit 1" );

$cd = $bc;

while ($cd == $bc || $cd == $ab)
{
while ( $a_row = mysql_fetch_array( $result ))
{
$c = $a_row[fldimaget];
$cd = $a_row[flduid];
}
}

print " <a href='gallery/index.php?id=$cd&thumb=1' style='text-decoration: none'><img border=0 src = 'gallery/thumbs/$c'> </a>";

mysql_close ($link);

tasistro

Order by will be slower than a limit to some record, plus your checking for repeats by yourself. Let MySQL do that by itself in the way I told you.

Plus after the second SQL you go into a while loop that can lock. Basically if the row returned by the second sql is the same as the first row $ab and $bc will always evaluate to equal. I've seen the rand() thing in the php man pages and I don't trust it too much. On few images it gave the same result twice. Which would explain your lock.

Saludos
Gerardo

sol01

Well I couldn't quite figure what you were getting at with what you wrote so I used my understanding of what you wrote and came up with the following....it checks to make sure the number is not being used then checks to see if it can find it in the database.....if it can find the entry in the database then it uses it. This works for $b and $c ... I am still working on the $a factor.....any comments???? - TX

$getamount = mysql_query( "SELECT * FROM photo_gallery" );//grabs the amount of entries
$num_rows = mysql_num_rows( $getamount);//variable that contains the amount of total entries

srand ((double) microtime() * 1000000);

$a=rand(1,$num_rows);

while ($b == "" || $b < 1 || $b == $a)
{
$b=rand(1,$num_rows);
$que = "SELECT * FROM photo_gallery where flduid = '$b'";
$res = mysql_query($que);
$rows = mysql_num_rows($res);
//print $rows;
if ($rows == 0)
{
$b = "";
}
}

while ($c == "" || $c < 0 || $c == $b || $c == $a)
{
$c=rand(1,$num_rows);
$que = "SELECT * FROM photo_gallery where flduid = '$c'";
$res = mysql_query($que);
$rows = mysql_num_rows($res);
//print $rows;
if ($rows == 0)
{
$c = "";
}
}

sol01

OK thanks Gerardo Tasistro for your tips. They were a great help. For everyones info here is the final script I did that works quite nicely. Very fast and efficiant....ofcourse it could prob use some tweaking and I will get to that later. Here it is folks.

####

$getamount = mysql_query( "SELECT * FROM photo_gallery" );//grabs the amount of entries
$num_rows = mysql_num_rows( $getamount);//variable that contains the amount of total entries

srand ((double) microtime() * 1000000);

$a = "0";
if ($a == "0")
{
while ($a == "0")
{
$a=rand(1,$num_rows);
$que = "SELECT FROM photo_gallery where flduid = '$a'";
$res = mysql_query($que);
$rows = mysql_num_rows($res);
if ($rows == "0")
{
$a = "0";
}
}
$result = mysql_query( "SELECT FROM photo_gallery where flduid = '$a'" );
while ( $a_row = mysql_fetch_array( $result ))
{
$ida = $a_row[fldimaget];
$ab = $a_row[flduid];
}
print " <a href='gallery/index.php?id=$ab&thumb=1' style='text-decoration: none'><img border=0 src = 'gallery/thumbs/$ida'> </a>";
}

if ($b == "" || $b < 1 || $b == $a)
{
while ($b == "" || $b < 1 || $b == $a)
{
$b=rand(1,$num_rows);
$que = "SELECT FROM photo_gallery where flduid = '$b'";
$res = mysql_query($que);
$rows = mysql_num_rows($res);
//print $rows;
if ($rows == "0")
{
$b = "";
}
}
$result = mysql_query( "SELECT FROM photo_gallery where flduid = '$b'" );
while ( $a_row = mysql_fetch_array( $result ))
{
$idb = $a_row[fldimaget];
$bc = $a_row[flduid];
}
print " <a href='gallery/index.php?id=$bc&thumb=1' style='text-decoration: none'><img border=0 src = 'gallery/thumbs/$idb'> </a>";
}

if ($c == "" || $c == $b || $c == $a)
{
while ($c == "" || $c < 0 || $c == $b || $c == $a)
{
$c=rand(1,$num_rows);
$que = "SELECT FROM photo_gallery where flduid = '$c'";
$res = mysql_query($que);
$rows = mysql_num_rows($res);
//rint $rows;
if ($rows == "0")
{
$c = "";
}
}
$result = mysql_query( "SELECT FROM photo_gallery where flduid = '$c'" );
while ( $a_row = mysql_fetch_array( $result ))
{
$idc = $a_row[fldimaget];
$cd = $a_row[flduid];
}
print " <a href='gallery/index.php?id=$cd&thumb=1' style='text-decoration: none'><img border=0 src = 'gallery/thumbs/$idc'> </a>";
}

####