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
– Qirel, Commented Nov 29, 2016 at 12:33

Community · Accepted Answer · 2017-05-23 11:52:59Z

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?
What do you mean by "make addition with old value and new value"?
Do you want that in a separate row, or to update the existing one?
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.

Anijit Sau · Accepted Answer · 2016-11-29 12:37:51Z

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

Collectives™ on Stack Overflow

Update data in my db

2 Answers 2

The more secure approach

References

6 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

The more secure approach

References

6 Comments

Comments

Linked

Related