How can I insert and update a number within the same mysql record?

Question

Hi and thanks for taking the time to read this post

I have a customer database and have been trying to add a loyalty points system so that total of order x value = total loyalty points

I have it working so that when an order is received it updates the table_loyalty with the points and that is working fine

$points = $row["price"] * 1000;

$insert = mysql_db_query($db, "INSERT into table_loyalty (username, orderno, points) VALUES ('$username', '$this_orderno', '$points')", $connection);
check_mysql($insert);

However, what I would ideally like to be able to do but can't seem to get it to work (tried a few different ways) is to have a running total so that each additional order builds up the $points rather than add a separate record

I'm no coder which I'm sure is obvious but would appreciate any help.

I have tried this but didn't work:

$points = $row["points"];

$newpoints = $row["price"] * 1000;

$update = mysql_db_query($db, "update table_loyalty set points='$points'+'$newpoints' WHERE username='$username'", $connection);
check_mysql($update);

} else {

$insert = mysql_db_query($db, "INSERT into table_loyalty (username, orderno, points) VALUES ('$username', '$this_orderno', '$newpoints')", $connection);
check_mysql($insert);

It is better to record the points per order and totalling them separately, since any order can be amended, cancelled, fully or partially returned. — Shadow, Feb 16 '19 at 21:04

score 0 · Accepted Answer · edited Feb 17 '19 at 14:47

I think I would do a select to check if there already is a record for the given user name. If there is, get its points then execute an update adding additional points to its retrieved total. If there is no record do an insert. I would love to write the php and queries but I'm just on my phone atm. Hope this helps will check later

(UPDATE) Hi Mark apart from the fact that you might want to keep them separate rows because order could be canceled or have other things happen to it (which could be valid points depending on how 'big' your system is. Let's check how you could get the asked for functionality to work.

Do be aware that mysql_db_query has been deprecated since PHP 5.3 and has been completely removed from PHP 7.0.0. So if you want to be able to have this work on the long run I would recommend using http://php.net/manual/en/book.mysqli.php

What I was proposing is the following:

$selectQuery = "SELECT points FROM table_loyalty WHERE username=" . $username . ";"

$selectResult = mysql_db_query($db, $selectQuery, $connection);

// if no results could be found
if (mysql_num_rows($selectResult) == 0) {
  //Presuming this is the order price?
  $newpoints = $row["price"] * 1000;

  $insertQuery = "INSERT into table_loyalty (username, orderno, points) VALUES ('$username', '$this_orderno', '$newpoints')";
  $insertResult = mysql_db_query($db, $insertQuery, $connection);
  // I personally have no idea what the check_mysql() function does, I presume its a function of you own making? With that I'm making the assumption that it handles the result in some way? 
  check_mysql($insertResult);

//results have been found
} else {
  $existingPoints = 0;
  while ($row = mysql_fetch_assoc($result)) {
    //There is a catch here. If used in this way you need to make sure that every username only has 1 entry in the table_loyalty. So updating only 1 row per user also means you can ONLY have 1 record per user.
    $existingPoints = $row["points"];
  }

  $newPoints = $row["price"] * 1000;
  $totalPoints = $existingPoints + $newPoints;
  $updateQuery = "update table_loyalty set points='$totalPoints' WHERE username='$username'";

  $update = mysql_db_query($db,updateQuery , $connection);
  check_mysql($update);
}

Is this something you can work with? It's not the most Elegant solution but in the basics this is what you might want :).

Thanks (I have updated my post with the code I tried) – Mark Feb 16 '19 at 21:20 — Mark, Feb 16 '19 at 21:20
Updated my answer, Hope it might help – Tjeerd Feb 17 '19 at 09:05 — Tjeerd, Feb 17 '19 at 09:05

How can I insert and update a number within the same mysql record?

1 Answers1