[RESOLVED] mysql smart auto_increment

Hi!

I wonder if it is possible to make some kind of auto_increment in php that first changes all the id values of the rows so that they are increasing 1-x without jumping over any numbers.. I mean if there are 5 rows in a database and the id values are

1
2
5
6
7

because I've removed 3 and 4.. I'd want a php script to change those to

1
2
3
4
5

when I put in a new object, and then put in the id 6 for the new object. So it would result in

1
2
3
4
5
6

instead of

1
2
5
6
7
12

Which is a pretty irritating thing that the mysql auto_increment does if you remove some stuff and put some stuff in, etc..

Could someone help me with the script for that?

Cheers,
Artheus

is the auto increment field set as the primary key?

If so, I'm not sure you can re-use primary keys.

plus mySql stores the next auto increment in the table data structure. auto-increment=14 or something like that if memory serves me correct.

so even if you change the values of the field to correctly fill in the blanks, mysql will add the next row at whatever the next auto-increment value of the table is.

Does that make sense?

perhaps if possible moving to postgreSQL would help solve the problem as it handles the auto increment primary key differently.

Yes that makes sense.. And I know all that.. I am not after reusing a primary key.. And I am not after using the actual auto_increment inside of mysql.. What I am after is to make a php function which first changes the

1
2
5
6
7

id's to become

1
2
3
4
5

And then count the rows, which are 5, add one to it.. which becomes 6.. and then add a id manually using an INPUT Query.. and then it's

1
2
3
4
5
6

Ok?

ahh .. well in that case a simple for loop using count() would help you

PHP Code:

---

//
$entries = //array of data rows sorted by that id field;
$counter = 1;
for($i = 0; $i < count($entries); $i++){
      $sql = "UPDATE table SET id='" . $counter . "' WHERE id='" . $entries[$i]['idField'] . "' LIMIT 1";
      $mysql_query($sql); 
     $counter++;

} 


---

Should get the job done.

So if I just make up a way to write that.. so maby I can elucidate what I mean...

*Not a real PHP script, just a way to try to elucidate what I mean*

Code:

---

$link = mysql_connect("localhost", "username", "password");
mysql_select_db("database", $link) or die(mysql_error());

$i = 1;

$result = mysql_query("SELECT * FROM table") or die(mysql_error());
while($row = mysql_fetch_array($result)) {

$row['id'] = $i; //Here I want to update the value of the current row to the value of $i
++$i;

}

---

that will work, but it won't actually change the database values, just the value within the resulting array.

you will also want to make sure you ORDER BY the correct field

SELECT * FROM table ORDER BY id

this will ensure that at the end row 1 is still row 1

well.. I solved it xD

Code:

---

$i = 1;

$result = mysql_query("SELECT * FROM table") or die(mysql_error());

while($row = mysql_fetch_array($result)) {

        $id = $row['id'];
       
        mysql_query("UPDATE table SET id=$i WHERE id=$id");
        ++$i;

}

---

Cool, good job.

Hope I helped and didn't hinder :)

Yeah ^^ I know that too.. :P Well thanks alot!

And Thanks for the tip of ORDER BY!

Cheers,
Artheus

You helped!
Soo, Thanks!

OUCH! You intend to call a query for every row?

You could do this all in one query - update multiple rows with one query.

I suppose it depends if this is a one time job or something to be done regularly though.

well not really.. It's for a Image gallery, which will never hold more than abour a 30 pictures.. so.. that's not really a problem I will encounter..
But a great tip! Which I will use, for a more effective code!

Thanks!

--

Frankly, if you need or depend on the primary key to be sequentially numbered with no gaps, there is probably a flaw in your design logic. The only purpose of that field should be to provide a unique identifier. The moment you start renumbering it, you start breaking any other table that references that table (now or in future enhancements), or else you have to cascade any renumbering into all such referencing tables (and hope like heck that you don't screw it up).

Thanks for that. But the thing is that it was just a simple little thing for a image gallery. And that was the easiest way of making it. Or something. I don't really remember. But I see what you mean. And I agree on that using the primary key otherwise than a unique identifier is not very smart. I don't use that method anymore.

But thanks!

Cheers,
Artheus