AUTO_INCREMENT help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
bwv2
Forum Commoner
Posts: 83
Joined: Fri Jun 10, 2005 11:50 am
Location: AZ

AUTO_INCREMENT help

Post by bwv2 »

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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

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..
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Re: AUTO_INCREMENT help

Post by timvw »

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.
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)

(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..)
bwv2
Forum Commoner
Posts: 83
Joined: Fri Jun 10, 2005 11:50 am
Location: AZ

Post by bwv2 »

Thanks for the wake up. I'm still learning mySQL, so it's a learn-as-I-go. I've been looping like this:

Code: Select all

for($i=1; $i<=$numRows; $i++){
     $sql="SELECT * FROM table WHERE id=$i";
     $result=mysql_query($sql);
     yada...yada...yada...
}
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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

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)

Code: Select all

$sql="SELECT * FROM `table` ORDER BY `id`"
$result=mysql_query($sql) or die(mysql_error());

...
bwv2
Forum Commoner
Posts: 83
Joined: Fri Jun 10, 2005 11:50 am
Location: AZ

Post by bwv2 »

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:

Code: Select all

$sql="SELECT * FROM products";
$result=mysql_query($sql);
$numRows=mysql_num_rows($result);
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:

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
}
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?
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

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....
mickd
Forum Contributor
Posts: 397
Joined: Tue Jun 21, 2005 9:05 am
Location: Australia

Post by mickd »

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'];
}
Post Reply