The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

One thing to keep in mind is the possibility of a race condition. You might do the search, and find the code is available. But in the tiny window of time between the search, and the actual insert, it's possible another script already inserted the same code. Now you might have a duplicate.

If this might be an issue, you can:
Let the database enforce uniqueness. Declare a unique constraint on the column. It will not fail you due to a race condition.

But, you still need to detect the collision so you can regenerate.
The easiest way imo is to just insert it, and check for the duplicate key error code. If it happens, regenerate and try again.

Another way is to lock the table. Once you have the lock, you can safely do a select to check, and follow up with the insert, then unlock. I think this is better if you have more than one unique constraint on the table, because you can easily see which column was the conflict. With the other method, you need to resort to parsing the error message to find out which column, which I think is real ugly.

@SBK - What happens if when you regenerate, the code regenerated is already in the DB? You don't have a check on the newly generated code.

@crmalibu - I do have a unique constraint on that column, but I'm not inserting it at that point. I'm wanting to generate it so that it's available to view before any info is input in a form to db entry. (that was difficult to word )

But you have me thinking maybe I should change how I'm doing that, especially if inserting the code at that point would be faster than querying and running it through a loop.

Well, I need a six character, alphanumeric code that's unique to each client.
When I go to add a client, I want that code generated upon page load so it can be seen as the client info is being filled in to the form.

@crmalibu - I do have a unique constraint on that column, but I'm not inserting it at that point. I'm wanting to generate it so that it's available to view before any info is input in a form to db entry. (that was difficult to word )

In that case, would it not be simpler to insert only that 6-letter code, and a flag for 'new'?

If it returns an error complaining of duplication, loop it. That way you'll always have 1 less query than your current solution.

Pass that 6-letter code onto the form, so when you insert it it's just a case of an update query - making sure that the update clause is that the code is equal AND 'new' is flagged - unflagging it in the query.

That 'new' flag should be enough to stop someone from (whether on purpose or by accident) overwriting a current record.

Jake Arkinstall
"Sometimes you don't need to reinvent the wheel;
Sometimes its enough to make that wheel more rounded"-Molona

If it returns an error complaining of duplication, loop it. That way you'll always have 1 less query than your current solution.

Doesn't my solution only have 1 query? 2, actually if you count the form insert query.

If I loop it, wouldn't it be sending a query every time it happens to match (duplication)?

Following your suggestion, I'm not understanding the purpose of a 'new' flag. If the code isn't a duplicate, the insert is successful and can return the newly inserted ID. On $_POST submit, wouldn't you just update that ID? Where does the threat of overwriting a record come from?