[RESOLVED] recursive function to test for unique value in mysql column

lisa99

I have a situation where I need to ensure that a column has a unique ID when a user creates it, but they can create multiple rows with this same ID (as I will have to group them by this later).

Let's say we're quoting prices of different products and the table looks something like this...

| UID       | SALESREP   |  QUOTE ID    |  PRODUCT     |   PRICE     |   DISCOUNT
++++++++++++++++++++++++++++++++++++++++++++++++++++++
+1           + SMITH       + 10568049   +  PRODUCT1   +  19.95     +   10%
+2           + SMITH       + 10568049   +  PRODUCT2   +  12.95     +   5%
+3           + SMITH       + 10568049   +  PRODUCT4   +  16.95     +   0%
+4           + JONES       + 10159870   +  PRODUCT2   +  11.95     +   0%
+5           + JONES       + 10159870   +  PRODUCT5   +  14.95     +   8%
+6           + JONES       + 10159870   +  PRODUCT9   +  17.95     +   4%

What I want to do is generate a QUOTE ID and test it against mysql to see if it already exists because I obviously can't set the column to UNIQUE via mysql.

I was wanting to let PHP do the work with something like this...

//the function
generate_quid(){
$quid=mt_rand();
$q="SELECT * FROM `quotes` WHERE `quid` = '$quid'";
$r=mysql_query($q)or die(mysql_error());
$rows=@mysql_num_rows($r);
if ($rows>0){return false;}else{return $quid;}
}

//a recursive call to the function
if (!isset($_GET['quid']) || !is_numeric($_GET['quid']))
{
while($quid==false){$quid=generate_quid();}
}

Am I going about this the wrong way? I see this might put a lot of strain on mysql.

I'm worried that there might be a point where it runs out of random numbers or comes close and has to run that function hundreds if not thousands of times to find one that works. I expect there to be as many as 5 million rows in here.

Is there a better way to do this?

lisa99

OMG, I'm such an idiot. 🙂

I'll just put in a blank row with the QUOTE ID = 100000000

When I add have to add a new quote entry that didn't previously exist, I can just query for the highest number and add 1.

bradgrafelman

lisa99 wrote:
When I add have to add a new quote entry that didn't previously exist, I can just query for the highest number and add 1.

Except that this creates a race condition allowing the same quote ID to be given out to more than one request.

A better idea would be to have a separate 'quotes' table with an AUTO_INCREMENT column. Insert a row in there, grab the unique id generated, and use that in your INSERT queries for the table above.

lisa99

Ahh...I see. I didn't think about that.

You make an excellent point, sir.

I'll do it that way.

Thanks.

🙂

FFVison

bradgrafelman;10949031 wrote:
Except that this creates a race condition allowing the same quote ID to be given out to more than one request.

A better idea would be to have a separate 'quotes' table with an AUTO_INCREMENT column. Insert a row in there, grab the unique id generated, and use that in your INSERT queries for the table above.

I agree. You might also wish to look into database normalization to help you design your data. This will allow you to avoid data duplication, potentially increase performance of the database, and resolve a bunch of other issues. This will also make it easier to redesign and allow you to add new data without having to do much additional work.