Page 1 of 1

AUTO_INCREMENT help

Posted: Tue Jan 10, 2006 1:21 pm
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?

Posted: Tue Jan 10, 2006 2:03 pm
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..

Re: AUTO_INCREMENT help

Posted: Tue Jan 10, 2006 4:19 pm
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..)

Posted: Tue Jan 10, 2006 4:48 pm
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.

Posted: Tue Jan 10, 2006 4:52 pm
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());

...

Posted: Tue Jan 10, 2006 5:57 pm
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?

Posted: Tue Jan 10, 2006 6:03 pm
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....

Posted: Wed Jan 11, 2006 5:12 am
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'];
}