How to Auto generate a 4 digit pin that is unique in MySql

How can i have the ID be a 4 digit Random unique number??  Automatically!!!   Thanks in advance.  Im new so please explain how to do it.  Thanks
jporter80Asked:
Who is Participating?
 
UmeshConnect With a Mentor MySQL Principle Technical Support EngineerCommented:
This shouldn't be an issue...  

in php say you have to do the insert then..
# Insert statement
 
mysql_query("SET @myPin:=returnPin();
             INSERT INTO PRData.Employees( Name
                             ,phone_number
                             ,email_address
                             ,pin
                             ) 
                       VALUES('Umesh Shastry'
                              ,'89898989'
                              ,'ushastry@gmail.com'
                              ,@myPin
                             );")

Open in new window

0
 
Cornelia YoderArtistCommented:
UPDATE Table SET ID=RAND()*4
0
 
Cornelia YoderArtistCommented:
The problem is that RAND()*4 is not guaranteed to be unique.

If you want to guarantee uniqueness, it's best to do it in your programming language.  Do you have to do it in MySQL, or can you do it in php?
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
jporter80Author Commented:
I would prefer to be in MySQL
0
 
jporter80Author Commented:
i basically want a 4 digit employee pin so it needs be unique and generated automatically.
0
 
Cornelia YoderArtistCommented:
Does it have to be random?  Can you make an autoincrement field?

If you make the ID field a key, you can guarantee uniqueness, but you can't guarantee that some query won't fail on duplicate key if RAND happens to generate a number that already exists.
0
 
jporter80Author Commented:
if i made it autoincrement it would make it easier for employee to miss type there pin by one and fall on another employee
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
0
 
jporter80Author Commented:
That is kind of what im looking for.   Is there any way to make that field automatically do that in the database at entry.  Kind of like how an ID is autoincrement.  it automatically enters that ID number when a new entry is entered,  How do i get that done inside the Mysql database
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
Trigger should do the trick...
If you want then let me know...

Thanks,
Umesh
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
Sorry correction... when you insert a record mysql is doing some lock stuff. you can't insert/update/delete rows of the same table where you insert.. because then the trigger would called again and again.. ending up in a recursion... pls avoid using trigger..instead write the logic in function & assign it to some vriable

Better store the new 4 digit number in a variable like below

SET @number:=FunctionToReturnUniq4Digit();

while inserting use @number... that's it

0
 
jporter80Author Commented:
Okay here i have this

Database:  PRData
Table: Employees
Column: Pin

I want the "pin" to automatically generate a unique 4 digit number when a line is inserted in to the table.  What MySQL code can i run to set those properties on that column.

Thank you so much for you help

Thanks
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
Would be gr8 if you can provide complete table description...


0
 
jporter80Author Commented:
it just basically has Name, phone number, email address.  All of which has a Varchar type information
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
Here you go..
# Create below Fucntion - once created no need to run this code..
 
DELIMITER |
 
CREATE FUNCTION PRData.returnPin()
    RETURNS VARCHAR(10)
BEGIN
 
	SELECT FLOOR(1000+ RAND()*8999) INTO @number FROM DUAL;
        RETURN(@number);
END;
 
|
 
 
 
# This should be called everytime in order to create unique pin..
SET @myPin:=returnPin();
 
# Your insert query goes here
 
INSERT INTO PRData.Employees( Name
                             ,phone_number
                             ,email_address
                             ,pin
                             ) 
                       VALUES( "Umesh Shastry"
                              ,"89898989"
                              ,"ushastry@gmail.com"
                              ,@myPin
                             );

Open in new window

0
 
UmeshMySQL Principle Technical Support EngineerCommented:
Did you try???
0
 
jporter80Author Commented:
Well that does work,  if im running directly in the MySql Database.  So i have to run the SET @myPin:=returnPin(); everytime??  Lets say i have a php page that inserts an employee into the table.  how would that work??
0
 
Cornelia YoderArtistCommented:
Sheesh, if you have a php page, just use

do{
        $usernumber=rand(1000,9999);
        $result=mysql_db_query($dbname,"Select * from YourTable where usernumber=$usernumber;",$conn);
}while(mysql_num_rows($result)>0);
0
 
jporter80Author Commented:
ushastry,

Well now i have a problem again in the php code i have this:

$insertSQL = sprintf("SET @myPin:=returnPin();
INSERT INTO premployee (employid, username, password, email, phonepersonal, phonepr, employtype, name, sms, permission, active) VALUES (@myPin, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['username'], "text"),
                       GetSQLValueString($_POST['password'], "text"),
                       GetSQLValueString($_POST['email'], "text"),
                       GetSQLValueString($_POST['personalph'], "text"),
                       GetSQLValueString($_POST['prphone'], "text"),
                       GetSQLValueString($_POST['employtyp'], "text"),
                       GetSQLValueString($_POST['name'], "text"),
                       GetSQLValueString($_POST['sms'], "text"),
                       GetSQLValueString($_POST['permission'], "text"),
                       GetSQLValueString($_POST['active'], "text"));


But it gives me this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; INSERT INTO premployee (employid, username, password, email, phonepersonal, p' at line 1
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
Pls try this... I just removed SET @myPin:=returnPin(); from the query instead directly used function name...

This should work...
$insertSQL = sprintf("
INSERT INTO premployee (employid, username, password, email, phonepersonal, phonepr, employtype, name, sms, permission, active) VALUES (returnPin(), %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['username'], "text"),
                       GetSQLValueString($_POST['password'], "text"),
                       GetSQLValueString($_POST['email'], "text"),
                       GetSQLValueString($_POST['personalph'], "text"),
                       GetSQLValueString($_POST['prphone'], "text"),
                       GetSQLValueString($_POST['employtyp'], "text"),
                       GetSQLValueString($_POST['name'], "text"),
                       GetSQLValueString($_POST['sms'], "text"),
                       GetSQLValueString($_POST['permission'], "text"),
                       GetSQLValueString($_POST['active'], "text"));

Open in new window

0
 
UmeshMySQL Principle Technical Support EngineerCommented:
Pls make sure the function exists.... otherwise you will get error
DELIMITER |
 
CREATE FUNCTION PRData.returnPin()
    RETURNS VARCHAR(10)
BEGIN
 
        SELECT FLOOR(1000+ RAND()*8999) INTO @number FROM DUAL;
        RETURN(@number);
END;
 
|
 

Open in new window

0
 
UmeshMySQL Principle Technical Support EngineerCommented:
Did you check??
0
 
jporter80Author Commented:
yeah worked

thanks!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.