How to turn back the index counter (auto_increment)

[madmhan84]

 

Country:

 

 

Animal:

 

 

 

<?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 "

while ($row = mysql_fetch_row($result))

{

echo "

echo "

" .$row[0]. "";

echo "

" .$row[1]. "";

echo "

" .$row[2]. "";

echo "

";

}

echo "

";

}

echo "

";

echo mysql_num_rows($result) . " Total No. of Records";

 

mysql_free_result($result);

mysql_close($connection);

}

?>

 

 

Edited August 20, 2009 by madmhan84

[bishwadeep]

Country:


Animal:





<?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 "

" .$count. "

" .$row[1]. "

" .$row[2]. "

";
}
echo "
";
echo mysql_num_rows($result) . " Total No. of Records";

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

[madmhan84]

sir bishwadeep,

 

thanks for the great help!

[falkencreative]

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.

[madmhan84]

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!

Edited August 30, 2009 by madmhan84

[falkencreative]

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.

[falkencreative]

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