PHP/MySQL: Rebuild primary key field -

problem:

primary key field 'id'

data inserted/updated using replace into command, easy use unfortunately increases 'id' value of record replacing.

so need way rebuild id feild that:

| id | name | |=============== | 21 | deer | | 8 | snow | | 3 | tracks | | 14 | arrow | 

goes to:

| id | name | |=============== | 1 | deer | | 2 | snow | | 3 | tracks | | 4 | arrow | 

and need through php.

current attempts:

<?php $reset = "set @num := 0; update `users` set `id` = @num := (@num+1); alter table `users` auto_increment =1;"; $con = mysql_connect("mysql2.000webhost.com","db_user","password"); if (!$con) { die('could not connect: ' . mysql_error()); } mysql_select_db("db_name", $con); if (!mysql_query($reset,$con)) { die('<h1>nope:</h1>' . mysql_error()); } mysql_close($con); ?> 

and trying:

$reset = "alter table `users` drop `id`; alter table `users` auto_increment = 1; alter table `users` add `id` int unsigned not null auto_increment primary key first;`"; 

also yielded no results.

strangness of all

both $reset commands tried both execute in mysql, reason fail act php.

---

answer

as pointed out answer, @ variables preserved per connection, reasonable run multiple queries:

///trigger multiple queries $nope = '<h1>nope:</h1> '; $res1 = "set @num := 0;"; $res2 = "update `users` set `id` = @num := (@num+1);"; $res3 = "alter table `users` auto_increment =1;"; if (!mysql_query($res1,$con)) die($nope . mysql_error()); if (!mysql_query($res2,$con)) die($nope . mysql_error()); if (!mysql_query($res3,$con)) die($nope . mysql_error()); mysql_close($con); 

mysql_* not support running multiple queries. have run them separately