• Registered: January 4, 2009
  • Posts: 11

Topic: How to turn back the index counter (auto_increment)

I made these scripts and runs well! But my problem is when I Deleted the last records that is greater than 5, the number or counter still continue to increment instead of following the last number in the id field.

I mean like this:

http://i417.photobucket.com/albums/pp257/madmhan84/result.jpg


I used this code:

<html>
<head></head>
<body>

<form method = "post">
Country: <input type = "text" name = "country"><br><br>
Animal: <input type = "text" name = "animal"><br><br>
<input type = "submit" name = "submit">
</form>

<?php
if (isset($_POST['submit']))
{
$country = $_POST['country'];
$animal = $_POST['animal'];

$db = 'testdb';
$connection = mysql_connect('localhost','root','test');
mysql_select_db($db);

mysql_query("INSERT INTO symbols (country, animal) VALUES('$country', '$animal')");


$query = "SELECT * FROM symbols ORDER BY id";
$result = mysql_query($query);

if (mysql_num_rows($result) >0)
{
echo "<table border = 1 cellpadding = 5>";
while ($row = mysql_fetch_row($result))
{
echo "<tr>";
echo "<td>" .$row[0]. "</td>";
echo "<td>" .$row[1]. "</td>";
echo "<td>" .$row[2]. "</td>";
echo "</tr>";
}
echo "</table>";
}
echo "<br>";
echo mysql_num_rows($result) . " Total No. of Records";

mysql_free_result($result);
mysql_close($connection);
}
?>


</body>
</html>


Thanks in advanced! big_smile

Last edited by madmhan84 (August 19, 2009 10:42 pm)

Vote up Vote down

  • From: Nepal
  • Registered: August 17, 2009
  • Posts: 23

Re: How to turn back the index counter (auto_increment)

Hi madmhan84,
You can do this by incrementing a value.

<html>
<head></head>
<body>

<form method = "post">
Country: <input type = "text" name = "country"><br><br>
Animal: <input type = "text" name = "animal"><br><br>
<input type = "submit" name = "submit">
</form>

<?php
if (isset($_POST['submit']))
{
$country = $_POST['country'];
$animal = $_POST['animal'];

$db = 'testdb';
$connection = mysql_connect('localhost','root','test');
mysql_select_db($db);

mysql_query("INSERT INTO symbols (country, animal) VALUES('$country', '$animal')");

$query = "SELECT * FROM symbols ORDER BY id";
$result = mysql_query($query);

if (mysql_num_rows($result) >0)
{
echo "<table border = 1 cellpadding = 5>";
while ($row = mysql_fetch_row($result))
{
$count+=1;
echo "<tr>";
echo "<td>" .$count. "</td>";
echo "<td>" .$row[1]. "</td>";
echo "<td>" .$row[2]. "</td>";
echo "</tr>";
}
echo "</table>";
}
echo "<br>";
echo mysql_num_rows($result) . " Total No. of Records";

mysql_free_result($result);
mysql_close($connection);
}
?>

</body>
</html>

Vote up Vote down

  • Registered: January 4, 2009
  • Posts: 11

Re: How to turn back the index counter (auto_increment)

sir bishwadeep,

thanks for the great help!

Vote up Vote down

  • Gender: Male
  • From: Chico/Sacramento, CA
  • Registered: December 19, 2008
  • Posts: 2,810
  • Reputation: 14

Re: How to turn back the index counter (auto_increment)

Just to be clear, the code above doesn't make any change to the database itself -- it just adds a counter that gets incremented when the country and animal values are displayed.

Benjamin Falk | Falken Creative : Twitter
Skills: Photoshop, Illustrator, HTML, CSS, jQuery, PHP and CodeIgniter

Vote up Vote down

  • Registered: January 4, 2009
  • Posts: 11

Re: How to turn back the index counter (auto_increment)

falkencreative wrote:

Just to be clear, the code above doesn't make any change to the database itself -- it just adds a counter that gets incremented when the country and animal values are displayed.


Is there a way to reset the id field to follow the last number of the record instead of using the $count += 1;

How?

I'd try:
TRUNCATE table symbols where id > 5;
but it did'nt work!

Thanks a lot!

Last edited by madmhan84 (August 29, 2009 8:33 pm)

Vote up Vote down

  • Gender: Male
  • From: Chico/Sacramento, CA
  • Registered: December 19, 2008
  • Posts: 2,810
  • Reputation: 14

Re: How to turn back the index counter (auto_increment)

Even if that did work, it wouldn't reset the id field. Perhaps try this:

Drop any rows where id > 5. Then, reset the auto increment by using this (obviously change the values to match):

ALTER TABLE yourTable AUTO_INCREMENT=6

Realistically though, I wouldn't suggest relying on an auto-increment field to have perfectly consecutive numbers -- its just not going to happen if you are adding or removing rows from the table.

Benjamin Falk | Falken Creative : Twitter
Skills: Photoshop, Illustrator, HTML, CSS, jQuery, PHP and CodeIgniter

Vote up Vote down

  • Gender: Male
  • From: Chico/Sacramento, CA
  • Registered: December 19, 2008
  • Posts: 2,810
  • Reputation: 14

Re: How to turn back the index counter (auto_increment)

Also, here is the MySQL page on Auto-Increment: http://dev.mysql.com/doc/refman/5.0/en/ … ement.html

Benjamin Falk | Falken Creative : Twitter
Skills: Photoshop, Illustrator, HTML, CSS, jQuery, PHP and CodeIgniter

Vote up Vote down