Updating MySQL database via PHP

Question

In one page of my website, you can see this simple PHP code:

$con = mysqli_connect('localhost','marostic_dwa','paswd','marostic_dwa');

if(!$con)
{
    echo 'Connessione al Server fallita!';
}

$magazzino = $_POST["magazzino"];
$tipologia_cliente = $_POST["tipologia_cliente"];
$id_privato = $_POST["id_privato"];
$id_azienda = $_POST["id_azienda"];
$data_creazione = $_POST["data"];
/* Dichiarazione variabili per l'inserimento degli articoli nel documento di trasporto */
$id_articolo1 = $_POST["id_articolo1"];
$quantita1 = $_POST["quantita1"];
$id_articolo2 = $_POST["id_articolo2"];
$quantita2 = $_POST["quantita2"];
$id_articolo3 = $_POST["id_articolo3"];
$quantita3 = $_POST["quantita3"];
$id_articolo4 = $_POST["id_articolo4"];
$quantita4 = $_POST["quantita4"];
$id_articolo5 = $_POST["id_articolo5"];
$quantita5 = $_POST["quantita5"];
//ALL POSTS....

if ($tipologia_cliente == "Azienda"){
    $id_cliente_st = $id_azienda;
} else {
    $id_cliente_st = $id_privato;
}

if ($magazzino == "Magazzino-001"){
    $sql = "UPDATE giacenze SET quantita=quantita-$quantita1 WHERE id='$id_articolo1'";
    
} else {
    $sql1 = "UPDATE giacenze_m2 SET quantita=quantita-$quantita1 WHERE id=$id_articolo1";
    $sql2 = "UPDATE giacenze_m2 SET quantita=quantita-$quantita2 WHERE id=$id_articolo2";
    $sql3 = "UPDATE giacenze_m2 SET quantita=quantita-$quantita3 WHERE id=$id_articolo3";
    $sql4 = "UPDATE giacenze_m2 SET quantita=quantita-$quantita4 WHERE id=$id_articolo4";
    $sql5 = "UPDATE giacenze_m2 SET quantita=quantita-$quantita5 WHERE id=$id_articolo5";
    $sql6 = "UPDATE giacenze_m2 SET quantita=quantita-$quantita6 WHERE id=$id_articolo6";
    $sql7 = "UPDATE giacenze_m2 SET quantita=quantita-$quantita7 WHERE id=$id_articolo7";
    $sql8 = "UPDATE giacenze_m2 SET quantita=quantita-$quantita8 WHERE id=$id_articolo8";
    $sql9 = "UPDATE giacenze_m2 SET quantita=quantita-$quantita9 WHERE id=$id_articolo9";
    $sql10 = "UPDATE giacenze_m2 SET quantita=quantita-$quantita10 WHERE id=$id_articolo10";
    $sql11 = "UPDATE giacenze_m2 SET quantita=quantita-$quantita11 WHERE id=$id_articolo11";
    $sql12 = "UPDATE giacenze_m2 SET quantita=quantita-$quantita12 WHERE id=$id_articolo12";
    $sql13 = "UPDATE giacenze_m2 SET quantita=quantita-$quantita13 WHERE id=$id_articolo13";
    $sql14 = "UPDATE giacenze_m2 SET quantita=quantita-$quantita14 WHERE id=$id_articolo14";
    $sql15 = "UPDATE giacenze_m2 SET quantita=quantita-$quantita15 WHERE id=$id_articolo15";
    $sql16 = "UPDATE giacenze_m2 SET quantita=quantita-$quantita16 WHERE id=$id_articolo16";
    $sql17 = "UPDATE giacenze_m2 SET quantita=quantita-$quantita17 WHERE id=$id_articolo17";
    $sql18 = "UPDATE giacenze_m2 SET quantita=quantita-$quantita18 WHERE id=$id_articolo18";
    $sql19 = "UPDATE giacenze_m2 SET quantita=quantita-$quantita19 WHERE id=$id_articolo19";
    $sql20 = "UPDATE giacenze_m2 SET quantita=quantita-$quantita20 WHERE id=$id_articolo20"; 
    
}

