Best way to update a single column in MySQL table?

Question

So im fairly new to coding, and have just started on a project for fun. I try to build a RPG game, and i have got to the point where players can fight and take damage. So i want to add a passive health regeneration.

So i was thinking that i would use a conjob to request a php file every 10min and update all players HP with +10.

What i have got with my very poor MySQL knowlege is;

<?php
/* 
 * hp_reg.php, auto updater +10hp to players.
 */

    //DATABAS CONNECTION
    $dbserver="my";
    $dbusername ="db";
    $dbpassword ="conection";
    $db ="information";

    //CREATE CONNECTION
    $conn = new mysqli($dbserver, $dbusername, $dbpassword, $db);
    $query = "SELECT health FROM users";
    $result = mysqli_query($conn, $query);
    $row = mysqli_fetch_assoc($query);

    $newhp = $result + 10;
    $sql = "INSERT INTO users(health) VALUES ($newhp)";

Wich clearly will not work at all! Any tips on what to change in order to get it to work? All pointers are much appreciated.

$query = "UPDATE users SET usersname='john'"; – Angel Jan 28 '16 at 17:52 — Angel, Jan 28 '16 at 17:52

score 1 · Answer 1 · answered Jan 28 '16 at 17:55

1

To increase the health column of all users by 10, you can execute the following query:

UPDATE users SET health = health + 10

answered Jan 28 '16 at 17:55

Imran

4,582
2
18
37

2

Thanks alot fot the quick reply! :) Much appreciated! – Naxor Jan 28 '16 at 18:44

score 1 · Accepted Answer · answered Jan 28 '16 at 17:55

1

$sql = "UPDATE users SET health = health + 10";

Updated Code

 <?php
/* 
* hp_reg.php, auto updater +10hp to players.
*/

//DATABAS CONNECTION
$dbserver="my";
$dbusername ="db";
$dbpassword ="conection";
$db ="information";

//CREATE CONNECTION
$conn = new mysqli($dbserver, $dbusername, $dbpassword, $db);

$sql = "UPDATE users SET health = health + 10";

$result = mysqli_query($conn, $sql);

answered Jan 28 '16 at 17:55

Nana Partykar

10,556
10
48
77

1

Thanks alot fot your help! :) Much appreciated! – Naxor Jan 28 '16 at 18:45
*Happy Coding Bro* Cheers. !! @Xirez – Nana Partykar Jan 28 '16 at 18:53

score 0 · Answer 3 · edited May 23 '17 at 12:31

Assuming:

you want update health of One user;
each user have an unique id;
the unique id is stored in your database as userID (if not, change it with your correct field name);

you have to try in this way:

$query = "SELECT health FROM users WHERE userID='$userID'";
$result = mysqli_query( $conn, $query );
$row = mysqli_fetch_assoc( $query );

$newhp = $row['health'] + 10;
$query = "UPDATE users SET health='$newhp' WHERE userID='$userID'";
$result = mysqli_query( $conn, $query );

At last, take a look to how preventSQL-injection in PHP and how check if your result is empty

Best way to update a single column in MySQL table?

3 Answers3