PHP updating a table using data from another

Question

I'm new at programming. This week I have learned the basics of HTML, CSS, PHP and MySQL and I am making a simple turn-based strategy game to practice coding (and have fun while doing it!).

Now, I usually look on DuckDuckGo and YouTube when I am stuck but this time I can't seem to find an answer to my question. Here it is, hope you can help:

I am making the file that will be a cronjob to update the resources my players have. In table 1 (named stockpile) I have columns called village_id, wood, stone and iron. In table 2 (named buildings) I have columns called village_id, woodcutters, quarry and mine. Now here is an example of what I'd like the code to do:

Take 'wood' of village1 and update it like this; wood = wood + (x * woodcutter of village1).

I really hope someone can help me! My project is going fine except for this part I can't seem to wrap my head around...

score 0 · Answer 1 · answered Nov 28 '15 at 23:42

0

use a subquery (replace [x] with some value):

UPDATE stockpile s
SET wood = wood + [x] * 
(SELECT woodcutters
    FROM buildings b
    WHERE b.village_id = s.village_id);

answered Nov 28 '15 at 23:42

pBuch

986
1
6
20

score 0 · Answer 2 · edited May 23 '17 at 12:15

You need to SELECT the columns from the table and get the data for the player from those columns, multiply, and update the table. I'm assuming your village_id is a unique index.

$sql = SELECT COUNT(*) FROM table1;
$result = $conn->query($sql);

$sql1 = "SELECT wood FROM table1";
$result1 = $conn->query($sql);
$arr1 = $result1->fetch_assoc();

$sql2 = "SELECT woodcutter FROM table2";
$result2 = $conn->query($sql2);
$arr2 = $result2->fetch_assoc();


for ($i=1;$i<=$result;$i++) {

    $newwood = $arr1[$i] + (x * $arr2[$i]);

    $sql3 = "UPDATE table1 SET wood=" . $newwood . " WHERE id=" . $i;
    $conn->query($sql3);
}

To make it a cron, you need to configure a job with Apache or your other server.

PHP updating a table using data from another

2 Answers2