strava1 strava1 - 8 days ago 6
MySQL Question

Update data in my db

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}') ");
?>

Answer

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