Update data in my db

Question

I'm really bad with mysql db and I need help.

I need to input data UPDATE table old value from table + new data from input.

Here is my php code what I need to change

<?php
$data = json_decode(file_get_contents("php://input"));
$zm = mysql_real_escape_string($data->zlatni_medvjed);
$ck = mysql_real_escape_string($data->crna_kraljica);
$gv = mysql_real_escape_string($data->gricka_vjestica);
$dk = mysql_real_escape_string($data->dva_klasa);

mysql_connect("localhost","root","");
mysql_select_db("medvedgrad");
mysql_query(" INSERT INTO stanje_piva(`zlatni_medvjed`, `crna_kraljica`, `gricka_vjestica`,`dva_klasa`)
VALUES('{$zm}','{$ck}','{$gv}','{$dk}') ");
?>

You're just assuming that the query went through, at the very least add `mysql_query(..) or die(mysql_error());`. And enable general error-reporting, by adding `error_reporting(E_ALL);` `ini_set('display_errors', 1);` at the top of your file. — Qirel, Nov 29 '16 at 12:33

score 1 · Accepted Answer · edited May 23 '17 at 11:52

You really ought to secure your code by using MySQLi or PDO with prepared statments instead. That being said, your issue is using mysql_real_escape_string() before opening the connection.

From the manual of mysql_real_escape_string()

Executing this function without a MySQL connection present will also emit E_WARNING level PHP errors. Only execute this function with a valid MySQL connection present.

This means that you should put your connection on top of your file, making it

mysql_connect("localhost","root","");
mysql_select_db("medvedgrad");

$data = json_decode(file_get_contents("php://input"));
$zm = mysql_real_escape_string($data->zlatni_medvjed);
$ck = mysql_real_escape_string($data->crna_kraljica);
$gv = mysql_real_escape_string($data->gricka_vjestica);
$dk = mysql_real_escape_string($data->dva_klasa);

mysql_query(...);

Dealing with errors

You're also not doing any sort of error-handling or checking. I recommend you add error_reporting(E_ALL); ini_set('display_errors', 1); directly after your opening tag <?php, this would've told you about all warnings and errors. Also, any errors returning from the connection or the query can be caught by mysql_error()

The more secure approach

Use PDO with prepared statements, to prevent SQL injection and using a proper, modern API. mysql_* functions are deprecated since PHP 5.5 (and removed entirely in PHP 7) and you should stop using them if you can.

error_reporting(E_ALL);
ini_set('display_errors', 1);

$mysql_host = "localhost";
$mysql_username = "root";
$mysql_password = "";
$mysql_database = "medvedgrad";

// First we create the connection
$pdo = new PDO("mysql:host=".$mysql_host .";dbname=".$mysql_database .";charset=utf8", $mysql_username, $mysql_password);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  

$data = json_decode(file_get_contents("php://input"));
$zm = $data->zlatni_medvjed;
$ck = $data->crna_kraljica;
$gv = $data->gricka_vjestica;
$dk = $data->dva_klasa;

// Then we prepare, and execute the query
$stmt = $pdo->prepare("INSERT INTO stanje_piva (`zlatni_medvjed`, `crna_kraljica`, `gricka_vjestica`, `dva_klasa`) VALUES (:zm, :ck, :gv, :dk)");
$stmt->execute(array("zm" => $zm, "ck" => $ck, "gv" => $gv, "dk" => $dk));

This is just a quick example, and there are additional things you could do to improve it, but this will prevent SQL injection and is using a proper API. Note that APIs don't mix, so if you have any other mysql_ code, you need to switch that out, too.

References

thanks Quirel I'am did't know that. I am change my code with your code and works perfectly, but how to make addition with old value and new value? — strava1, Nov 29 '16 at 13:05
What do you mean by "*make addition with old value and new value*"? — Qirel, Nov 29 '16 at 13:06
Do you want that in a separate row, or to update the existing one? — Qirel, Nov 29 '16 at 13:13
Then you want to run a `UPDATE` query instead of an `INSERT` one. And if there are more than 1 row in your table, you need to specify a `WHERE` clause too, otherwise it just updates everything. But for starters, you could do something like `UPDATE stanje_piva SET zlatni_medvjed=CONCAT(zlatni_medvjed, ' Your new value here') WHERE (...)` where the `(...)` is replaced by a condition as to what row is updated. — Qirel, Nov 29 '16 at 13:18

score 0 · Answer 2 · answered Nov 29 '16 at 12:37

Make a Unique Key on the table stanje_piva and use the following command INSERT INTO <table_name>(<attributes of the table>) VALUES (<values you want to insert>) ON DUPLICATE KEY UPDATE <column_to_update> = <column_to_update> + <increment/decrement value>;.

For example,

INSERT INTO table (a,b,c) VALUES (1,2,3)
      ON DUPLICATE KEY UPDATE c=c+1;

which is same as

UPDATE table SET c=c+1 WHERE a=1;

for details read here

Update data in my db

2 Answers2

The more secure approach

References