View Full Version : Help using an array to update a table.

01-27-2007, 08:26 PM
Hi there.
I'm a definite php noob. What I'm trying to do is pull a couple variables off a table. Then update some variables and upload them to the server. I'm very badly confused right now. I keep trying things but I'm in over my head. If anyone could point me in the right direction I'd appreciate it.


$sql = "SELECT id, stats_income, stats_gold, stats_farming, stats_food FROM stats";
$result = mysql_query($sql);
while ($stats = mysql_fetch_array($result));
$stats[$stats[id]] = $stats[id];
$stats_income[$stats[id]] = $stats[stats_income];
foreach ($stats as $key => $val) {
$sql = "UPDATE stats
SET stats_gold = 200000
stats_turns = 200000
stats_food = 21111111
WHERE stats.id='$key'";
$result = mysql_query($sql);

01-28-2007, 03:08 AM
I don't understand what you're trying to do. Could you clarify?


01-28-2007, 04:28 AM
you don't need the select statement if you are just updating your table.

01-28-2007, 07:44 AM
you don't need the select statement if you are just updating your table.

I'm pretty sure he's trying to pull data from a SQL table and then re-enter new information.

01-29-2007, 01:25 AM
Sorry.... Let me try to clarify. What I'd like to do is that when this script is used I'd like it to pull stats_income and stats_farming then update stats_gold and stats_food to equal the original values for those two variables plus stats_income and stats_farming. I'd like to do this for all the entries in the table at once, hence the need for the array which I've never used before. Instead of update stats_gold = 200000 it should say update stats_gold = $stats_income +$stats_gold or something but I was messing around with something.
I hope that cleared it up a little.

Thanks a bunch!

01-29-2007, 02:13 AM
Without asking why you would want to do this (it is generally a bad table design where a column contains a value that is calculated from other columns and you must execute whole table scans to maintain the values), you can do this all at once as guelphdad posted.

If I understand your statement, this should update all the rows in your table at once -

$sql = "UPDATE stats
SET stats_gold = stats_gold + stats_income,
stats_food = stats_food + stats_farming

01-29-2007, 02:15 AM
can you show some sample rows from your table and what you are trying to accomplish?

and no, you don't need to do a select if you are doing an update. you can update your entire table all at once if you need to and do a calculation from other columns while doing it.

01-30-2007, 12:23 AM
Alrighty..... here's an example of a row.

id | stats_population | stats_gold | stats_income | stats_food | stats_farming | stats_food | stats_turn
1 | 11 | 100 | 23 | 80 | 5 | 80 | 0

Shoot that's kinda hard to read sorry.
So what I would like the script to do is to update a new stats_gold that = stats_gold + stats_income, stats_food would be stats_food + stats_farming. I need it to update those stats for every row in the table.

Thanks for everyone whose tried to help and put up with me!