Link to home
Start Free TrialLog in
Avatar of gingera
gingera

asked on

How to insert a random number in each row of an existing MySQL table?

MYSQL PHP


Hi,

How do I insert a 10-digit random number for each row in a mysql database?

For example... I want to insert a 10-digit random no. in the 'Unique_No' field.

Date, Food, Unique_No

2008-01-01, Beef Steak, ?
2008-01-02, Pasta, ?
2008-01-03, Pizza, ?
2008-01-04, Taco, ?

becomes

2008-01-01, Beef Steak, 6512389356
2008-01-02, Pasta, 5462156248
2008-01-03, Pizza, 9210104732
2008-01-04, Taco, 1260549879


I suppose we will use a mysql query something like:
UPDATE eating_record SET Unique_No = ..... ?

Thanks.
SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
hi, you can do something like this but it will not guarantee that it will be unique...

FLOOR(1 + (RAND() * 10000000000));
Avatar of gingera
gingera

ASKER

Thank you both.

Can you please phrase your answer in

UPDATE eating_record SET Unique_No = ... ?
UPDATE eating_record SET Unique_No = RAND()
Actually it should be ee_rlee's versin as his gives 10 digit exactly

UPDATE eating_record SET Unique_No = FLOOR(1 + (RAND() * 10000000000));
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of gingera

ASKER

Thanks AlexanderR and ee_rlee.

I have tested

UPDATE eating_record SET Unique_No = FLOOR(1000000000 + (RAND() * 9000000000));

It sets ONE random no. into each row. I need a UNIQUE random no. in each row. How do I do that?
Avatar of gingera

ASKER

By the way, I am running a PHP script to insert a random no. into a mysql database, so that code currently is as below.

I suppose we need to write the script in such a way that FOR EACH row, create a (different) random no.
<?php
 
 
  // Connect to mysql database
 
$connection = mysql_connect("localhost", "username", "password");
if (!$connection)
  {
  die('Could not connect: ' . mysql_error());
  }
 
mysql_select_db("eating_record", $connection);
 
 
$random = FLOOR(1000000000 + (RAND() * 9000000000));
 
$query = "UPDATE eating_record SET Unique_No = '$random' ";
 
if (!mysql_query($query, $connection))
  {
  die('Error: ' . mysql_error());
  }
 echo "Random numbers inserted";
 
 
?>

Open in new window

Thanks for the script!
Looks like you need to loop through the whole table and update each row individually
In your table, do you have a primary key column with auto_increment or smth?
try this

$query = "UPDATE eating_record SET Unique_No = FLOOR(1000000000 + (RAND() * 9000000000))";

or if unique_no is varchar

$query = "UPDATE eating_record SET Unique_No = CAST(FLOOR(1000000000 + (RAND() * 9000000000)) AS varchar)";
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of gingera

ASKER

I am dropping in to mention that I have solved the problem. I added a column with an autoincrement value and added it to a random number to create a unique ID.

Thanks very much AlexanderR for the script! I didn't get to use it but it seems what you have there is very similar to what I did. If you have tested it and it works, I guess your script is the best offered solution to my question.

Problem solved, with thanks for everyone's inputs.
Avatar of gingera

ASKER

Thank you very much for your help!
hmm, have you tried my last post? I thought that should have worked (although not guaranteed to be unique). In your script, the random number is generated by php before updating the records. I modified it so that the random number is generated by mysql so each record should be assigned a random number.

Since you already solved your problem (although it will only make the first record random, but it will guarantee that the values are unique), I posted this just to let you know that it should be possible to generate random numbers in mysql. :-)
I think you might have missed his point:
>>"It sets ONE random no. into each row. I need a UNIQUE random no. in each row. How do I do that?"
Your solutions are perfect if he wants to perform a single operation for every new record, but the problem i guessed was that he already had all the records in place and needed to ADD a unique number to EACH ONE.  For that you need to cycle through each record and add a unique number. Thats why i did mine through php so that each record in the table will receive its own UPDATE statement.  I could have used your SQL for random number, but i was thinking in php at the time.  I also added usleep(10000);  to make a pause between each cycle so that it has a chance to generate better random number.
In sql server, using the rand function will generate the same random number in every record. But in mysql, rand function will generate a different random numbers for each row. The problem with the script used is because the value passed to mysql is already a constant since the random number is generated at php.

>it has a chance to generate better random number.
Which means that it also doesn't guarantee that the number generated will be unique. I believe that the only way to generate a random number is to keep track of the numbers already used, or the by using an id (which the author used) and use it as a seed in the rand function. i.e. RAND(ID)
>>generate a different random numbers for each row
You are right!!  I must have forgotten about that because i didn't set it to bigint as i should have, and while i had it in INT it kept on giving me the same number so i was under an illusion that it needed a more manual approach.

But if thats the case your comment 21081743 (second one) should give a perfect answer so then why he said in comment7 ID:21081882:

I have tested

UPDATE eating_record SET Unique_No = FLOOR(1000000000 + (RAND() * 9000000000));

It sets ONE random no. into each row.

May be he also had a type mismatch like i did.
I don't know either. Maybe I would test it if I have time and resources(I currently don't have mysql installed). Btw, it is not perfect since it's not guaranteed to be unique. :-)