Updating a values in mysql
There may be a requirement where the existing data in a My SQL table needs to be modified. This will modify any field value of any My SQL table.
The following code block has a generic SQL syntax of the UPDATE command to modify the data in the My SQL table − The WHERE clause is very useful when you want to update the selected rows in a table.
One way is to create a new table with an AUTO_INCREMENT column instead of the original column, inserting all data from the old into the new table, and then renaming the new and deleting the old.
Another way is to run your update query with a My SQL variable that generates an increasing number for each row (to emulate the ROW_NUMBER() function found in other DBMS systems).
The row/s affected value is reported as 1 if a row is inserted, and 2 if a row is updated, unless the API's Where two rows match the unique keys match, only the first is updated.
This can be unsafe and is not recommended unless you are certain what you are doing.
the $points variable is the user´s points right now.. so example if he had like 5 points, it should be 5 1 = 6..
(What your AFTER UPDATE trigger is really seeing is the value that got stored, which is not necessarily the value from your UPDATE statement if a BEFORE INSERT trigger has mucked with the value, and supplied a different value for that column.) If your intent is to log changes in values, you probably do NOT want the ELSIF, but instead want to run through all the columns to check whether it's value has been changed or not.
To demonstrate, the following will produce the result 6: String concatenation in My SQL requires the CONCAT() function so there is no ambiguity here and My SQL converts the strings to floats and adds them together.
I actually think the reason the initial query wasn't working is most likely because the $points variable was not in fact set to the user's current points.
Instead, the My SQLi or PDO_My SQL extension should be used.
See also My SQL: choosing an API guide and related FAQ for more information.