[SOLVED] Select All and effect only X amount

[SirChick]

Hey guys,

 

Is there a way to do some thing like this:

 

Select all users ... then UPDATE a random 20 % of the found users from A to a B..in a field lets say named Test ?

[Barand]

You can add LIMIT x to limit the update to x records

[Wuhtzu]

Do you want exactly 20% or just around 20%?

 

Because if you want around 20% you could do something like this:

 

<?php

//Get the users
$get_users = mysql_query("SELECT * FROM table");

//Loop through all the records
while($user = mysql_fetch_array($get_users)) {

  //Do something which has a 20% chance of succeeding.
  //Choose a random number between 1 and 5
  $rand = rand(1,5);

  //If the number is 3 (which there is a 20% chance of it being) update the user
  if($rand == 3) {
    $update_user = mysql_query("UPDATE QUERY");
  }

}

?>

 

This will statistically cause 20% of all your users (selected by the query) to be updated. Of course this will _around_ 20% as I said... more precise the more users

[SirChick]

20 % was a number i put in for example it could be any number chosen from something else earlier back.

 

  //Do something which has a 20% chance of succeeding.
  //Choose a random number between 1 and 5
  $rand = rand(1,5);

  //If the number is 3 (which there is a 20% chance of it being) update the user
  if($rand == 3) {
    $update_user = mysql_query("UPDATE QUERY");
  }

 

How exactly does this update 20% of users... just curious cos i dont understand that part

[Wuhtzu]

I presumed you wanted to do something like a "lottery". Choose a given percentage of your users and upgrade their memberships or something like that. That of course had to be random and somehow you must randomly choose 200% of your users for upgrade. (if that was what you wanted to do).

 

This I did by looping over all users and and for each user pick a random number between 1 and 5. If their number was 3 they will get the upgrade. Picking 3 from 1 to 5 has a 20% chance of succeeding so in the end 20% of the users would pick the number 3 and get the upgrade...

 

------------------------------

 

But since that is not what you are/were after you need to explain your self a little better. 

[SirChick]

Would that work automated .. or only when the the user views that .php script in the browser?

[Wuhtzu]

The code I posted select all users and updates "around 20%" of them all at once... so letting this happen when a user requests the script will cause all users to get updated over and over again.

 

So this script should probably be called from your administrative pages where you can request it whenever you like to hand out something random.

 

You could also look into cronjobs if you want to run it every once in a while automated...

[SirChick]

yeh cronjobs is what im thinking as itll need to be daily.

 

ill give it a try see what happens

 

although just thought its not always going to be 20% the % is meant to be a random amount also

[Wuhtzu]

Then you can just play around with the percents:

 

20% rand(1,5)

33% rand(1,3)

50% rand(1,2)

10% rand(1,10)

 

You could also get all the ID's of your users, store them in an array, choose x random ID's/records from that array and update those... But that requires some work of your table of ID's is not contiguous (e.g. 1,2,3,19,20,21,23,24 because some users have been deleted and the gabs not filled in).

[SirChick]

thats a point with the first one you provided will it just update the first 20% of all users.. or any 20% ?

 

the reason i ask is cos i need the latter...

[sasa]

try

UPDATE `users`  SET name='B' WHERE name='A' ORDER BY RAND() LIMIT 5

[SirChick]

<?
If ($Result == 'B'){

//Get the users
$get_users = mysql_query("SELECT * FROM userregistration WHERE CountryID='1' AND IllnessType='0'");
if (!$get_users)
    die("Error: " . mysql_error());

//Loop through all the records
while($user = mysql_fetch_array($get_users)) {

  //Do something which has a 20% chance of succeeding.
  //Choose a random number between 1 and 5
  $rand = rand(1,1);

  //If the number is 3 (which there is a 20% chance of it being) update the user
  if($rand == 1) {
    $update_user = "UPDATE userregistration SET IllnessType='Winter Flu' And TimeOfIllness='$Date' WHERE CountryID='1'";
		$result = mysql_query($update_user) or die(mysql_error());
		}

}

}

?>

 

Got this at the moment.. and as you can see i put rand as 100% so that itll always success but in my database the update is not occuring even though no error occurs..

[Barand]

try

if($rand == 1) {
    $update_user = "UPDATE userregistration SET IllnessType='Winter Flu' And TimeOfIllness='$Date' WHERE CountryID='1'";
		$result = mysql_query($update_user) or die(mysql_error());
    echo $update_user, '<br>';
		}

 

to see if it being executed

[SirChick]

UPDATE userregistration SET IllnessType='Winter Flu' And TimeOfIllness='2007-10-21 22:20:06' WHERE CountryID='1'

UPDATE userregistration SET IllnessType='Winter Flu' And TimeOfIllness='2007-10-21 22:20:06' WHERE CountryID='1'

UPDATE userregistration SET IllnessType='Winter Flu' And TimeOfIllness='2007-10-21 22:20:06' WHERE CountryID='1'

 

the fields are still empty in my database though,there are only 3 users also...