New IDs

I have written a script, whenever a client clicks on "New Product" the script would do a mysql_query("INSERT INTO prods") to produce a new id, because this id is used in the page before the client clicks on save button.

Now, the client is asking that the id should be like 245, 246, 247 not to miss ids between the them, I really need your ideas, what should I do to produce id that is actually inserted, because I have also tried this:

$check_id = mysql_query("SELECT id FROM prods WHERE used = 'no' ORDER BY id DESC");

now this will give me the id which is not used, but what if a two clients access the same page?

Help guys!!:eek:

The best way to avoid racing conditions (when 2 clients access the same page) is to use AUTO_INCREMENT field.

If this is not acceptable, another way could be (not recommended): You make ID a PRIMARY KEY (it would be unique and not null). The in a loop you find a vacant ID with SELECT and then try to INSERT. If INSERT fails (if I remember correctly it would fail with error code 1062 which could be checked with mysql_errno() too), this ID is already taken and you repeat the SELECT-INSERT iteration. You could do it in a for-loop with big maximum amount of iterations (100000 or something) just not to create an infinite loop if anything goes wrong. But you could use this approach at your own risk. I would not recommend it. The best way is to use an AUTO_INCREMENT field and to explain to the client that ID assigning is most effectively done by the database itself.

I don't see why you need to add the ID to the page before inserting the whole product into the database. You can do this when you post the data to your script, and then use the inserted id for the product for the rest of the info in different tables if needs be