auto_incrementing problem

Schwalbach

Ok guys, here's my prob:

I have a flash file that uses php to read a mysql database. Well, this flash file needs each number to be in order to display my comments right(that's whats in the database) so the id needs to be 1 to be in the first box. I can do that fine.

The problem is I want an admin panel. Well, If I decide to delete a comment, now the auto_increment is messed up. Is there any way to make mysql reset it's order and go 1-10 or 1-20 in order, even if I delete entry like 15.

See, if I delete entry 15, it goes 1-20, but it just skips 15. I want it to go 1-19 then, and keep the 15, so it is like having a mysql db that repairs itself so it is always in order of 1-Infinity....

Any ideas?

Sxooter

Fix the flash file? Or can you do that?

Schwalbach

lol...There is nothing wrong with the flash file. All it does is show the results. I need the mysql db to be in order no matter what, even if something gets deleted. Here is what I am doing...

Php is telling flash how many rows that the db has. Flash is duplicating its movieclip that many times. Each time it is duplicated it sets a number to the clip...so if we have 8 rows in the db, flash duplicates and sets:

for comment 1: num = 1
for comment 2: num = 2

So I need it to be read the right way by php...:

$num = $_POST['num'];
$result = mysql_query("SELECT comments, Author, Email FROM comments".$number." WHERE ID='$num'") or die('Sorry, there was an error: ' . mysql_error());
 while ( $row = mysql_fetch_array($result) ) {
 echo($row['comments']);
 echo($row['Author']);
 echo($row['Email']);
}

Sxooter

If the flash file expects an autoincrement field to always be in order, it's broken. The purpose of autoincrement fields is to provide a permanent unique key for a field. Filling in holes is not its job.

The easiest way to work around this is to just ignore the autoinc field and programmatically generate it in PHP.

I.e. select * from table order by field1 limit 10,0;

then as you loop through the data, just create your own int an increment it as you go along.

Sxooter

Note that your other option is to write something that will go through the autoinc field and fill in the holes for you. It's not the best way, as it's considered bad form to renumber a primary key / foreign, which an autoinc almost always is.

Schwalbach

Ok, It's broken then...anyways, I have a way to fix it, but not sure..

First, I am going to select the db. Then say the flash has exported num = 2

Well, how can I select the second entry on the database, then display that?

I am thinking

mysql_fetch_array

But what would I do, something like

$row['2']

Like so I could select the second entry in the db?

Thanks,
Chase

Sxooter

Something like:

<?php
$offset = 0;
$limit = 10;
$query = "select * from table order by field limit $offset,$limit";
$res = mysql_query($query);
if mysql_num_rows($res)<$limit $count = mysql_num_rows($res);
else $count = $max;
for ($i=0;$i<$count;$i++){
  $row = mysql_fetch_array($res,$i);
  print $i+$offset;
  print " ".$row['moreinfo']."\n";
}
?>

Something like that.

Schwalbach

Hey,

I don't understand this part:

for ($i=0;$i<$count;$i++){

I am not searching through the variables or anything like this. Each seperate movieclip will give php a number.

$num = $_POST['num']

That might be say 3 or something. I just then want to be able to get the info from row $num. I don't need to go through all them or anything like that.

Sxooter

But, the numbers coming out of the database aren't in order. We're trying to give the application a set of numbers that are in order.

OK, I just reread what you were doing.

Just use the number like this:

$number = $_POST['num'];
$query = "select * from table order by field limit $number,1"