0

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:

  1. Starting with $profile_outM as a string

  2. I convert to an array and get the number of element

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

$length_profile2array = count($profile2array);

  1. 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"

  1. This way, all users get a different value.

W

2WFR
  • 121
  • 1
  • 8

1 Answers1

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.

Example person
  • 3,198
  • 3
  • 18
  • 45
  • 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). – 2WFR Feb 11 '22 at 10:20
  • @2WFR Oh god! Use ```UPDATE `users` SET `user_profile` = ". array_rand($profile_outM,1)." WHERE `user_UFR`=2``` – Example person Feb 11 '22 at 10:21
  • Thanks. No more error. But it still does not work as it picks up the same value for all users. – 2WFR Feb 11 '22 at 10:42
  • 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). – 2WFR Feb 11 '22 at 10:53
  • @2WFR you could use my solution using `mt_rand` and try – Example person Feb 11 '22 at 11:02
  • Sorry but it does not work – 2WFR Feb 11 '22 at 11:33