$sql = "INSERT INTO documenti_trasporto (magazzino, tipologia_cliente, id_cliente, data_creazione, imposta, imponibile_netto, id_articolo1, quantita1, id_articolo2, quantita2, id_articolo3, quantita3, id_articolo4, quantita4, id_articolo5, quantita5, id_articolo6, quantita6, id_articolo7, quantita7, id_articolo8, quantita8, id_articolo9, quantita9, id_articolo10, quantita10, id_articolo11, quantita11, id_articolo12, quantita12, id_articolo13, quantita13, id_articolo14, quantita14, id_articolo15, quantita15, id_articolo16, quantita16, id_articolo17, quantita17, id_articolo18, quantita18, id_articolo19, quantita19, id_articolo20, quantita20) VALUES ('$magazzino','$tipologia_cliente','$id_cliente_st','$data_creazione','$imposta','$imponibile_netto','$id_articolo1','$quantita1','$id_articolo2','$quantita2','$id_articolo3','$quantita3','$id_articolo4','$quantita4','$id_articolo5','$quantita5','$id_articolo6','$quantita6','$id_articolo7','$quantita7','$id_articolo8','$quantita8','$id_articolo9','$quantita9','$id_articolo10','$quantita10','$id_articolo11','$quantita11','$id_articolo12','$quantita12','$id_articolo13','$quantita13','$id_articolo14','$quantita14','$id_articolo15','$quantita15','$id_articolo16','$quantita16','$id_articolo_17','$quantita17','$id_articolo18','$quantita18','$id_articolo19','$quantita19','$id_articolo20','$quantita20')";

I would like modify quantity of a product in my MySQL database, via a PHP page. But that doesn't work.

In this code you can see all $_POSTS of inputs.

"quantita" is "quantity" in english.

well you have to execute the sql statement somehow. plus, you're vulnerable to sql injection attacks — Kevin, Jul 28 '20 at 07:21
Pls clarify what does not work exactly means! Pls also share the rest of the code with us as well. — Shadow, Jul 28 '20 at 07:38
You urgently need to learn about `for` loops, arrays, and iteration. This code is excessively repetitive. — tadman, Jul 28 '20 at 08:05
If you're just getting started with PHP and want to build applications, I'd strongly recommend looking at various [development frameworks](https://www.cloudways.com/blog/best-php-frameworks/) to see if you can find one that fits your style and needs. They come in various flavors from lightweight like [Fat-Free Framework](https://fatfreeframework.com/) to far more comprehensive like [Laravel](http://laravel.com/). These give you concrete examples to work from and guidance on how to write your code and organize your project's files. — tadman, Jul 28 '20 at 08:06
Is the "id" column numeric or string? It is not clear since in the WHERE clause of the first UPDATE ($sql), the value is enclosed in single quotes, but in the rest of the UPDATE statements it isn't. Although MySQL will do an internal type conversion before executing the statements, it is still good practice to enclose strings in quotes for robustness and code clarity. — alds, Jul 28 '20 at 08:28

score 0 · Answer 1 · answered Jul 28 '20 at 08:01

0

As far as i can see you are not executing your query just storing it in a variable won't help you try to execute it like this:

$result = mysqli_query($con, $sql);//$con will be your database connection variable

After this try to print some message on the bases of its result.

if($result){//if your query executes properly it will be true and print success message otherwise it will print error message
echo "Record updated successfully";
}
else
{
echo "Error Occurred";
}

I hope it will be beneficial.

answered Jul 28 '20 at 08:01

Emraan Khalid

7
2

Note: The [object-oriented interface to `mysqli`](https://www.php.net/manual/en/mysqli.quickstart.connections.php) is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface where missing a single `i` can cause trouble. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is an artifact from the PHP 4 era and should not be used in new code. Additionally the procedural interface has less rigorous error checking and reporting, frustrating debugging efforts. – tadman Jul 28 '20 at 08:05
Tip: A lot of problems can be detected and resolved by [enabling exceptions in `mysqli`](https://stackoverflow.com/questions/14578243/turning-query-errors-to-exceptions-in-mysqli) so errors resulting from simple mistakes made aren’t easily ignored. Without exceptions you must pay close attention to return values, many of these indicate problems you must resolve or report to the user. Exceptions allow for more sophisticated flow control as they can “bubble up” to other parts of your code where it’s more convenient to handle them. – tadman Jul 28 '20 at 08:06
can you share what are you receiving any error or warning? or any other message – Emraan Khalid Jul 28 '20 at 10:37

Updating MySQL database via PHP

1 Answers1