Auto_Increment -- Help!

novon

I have a quick mySQL/PHP question I was hoping I could ask the experts:

My table 'ID' column is setup as "auto_increment", There were 20 rows, I deleted number 18, and renamed 19 and 20 to 18 and 19 respectivley. However, on the next row creation, the auto_increment starts at 21 instread of 20, somewere it's storing the last used row number, but I couldn't find any documentation in the online manual. Any idea on how to change this number? I really need the rows to be ordered by number.

Thanks

novon

bastien

don't mess with it...they are extremely sensitive when mucking about with and doing so wil cause you endless headaches...either leave it (it most cases autonumber only act as unique identifiers to records and have no other purpose. so skipping numbers does not have any downsides.

what i like to do about deletes is NOT ALLOW THEM. i create an extra column (tiny int) and set it to 1 or 0 for deleted or not deleted and then any sql statements executed against the table is set to only take records with the not deleted indicator...

this way i can remove all privledges against the table except insert, select and update....any potential hackers get stopped here and data integrity is maintained. if they do manage to set all the deletes to on then I can simply turn them off and all is okay again.

hth

novon

Com'on, there has to be a way to change that one number...

frigidman

Then maybe do not use the auto_increment field... but rather just have it as an INT, and when you go to insert the new row, do a "MAX(ID)+1". This grabs the maximum id number in the table, then increments it up one for the next new row.

This means NOTHING is automatic for you. Auto_increment is an incremental number that continues to step from where it left off, and is non adjustable (except to reset it completely to 0, but that opens a whole can of ass woopen worms).

novon

Okay, I tried that however I get a "Warning: Wrong parameter count for max() in /www/topics.php on line 159" Error. Any ideas???

$timeStamp = time();
			// Get Current ID
			$query = mysql_query("SELECT ID FROM topics");
				while ($content = mysql_fetch_array($query)) {
					$lastID = $content["ID"];
					$topicID = max($lastID) + 1;
				}

		//Insert into DB
		$sql = "INSERT INTO topics SET 
	    ID='$topicID',
		topicName='$topicName',      
		topicText='$topicText',
		topicDate='$timeStamp'";

RussWright

OOH! OOH I know this one!

Change your query as follows

$query = mysql_query("SELECT MAX(ID) AS MAX_ID FROM topics");

            while ($content = mysql_fetch_array($query)) {

                 $NextTopicID = ($content [MAX_ID] + 1);

            }

This will give you the next greatest value
Regards
Russ

novon

thanks for the help

frigidman

Right, sorry... shoulda mentioned that goes inside the mysql query statement, and is not actually a php action. Oops!

ozzythaman

ladies and gentlemen;

alter tablename auto_increment=#

no comments,
who's da master of php?? (no pictures, please..)

Weedpacket

Dunno about PHP - that's MySQL you're talking there.

ozzythaman

sorry, my mistake, they are both so in my veins i can't even tell them (mysql and php) apart... and i love them both equally..so don't ask me who i like the most.. they are like my children..

and windows is the wife i argue with most of the time..

and linux is like the woman i could have had if i had "gone for it" when i had the chance.. perhaps we'll bump in on eachother one day and who knows then what might happen...