Update Database

[Daney11]

Basically i want to update my mysql database and want to update ALL rows within it. Say i have like 50 different rows, my peice of code is only pulling out 1 row 50 times. Ive been told to use for loop but im not sure what to do.

My code is:

[code]<?

function Update($db)

{

$strQuery = "SELECT id, non_technical_attributes_stamina, condition, first_name, second_name from players where club <> 'R'";
$result = mysql_query($strQuery,$db) or die(mysql_error());
$myrow = mysql_fetch_array($result);

$id = $myrow['id'];
$stamina = $myrow['non_technical_attributes_stamina'];
$condition = $myrow['condition'];
$first_name = $myrow['first_name'];
$second_name = $myrow['second_name'];

do

{

extract($myrow);

{

if ($stamina <= 50)
$percentage = 0.02;
elseif ($stamina == 51 || $stamina <= 55)
$percentage = 0.03;
elseif ($stamina == 56 || $stamina <= 60)
$percentage = 0.04;
elseif ($stamina == 61 || $stamina <= 65)
$percentage = 0.05;
elseif ($stamina == 66 || $stamina <= 70)
$percentage = 0.06;
elseif ($stamina == 71 || $stamina <= 75)
$percentage = 0.07;
elseif ($stamina == 76 || $stamina <= 80)
$percentage = 0.08;
elseif ($stamina == 81 || $stamina <= 85)
$percentage = 0.09;
elseif ($stamina == 86 || $stamina <= 90)
$percentage = 0.10;
elseif ($stamina == 91 || $stamina <= 95)
$percentage = 0.11;
elseif ($stamina == 96 || $stamina <= 100)
$percentage = 0.12;

}

$newcondition = $condition*$percentage;

$updatedcondition = round($condition+$newcondition);

if ($updatedcondition > 100)
{ $updatedcondition = 100; }
if ($updatedcondition < 10)
{ $updatedcondition = 10; }


$strQuery = "UPDATE `players` SET condition = '$updatedcondition' WHERE id = $id";
mysql_query($strQuery,$db) or die(mysql_error());

}

while ($myrow = mysql_fetch_array($result)); // This Gets The Next Player In The Database

}

Update($db);

?>[/code]

[Daney11]

I need it to update all rows, one after another.

while ($myrow = mysql_fetch_array($result)); // This Gets The Next Player In The Database

i thought this bit of code would have done it but obviously not...

[Ninjakreborn]

I don't like the situation the code is set in, so I can't help with that part.  But I can point you in the right direciton, you want to build your query
have each table you need updated in an array, and each thing updating it in an array, and run it through a foreach loop.  Use count to get the total number, so it foreaches through the proper amount.

[Daney11]

Im not quite sure what you mean,

could you give me a quick example please?

[Ninjakreborn]

For example, you had a table, with a column

Table name - users
fields
id
username
password
firstname
lastname
accesslevel
The access level is set to 1, for all user's.

You redo your entire database structure so now you want all access level's to change to 1
There are 2 ways to do this

UPDATE users SET accesslevel = 2;";
run that through, it will update them ALL to 2.  That is for that kind of situation, for another, if you have a build a bunch of "different" updates, and you are not sure which one's are which.

Then set up 2 arrays, one with column name's, one's with the values they need
like

$table_names = array("user"=>"dave", "user2"=>"mike", "user3"=>"John");
// excuse if it's not proper, right now, I am tired, and not thinking much, just trying to help some people tonight before going to bed.
then run it through a foreach

foreach($table_names as $k=>$v) {
$update = "UPDATE tablename $k = $v;";
$query = mysql_query($update);
}

Shitty example, but hopefully you get the point.  This will take heavy modification, because last time I Tried to do this, it failed miserably, after 6 hours worth of fighting with it, I finally got it to work, but I don't remember "Where" I did this at, or I could provide you with the code.

Unfortunately, I  don't have it right now, so hopefully atleast that will point you in the right direction.

[Daney11]

Hi,

thanks for your time.

I kind of get where you're coming from, but i have 119 rows in my database that i need to pull and modifiy.

Using this example that you posted


$table_names = array("user"=>"dave", "user2"=>"mike", "user3"=>"John");

I would have to do that with all 119 rows?

I get where you coming from yer but i think that would be too long

Thanks again for you help mate.