Solved

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

Posted on 2008-11-01
23
1,464 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:jporter80
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
  • 4
23 Comments
 
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 22857832
UPDATE Table SET ID=RAND()*4
0
 
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 22857835
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
 

Author Comment

by:jporter80
ID: 22857841
I would prefer to be in MySQL
0
 Watch the Recording: Learning MySQL 5.7

MySQL 5.7 has a lot of new features. If you've dabbled with an older version of MySQL, it is definitely worth learning.

 

Author Comment

by:jporter80
ID: 22857856
i basically want a 4 digit employee pin so it needs be unique and generated automatically.
0
 
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 22857864
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
 

Author Comment

by:jporter80
ID: 22857878
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
 
LVL 26

Expert Comment

by:Umesh
ID: 22860162
0
 

Author Comment

by:jporter80
ID: 22864661
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
 
LVL 26

Expert Comment

by:Umesh
ID: 22864670
Trigger should do the trick...
If you want then let me know...

Thanks,
Umesh
0
 
LVL 26

Expert Comment

by:Umesh
ID: 22864718
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
 

Author Comment

by:jporter80
ID: 22864776
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
 
LVL 26

Expert Comment

by:Umesh
ID: 22864783
Would be gr8 if you can provide complete table description...


0
 

Author Comment

by:jporter80
ID: 22864786
it just basically has Name, phone number, email address.  All of which has a Varchar type information
0
 
LVL 26

Expert Comment

by:Umesh
ID: 22864808
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
 
LVL 26

Expert Comment

by:Umesh
ID: 22865239
Did you try???
0
 

Author Comment

by:jporter80
ID: 22866737
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
 
LVL 26

Accepted Solution

by:
Umesh earned 500 total points
ID: 22867095
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
 
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 22867164
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
 

Author Comment

by:jporter80
ID: 22880699
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
 
LVL 26

Expert Comment

by:Umesh
ID: 22883143
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
 
LVL 26

Expert Comment

by:Umesh
ID: 22883154
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
 
LVL 26

Expert Comment

by:Umesh
ID: 22886480
Did you check??
0
 

Author Comment

by:jporter80
ID: 22888362
yeah worked

thanks!!!
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question