Reset auto_increment id field

[chrisdee]

I'm making a simple php/mysql blog and got the script below to add new messages.

The problem is that when I delete messages, and then post new messages the auto_increment of id starts at the last deleted message, and is not reset to the last table entry. So I have to run alter table tablename auto_increment = 1; to reset the table everytime I delete a message.

Is there a way to do this automatically ?

<?php
mysql_connect("localhost", "user","pass") or die("Could not connect: " . mysql_error());
mysql_select_db("db");

# The variables
$name=mysql_real_escape_string($_POST['name']);
$message=$_POST['message'];
$date = mysql_real_escape_string(date("d.m.Y | H:i:s"));
$ip=$_SERVER['REMOTE_ADDR'];
$browser=$_SERVER['HTTP_USER_AGENT'];

if(!$_POST) {
 echo "<form method='post' action=".$_SERVER['PHP_SELF'].">";
 echo "<input type='hidden' name='id'>";
 echo "<input type='hidden' name='browser'>";
 echo "<input type='hidden' name='ip'>";
 echo "<input type='hidden' name='date'><br>";
 echo "Your name :<br><input type='text' size='70' name='name'><br>";
 echo "Your message : <br><textarea rows='20' cols='100' name='message'></textarea><br>";
 echo "<input type='submit' value='Post'>";
 echo "<input type='button' onclick='history.go(-1)' value='Cancel'>";
 echo "</form>";
}
elseif(empty($_POST['name']) or empty($_POST['message'])) {
 print "Fill inn all fields! <a class=cd href=add.php>Try again</a>";
}
else {
 $sql = "INSERT INTO table SET name='$name', message='$message', date='$date', browser='$browser', ip='$ip'";
 $query = mysql_query($sql) or die(mysql_error());
 echo "Message posted.......<br><a href=index.php?id=blog.php>To mainpage</a>";
}
?>

[bradgrafelman]

chrisdee wrote:

Is there a way to do this automatically ?

No, because you should never alter an AUTO_INCREMENT sequence in a SQL table. Doing so destroys the relationships that the field was meant to be used for.

Why does it matter whether or not the AUTO_INCREMENT values are sequential or not? They're meant to be used by the DBMS (not you) to identify rows... if you want a counter variable, then create a counter variable - don't use the AUTO_INCREMENT column as one.

[chrisdee]

Thanks for clearing that up.

I was looking for a way to register and show the post number on my blog.
Meaning, the first post will get number 1, the second post number 2, third post number 3, and so on.

[Weedpacket]

There's no inherent order to the records in a table; this is why the ORDER BY clause exists in select statements, to define what order the results should be listed in.

[Roger_Ramjet]

If you want to calculate and display a sequential number then have a look at my blog post on How to Sequence a Set of Records