AUTO_INCREMENT help
Moderator: General Moderators
AUTO_INCREMENT help
I am having an issue and I have a way of getting around it. However, I don't like my way and it seems like there should be an easier "built-in" way of doing it. Have a look and see if you can think of a better way...
I have some product tables. The tables have an "id" column, which is used in iterating through the table in later loops. Thus, it is very important that the id column always starts at 1 and goes *without skipping any numbers* up to the max row number.
I am building an administrative page where I can add and delete products from these tables. Whenever I add or delete a product, I need the auto_increment "id" column to either fill in a hole from the newly deleted product, or add on to the end for an added product. However, as it stands this does not happen.
When I delete, say, row 45 from a table, the next product I add will not fill in row 45, but will instead tack onto the end and auto_increment from the largest value in id. Furthermore, if I delete all but one of 450 values, my id column may later end up going from 1 straight to 451 when I add another product.
My solution is this process:
1. I go in and add or delete a row
2. mySQL pulls all values from product table, and puts them in a temporary table
3. mySQL deletes all entries in the old product table
4. mySQL pulls all product info from the temp table and rewrites it to the product table (resetting auto_inc)
5. mySQL deletes the temporary product file
This will effectively restart the auto_increment count every time a product is added, but it risks losing all product data if somehow an error occurs while the data is being moved around. It's unlikely, but could result in the end of the world. Is there a better way?
I have some product tables. The tables have an "id" column, which is used in iterating through the table in later loops. Thus, it is very important that the id column always starts at 1 and goes *without skipping any numbers* up to the max row number.
I am building an administrative page where I can add and delete products from these tables. Whenever I add or delete a product, I need the auto_increment "id" column to either fill in a hole from the newly deleted product, or add on to the end for an added product. However, as it stands this does not happen.
When I delete, say, row 45 from a table, the next product I add will not fill in row 45, but will instead tack onto the end and auto_increment from the largest value in id. Furthermore, if I delete all but one of 450 values, my id column may later end up going from 1 straight to 451 when I add another product.
My solution is this process:
1. I go in and add or delete a row
2. mySQL pulls all values from product table, and puts them in a temporary table
3. mySQL deletes all entries in the old product table
4. mySQL pulls all product info from the temp table and rewrites it to the product table (resetting auto_inc)
5. mySQL deletes the temporary product file
This will effectively restart the auto_increment count every time a product is added, but it risks losing all product data if somehow an error occurs while the data is being moved around. It's unlikely, but could result in the end of the world. Is there a better way?
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
that is not what auto_increment is for. I don't see why they must be stored in unbroken order. I can only guess that you iterate over the records in an unusual way instead of standard loops of asking for the next record not needing to care what id it actually is, more concerned about what data is inside..
Re: AUTO_INCREMENT help
Why is this required? If you base your loops on the ordernumber that an item has in an ordered resultset you don't have to care about the actual id (and make your code usable for other types of primary keys)bwv2 wrote: I have some product tables. The tables have an "id" column, which is used in iterating through the table in later loops. Thus, it is very important that the id column always starts at 1 and goes *without skipping any numbers* up to the max row number.
(If you insist to continue to follow the path that you've taken now, you should look at transactions. They offer you the possibility to consider a set of queries as one. But be prepared for other surprises..)
Thanks for the wake up. I'm still learning mySQL, so it's a learn-as-I-go. I've been looping like this:
Can someone tell me the normal way of doing this? I'm like a sponge here; anything you tell me will be absorbed and used later. Any help would be much appreciated.
Code: Select all
for($i=1; $i<=$numRows; $i++){
$sql="SELECT * FROM table WHERE id=$i";
$result=mysql_query($sql);
yada...yada...yada...
}- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
It depends on your intentions..
for example, how are you getting $numrows.. and why? Why not just run a normal query to get the result set (no need for where clause)
for example, how are you getting $numrows.. and why? Why not just run a normal query to get the result set (no need for where clause)
Code: Select all
$sql="SELECT * FROM `table` ORDER BY `id`"
$result=mysql_query($sql) or die(mysql_error());
...Ah yes, now I see your question. Here's a better explanation. Each row contains specifications for a specific product. I want to choose the best product for a predetermined application. The values associated with each product (and stored within its row) can be applied in a mathematical equation to determine a quantifiable rating as to how good the product is for the application.
Thus, here is a basic summary of my loops:
1. connect to database and pull all row data for current row.
2. assign the row contents to variables.
3. use the variables in the equation to get an outcome.
4. compare this outcome to previous outcomes and see if it's the best.
5. if it is the best, set it as $best and loop back for the next row.
After the entire table has been looped through, I am left with the very best product for the application. I have been using the table colum "id" as an iteration tracker. Before I go into the loop, I call a code like this:
Now I have the number of rows in my product table. If my "id" field is continuous and starts at 1, then my FOR loop works when I call it as above:
Now that that's cleared up, can anyone see a better way of doing it still? I assume there is a better way, but I just don't know it. Otherwise, would my process of temporary tables, deleting the old, and reforming the tables be the best solution?
Thus, here is a basic summary of my loops:
1. connect to database and pull all row data for current row.
2. assign the row contents to variables.
3. use the variables in the equation to get an outcome.
4. compare this outcome to previous outcomes and see if it's the best.
5. if it is the best, set it as $best and loop back for the next row.
After the entire table has been looped through, I am left with the very best product for the application. I have been using the table colum "id" as an iteration tracker. Before I go into the loop, I call a code like this:
Code: Select all
$sql="SELECT * FROM products";
$result=mysql_query($sql);
$numRows=mysql_num_rows($result);Code: Select all
for($i=1; $i<=$numRows; $i++){
$sql="SELECT * FROM products WHERE id=$i";
$result=mysql_query($sql);
//now I assign the vars and test the product
}- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
whatever the logic you are tyring to implement, it is never a good idea to generate auto numbers or trying to fill in holes. When I first started programming in ASP, I used to generate member ids from M10001 but instead found that auto id generation by the database is reliable and scaleable.
I would never say that looping ids like from 1...something is not a good idea...pls rethink again....
I would never say that looping ids like from 1...something is not a good idea...pls rethink again....
why not something like this?
Code: Select all
$sql="SELECT * FROM products";
$result=mysql_query($sql);
while($assoc = mysql_fetch_assoc($result)) {
//assign the vars and test the product
// for example:
echo $assoc['id']; // echo $assoc['field'];
}