tags:

views:

24

answers:

2
+1  Q: 

Mysql, reset id's

Hey guys, take a look at this, maybe you can help me. This code is pretty much self explanatory,it repeats itself via cron job.

<?php
$dbhost = 'blabla';
$dbuser = 'blabla';
$dbpass = 'blabla';
$dbname = 'blabla';
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
mysql_select_db($dbname);
$table = 'cron';
$feed = 'a big xml feed';

$xml = simplexml_load_file($feed);

mysql_query("TRUNCATE TABLE ".$table.""); // empties cron 

foreach( $xml->performerinfo as $performerinfo )
{
    bla bla inside foreach to get data from xml  

    mysql_query("INSERT INTO ".$table." (performerid, category, subcategory, build, hairlength, ethnicity, willingness, pic0, pic1, pic2, pic3, pic4, age) VALUES ('$performerid', '$category', '$subcategory', '$build', '$hairlength', '$ethnicity', '$willingness', '$pic0', '$pic1', '$pic2', '$pic3', '$pic4', '$age')");  
}  // inserts into cron 

   mysql_query("TRUNCATE TABLE newtable"); // empties newtable

   mysql_query ("INSERT INTO newtable SELECT * FROM ".$table." WHERE ethnicity = 'White' AND age BETWEEN '30' AND '45' AND build <> 'above average' AND build <> 'large' AND build <> 'average' AND hairlength <> 'short'"); // inserts into newrtable

?>

QUESTION, hope you can understand me ;)

Table Cron has id's from 1 to 1000

id      int(11)             No          auto_increment

When i filter Cron and insert the rows into newtable, i get id's like: 20, 65, 145, 699, etc.

How can i reset the newtable's id's so they will appear consecutive, from 1 to 35, lets say...strong text

A: 

Don't select the id in your "select * from ..." and instead have an auto_increment field in newtable.

I'm not sure if a truncate will reset the auto_increment counter, but you can do it manually with

ALTER TABLE newtable AUTO_INCREMENT=1

You might consider using temporary tables since you truncate end refill them all the time.

jishi
+2  A: 

It is possible to reset the auto-increment ID, but the reason why this doesn't happen automatically is to prevent a new record having the same ID as an old, deleted one. That is considered bad practice.

Whether the ID is 39 or 293440 should not matter for your application. If it does matter, you have an architectural problem.

Pekka
Really need a work around it, cause i use this code with a wordpress loop, and i need a way to manipulate this inside the loop
webmasters
@webmasters why? If it's an auto increment, this shouldn't be necessary. If you need to assign post IDs to it, it shouldn't be an auto increment.
Pekka