I want to add 100 entry to users table numbers field, random characters length is 10, all entry should be unique as well. How can i achieve this using MYSQL query code ?
Or do i need to use PHP ?
Help me with code snippets please. Thanks.
If rand returns the same number twice, wouldn't that create a violation of the uniqueness constraint?
sum1stolemyname
2010-03-23 07:59:24
will only work in mysql, in other DBMS you would need to truncate the md5
knittl
2010-03-23 07:59:43
hi, i want to add 100 entry by once ? How can i ?
spotlightsnap
2010-03-23 08:01:30
Hi @sum1stolemyname you are right. my mistake. A function counting from 1 to 100 will be better. But anyway just wanted to point a direction for solution.
aviv
2010-03-23 08:06:28
@spotlightsnap: are you talking about 'entry' (which I read as add to field for one user or 'row', as in : add 100 users with a 10-char number_field for each???
lexu
2010-03-23 08:06:28
hi lexu, it's like this, i have table name called users, at users table, there's field name called numbers, i want to generate 100 row and insert into that users table numbers field. Currently the above code generate only 1 row at a time. thanks
spotlightsnap
2010-03-23 08:12:02
+2
A:
in mysql u can do like :
insert into table ( SUBSTRING(MD5(RAND()) FROM 1 FOR 10) , field2 , field3) , ( SUBSTRING(MD5(RAND()) FROM 1 FOR 10) , field2 , field3) , .........
..............
in php see this 2 links :
http://stackoverflow.com/questions/307486/short-unique-id-in-php/307773#307773
http://stackoverflow.com/questions/637278/what-is-the-best-way-to-generate-a-random-key-within-php
Haim Evgi
2010-03-23 08:00:12
hello, i want to add 100 entry once. How can i achieve ? I have to use insert instead of update right ?
spotlightsnap
2010-03-23 08:05:37
+1
A:
That may create duplicates and it's too long. This is 10 char long:
UPDATE users SET numbers = SUBSTRING(MD5(RAND()) FROM 1 FOR 10)
However, you could still get duplicate values.
So you could put a unique restraint on your column. Then try the update. If there are duplicates, you will get an error. Then just try again. You only have 100 entries, so it's probably fine.
Is this for passwords?
If so, I'd recommend encrypting the value. Of course you then have the problem of not knowing what the value is. So you could create a temporary table, insert the random values in there. Then encrypt the values as they are inserted from the temp table into the real table. You can then use the temp table for reference (giving the users their passwords, etc). Hope that helps.
sims
2010-03-23 08:14:57