Insert a random value from a php array in MySQL? [duplicate]

Question

I would like to insert a random value from a php array into a mysql table.

My php array is $profile_outM = (3,6,7)

The following command works nicely and sets user_profile to 7 for all users having user_UFR equal to 2:

UPDATE `users` SET `user_profile` = '$profile_outM[3]' WHERE `user_UFR`= 2

But how can I select a random value from $profile_outM ?

Best,

HERE is the solution (might not be very elegant / efficient) but it works:

Starting with $profile_outM as a string
I convert to an array and get the number of element

profile2array = explode(",", $profile_outM);

$length_profile2array = count($profile2array);

Then

"SET @myValues = '$profile_outM'"

"UPDATE `users` SET `user_profile` = SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(@Values, ',', FLOOR(1+RAND()*$length_profile2array))),',',1) WHERE `user_UFR`=2"

This way, all users get a different value.

W

I always use $val = $array[mt_rand(0, count($array) - 1];

Example person
– Example person

2022-02-11 10:08:54 +00:00
Commented Feb 11, 2022 at 10:08 — Example person
– Example person, Commented Feb 11, 2022 at 10:08

Example person · Accepted Answer · 2022-02-11 10:18:10Z

0

You can use array_rand() as @Victorbzh mentioned, or you can use:

$val = $array[mt_rand(0, count($array) - 1];

The above generates a random number using mt_rand($min, $max) ($max is inclusive). Array indexes start from 0, and count() gets the number of elements in the array.

Security Note 1: array_rand() and mt_rand() do not generate cryptographically secure values. If you want cryptographically secure values, use random_int()

Security Note 2: UPDATE `users` SET `user_profile` = '$profile_outM[3]' WHERE `user_UFR`= 2: It seems you are not using prepared statements. Make sure to use prepared statements when you are using input from the user.

edited Feb 11, 2022 at 10:18

answered Feb 11, 2022 at 10:12

Example person

3,4063 gold badges25 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

2WFR Over a year ago

Thanks . But UPDATE `users` SET `user_profile` = array_rand($profile_outM,1) WHERE `user_UFR`=2 does not work. I get an error : FUNCTION up_idex.array_rand does not exist (up_idex is my table).

Example person Over a year ago

@2WFR Oh god! Use UPDATE `users` SET `user_profile` = ". array_rand($profile_outM,1)." WHERE `user_UFR`=2

2WFR Over a year ago

Thanks. No more error. But it still does not work as it picks up the same value for all users.

2WFR Over a year ago

I guess I have to use some SQL functions like UPDATE `users` SET `user_profile` = FLOOR( 1 + RAND( ) *60 ) WHERE `user_UFR`=2. This sets a random number for all users (albeit this is not what I need as I need to pick up a value from an array).

Example person Over a year ago

@2WFR you could use my solution using mt_rand and try

|

Collectives™ on Stack Overflow

Insert a random value from a php array in MySQL? [duplicate]

1 Answer 1

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Linked

Related