auto_increment not really auto incrementing

Based on the information you provided, there is no reason for this behavior. However, we will need to see the code that actually inserts the records into the table to determine a cause.

A few things.

1 - Can you control the autoinc mechanism to not necessarily be linear, maybe a formula or a trigger.
2 - Does data already exist and you are looking at the inserted IDs?

Some bits ....

To start with an AUTO_INCREMENT value other than 1, you can set that value with CREATE TABLE or ALTER TABLE, like this:

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;




Adding an AUTO_INCREMENT column to a table with ALTER TABLE might not produce the same ordering of the rows on the slave and the master. This occurs because the order in which the rows are numbered depends on the specific storage engine used for the table and the order in which the rows were inserted. If it is important to have the same order on the master and slave, the rows must be ordered before assigning an AUTO_INCREMENT  number. Assuming that you want to add an AUTO_INCREMENT column to the table t1, the following statements produce a new table t2 identical to t1  but with an AUTO_INCREMENT column:





NO_AUTO_VALUE_ON_ZERO

NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next sequence number. (Added in MySQL 4.1.1)

This mode can be useful if 0 has been stored in a table's AUTO_INCREMENT column. (Storing 0 is not a recommended practice, by the way.) For example, if you dump the table with mysqldump and then reload it, MySQL normally generates new sequence numbers when it encounters the 0 values, resulting in a table with contents different from the one that was dumped. Enabling NO_AUTO_VALUE_ON_ZERO before reloading the dump file solves this problem. As of MySQL 4.1.1, mysqldump automatically includes a statement in the dump output that enables NO_AUTO_VALUE_ON_ZERO to avoid this problem.

Hey. here is my PHP for the register.php page which handles that part of the script.

<?php
if(isset($_SESSION['s_username']))
{
      echo 'You are already logged in, you can not register again.';
      exit;
}
if(isset($_POST['submit']))
{

$first = addslashes(trim($_POST['firstname']));
$surname = addslashes(trim($_POST['surname']));
$username = addslashes(trim($_POST['username']));
$email = addslashes(trim($_POST['email']));
$pass = addslashes(trim($_POST['password']));
$conf = addslashes(trim($_POST['confirm']));
$website = addslashes(trim($_POST['website']));

$ip = $_SERVER['REMOTE_ADDR'];
$date = date("d, m y");

if ( $_POST['password'] == $_POST['confirm'] )
{}else{

echo '<script>alert("Your passwords were not the same, please enter the same password in each field.");</script>';
echo '<script>history.back(1);</script>';
exit;

}

$password = md5($pass);

if ((((( empty($first) ) || ( empty($surname) ) || ( empty($username) ) || ( empty($email) ) || ( empty($password) )))))
{

echo '<script>alert("One or more fields was left empty, please try again.");</script>';
echo '<script>history.back(1);</script>';
exit;

}

if((!strstr($email , "@")) || (!strstr($email , ".")))
{

echo '<script>alert("You entered an invalid email address. Please try again.");</script>';
echo '<script>history.back(1);</script>';
exit;

}

$q = mysql_query("SELECT * FROM critter WHERE Username = '$username'") or die(mysql_error());
if(mysql_num_rows($q) > 0)
{

echo '<script>alert("The username you entered is already in use, please try again.");</script>';
echo '<script>history.back(1);</script>';
exit;

}

$name = $first . ' ' . $surname;
$actkey = mt_rand(1, 500).'f78dj899dd';
$act = sha1($actkey);

$query = mysql_query(
"INSERT INTO critter (username, password, name, email, date, actkey, address, address1, city, state, zip, ip, phone, businfo, website)
 VALUES ('$username','$password','$name','$email','$date','$act','$address','$address1','$city','$state','$zip','$ip','$phone','$businfo','$website')"
) or die(mysql_error());
$send = mail($email , "Registration Confirmation" , "Thank you for registering with Critter Classifieds.\n\nYour username and password is below, along with details on how to activate your account.\n\nUser: ".$username."\nPass: ".$pass."\n\nClick the link below to activate your account:\nhttp://www.critterclassifieds.net?a=activate&id=".$act."\n\nPlease do not reply, this is an automated mailer.\n\nThanks", "FROM: auto@mailer.com");

if(($query)&&($send))
{

echo '
<p>Thank you for registering, you will receive an email soon with your login details and your activation link so that you can activate your account.</p>
<p><a href="?a=login">Click here</a> to login once you have activated.</p>
';

} else {

echo '
<p>We are sorry, there appears to be a problem with our script at the moment.</p>
<p>Your data was not lost. Username: '.$username.' | password: '.$pass.' | Email: '.$email.' | Full Name: '.$name.'</p>
<p>Please try again later.</p>
';

}

} else {

?>

Thanks,

Ryan

Even with what I've said, under normal conditions, the DB should use sequential numbers.

Can you delete the table and recreate it?

Yeah, that is going to be my next step. Try having them sign up again and see if it starts normal, by that I mean, 1,2,3, etc.

When I go to run my SQL code to create the table, what can I use at the auto_increment line to start it at 1?

I found what you typed above very helpful, I didnt know any of that. Thanks for that! Would you recomend doing what you said?

Ryan

Alrighty, thank you so much, I am going to give this a shot!

Thanks man.

Ryan

NP.

4 - edit the SQL script to make sure that you have supplied the IDs you want.
5 - make sure the table doesn't use autoinc.

When you say that do you mean:

Say if there 5, 1246, 4

Then

Change them to say 1,2,3 correct?

And when I go to use the import code, make sure the table doesnt have autoinc set and do that manually correct?

Thanks,

Ryan

I would only change the codes if there is no other table linking to the table in question and linking on the ID.

If you are linking based on the username (for example), then that's OK.

Okay, thank you!

I'll let you know how I make out with that

Ryan