Jump to content

A better way to...


Recommended Posts

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Well with a single MySQL query you could do something like:

update mytable, (select @rn:=0) rr set mycol=(@rn:[email protected]+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 ;)

Link to comment
Share on other sites

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!



CREATE FUNCTION my_rand(min INT(11),max INT(11))
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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...