[SOLVED] Primary key insert

[frost]

I have a large database that I am doing some work on.

 

One of the columns is a random number that is also a primary key and each time an entry is written to the database an new item is added to this column. I want to ensure that something is written each time.

 

So basically I need to check if the number I want to insert already exists and if it does, create a new number.

 

I was hoping someone could show me a fast way to do this.

 

Cheers.

 

 

[micah1701]

does it have to be a random number? if not, just use auto increment

[frost]

Yes it has to be random, I didn't build the database so I have to work with what is here 

[fenway]

Yes it has to be random, I didn't build the database so I have to work with what is here 

That is the PK -- you'll get a unique number, auto-incremented on its own.  If you want a pseudo-random field, just use the PK value as part of the "salt" for a one-way hash function.

[frost]

I am sorry, I didn't make myself clear. They are not random numbers they are alpha numeric.

 

Here is an example of a few that were entered into the database one after the other:

 

07.2007.HNE.100

07.2007.QYJ.100

07.2007.ICC.100

 

As you can see there doesn't seem to be a sequence...

 

 

[littledragon]

What are you using to access the database?  If it's php then I have a simple answer...

 

if not, the you need to generate the key and run a query like

 

SELECT COUNT * FROM 'table' WHERE 'primary_key_field' = 'new_key';

 

and as long as it's zero, you can insert it.

 

P.S. you'd better check that syntax I scripted all my mysql queries into php functions long ago 

[frost]

So now the problem becomes is what if it is not zero?

 

I need some sort of a loop to check for the value until one works right?

[littledragon]

So now the problem becomes is what if it is not zero?

 

I need some sort of a loop to check for the value until one works right?

 

If not zero, you need to generate another one.  What are you using to generate these numbers?  Need some more info here, sure we can help.  Simple php loop:

 

<?php
do{
$new_key = mt_rand(0, 1000000);
$query  = "SELECT COUNT * FROM 'table' WHERE 'primary_key_field' = '$new_key';";
$result = mysqli_query($query);
}while($result > 0);?>

 

[otuatail]

Would it be possible to draw a line in the sand and create a new sequnce of numbers as these numbers are nut int's You could cary on from where it left of

 

[otuatail]

One other thing to consider. If you wanted to draw a line in the sand as sugested above, you could use the unix date stamp as it would be unique and you get a date of entry thrown in as extra.

 

Desmond.

 

[frost]

aha!

 

the do-while loop is what I needed.

 

Cheers.