microsocket Posted May 9, 2013 Posted May 9, 2013 I have a piece of code that does this already but it is ....shoddy and sh1t to say the least so does anyone happen to know the cleanest and quickest way to do the following : if i had a table with say 11 rows and wanted to assign 10 numbers to that row randomly and one fixed ID. for example numbers : (unique id), 100,200,300,400,500,600,700,800,900,1000 DB rows : 1,2,3,4,5,6,7,8,9,10 ,11 What would be the fastest and easiest way to have it assign the numbers randomly and the unique ID to the DB rows ? I made something that does it already but , like i said earlier, its REALLY bad haha. Quote
KyleMassacre Posted May 9, 2013 Posted May 9, 2013 By what you are describing from what I read there may be a couple ways of doing it. Do you have a set of random numbers or ranges you can use a multi level array maybe or if you have lets just say 1 random range you can do the standard for() function and just loop the inserts that way Quote
Spudinski Posted May 9, 2013 Posted May 9, 2013 Let's see if anyone can figure out how to do this in a SQL-only way... Quote
a_bertrand Posted May 9, 2013 Posted May 9, 2013 Well with a single MySQL query you could do something like: update mytable, (select @rn:=0) rr set mycol=(@rn:=@rn+1)*100; replace mytable with your table name, and mycol with the column you want to update... If you want to start from another number than 100, then simply replace the :=0 with the number you want... you may also replace the *100 with whatever you want for the increment ;) Quote
bluegman991 Posted May 9, 2013 Posted May 9, 2013 Created a little function for you. Haven't really tested using mutli line mysql in php so I don't really know if it's only available using the mysqli extension or if it's also available using the mysql extension. Not sure whether or not it cache's either so I added a drop function if exists query also. If it does cache then you should only have to define the function once, then you can execute the function over across different sessions and connections. If not you will have to define the function in every connection. Well here goes! DROP FUNCTION IF EXISTS my_rand; CREATE FUNCTION my_rand(min INT(11),max INT(11)) RETURNS INT(11) DETERMINISTIC RETURN FLOOR(RAND()*(max-min+1))+1; SELECT * FROM `mytable` WHERE `mycol`>=my_rand(10,100); INSERT INTO `mytable` VALUES(val1,my_rand(10,100),val3,my_rand(10,100)); UPDATE `mytable` set `mycol`=my_rand(10,100) WHERE `mycol`=myval; Also added a few query examples. Quote
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.