Editing SQL table with PHP from HTML form

Question

Passed my day working on this project and now my brain can't help me finish. I hope anyone can help me! I'm still learning so you'll find errors. I'm making a database for my teammates. I just created a big form linked to the SQL database. It works. And a page where they can see all the data from an html table. Now I'm working editing table values from a similar form.

This is the php form

<?php
$link = mysqli_connect("localhost", "", "", "");

if($link === false) {
    die("ERROR: " . mysqli_connect_error());
}

$n_ordine = mysqli_real_escape_string($link, $_REQUEST['n_ordine']);
$cliente = mysqli_real_escape_string($link, $_REQUEST['cliente']);
$data = mysqli_real_escape_string($link, $_REQUEST['data']);
$paese = mysqli_real_escape_string($link, $_REQUEST['paese']);
$operatore = mysqli_real_escape_string($link, $_REQUEST['operatore']);
$n_spedizione = mysqli_real_escape_string($link, $_REQUEST['n_spedizione']);
$fornitore = mysqli_real_escape_string($link, $_REQUEST['fornitore']);
$ord_forn = mysqli_real_escape_string($link, $_REQUEST['ord_forn']);
$n_fornitore = mysqli_real_escape_string($link, $_REQUEST['n_fornitore']);
$corriere = mysqli_real_escape_string($link, $_REQUEST['corriere']);
$n_corriere = mysqli_real_escape_string($link, $_REQUEST['n_corriere']);
$riserva = mysqli_real_escape_string($link, $_REQUEST['riserva']);
$marrara = mysqli_real_escape_string($link, $_REQUEST['marrara']);
$note = mysqli_real_escape_string($link, $_REQUEST['note']);
$esito = mysqli_real_escape_string($link, $_REQUEST['esito']);
$rientro = mysqli_real_escape_string($link, $_REQUEST['rientro']);
$spese_forn = mysqli_real_escape_string($link, $_REQUEST['spese_forn']);
$spese_corr = mysqli_real_escape_string($link, $_REQUEST['spese_corr']);
$sostituzione = mysqli_real_escape_string($link, $_REQUEST['sostituzione']);
$n_fatt_orig = mysqli_real_escape_string($link, $_REQUEST['n_fatt_orig']);
$n_storno = mysqli_real_escape_string($link, $_REQUEST['n_storno']);

$sql = "INSERT INTO databasename (n_ordine, cliente, data, paese, operatore, n_spedizione, fornitore, ord_forn, n_fornitore, corriere, n_corriere, riserva, marrara, note, esito, rientro, spese_forn, spese_corr, sostituzione, n_fatt_orig, n_storno) VALUES ('$n_ordine', '$cliente', '$data', '$paese', '$operatore', '$n_spedizione', '$fornitore', '$ord_forn', '$n_fornitore', '$corriere', '$n_corriere', '$riserva', '$marrara', '$note', '$esito', '$rientro', '$spese_forn', '$spese_corr', '$sostituzione', '$n_fatt_orig', '$n_storno')";
if(mysqli_query($link, $sql)){
    echo "OK";
} else{
    echo "ERROR: $sql. " . mysqli_error($link);
}

mysqli_close($link);
?>

Then I created this one to show/edit the table value

<?php

$link = mysqli_connect("localhost", "", "", "");

if (mysqli_connect_error()) {
    die ("There was an error connecting to the database");
} 

$query="SELECT * FROM databasename";

if ($result = mysqli_query($link, $query)) {
    while ($row=mysqli_fetch_array($result)) {
        echo ("<tr><td>$row[n_ordine]</td>");
        echo ("<td>$row[cliente]</td>");
        echo ("<td>$row[data]</td>");
        echo ("<td>$row[paese]</td>");
        echo ("<td>$row[operatore]</td>");
        echo ("<td>$row[n_spedizione]</td>");
        echo ("<td>$row[fornitore]</td>");
        echo ("<td>$row[ord_forn]</td>");
        echo ("<td>$row[n_fornitore]</td>");
        echo ("<td>$row[corriere]</td>");
        echo ("<td>$row[n_corriere]</td>");
        echo ("<td>$row[riserva]</td>");
        echo ("<td>$row[marrara]</td>");
        echo ("<td>$row[note]</td>");
        echo ("<td>$row[esito]</td>");
        echo ("<td>$row[rientro]</td>");
        echo ("<td>$row[spese_forn]</td>");
        echo ("<td>$row[spese_corr]</td>");
        echo ("<td>$row[sostituzione]</td>");
        echo ("<td>$row[n_fatt_orig]</td>");
        echo ("<td>$row[n_storno]</td>");

echo ("<td><a href=\"edit.php?n_ordine=$row[n_ordine]\">Edit</a></td></tr>");
    }
    echo "</table>";
}
?>

Clicking the "edit" button I'll get the form with the right value

<?php

$link = mysqli_connect("localhost", "", "", "");

if($link === false) {
    die("ERROR " . mysqli_connect_error());
}

$num = $_GET['n_ordine']; 
$query = "SELECT * FROM databasename WHERE n_ordine = '$num'"; 
$result = mysqli_query($link,$query);
$row = mysqli_fetch_array($result);
?>

<form action="edit.php" method="post">

<table>

<tr>
<td>N°Ordine:</td>
<td><input type="text" name="n_ordine" value="<?php echo "$row[n_ordine]"; ?>"></td>
</tr>

<tr>
<td>Cliente:</td>
<td><input type="text" name="cliente" value="<?php echo "$row[cliente]"; ?>"></td>
</tr>

<tr>
<td>Data:</td>
<td><input type="text" name="data" value="<?php echo "$row[data]"; ?>"></td>
</tr>

<tr>
<td>Paese:</td>
<td><input type="text" name="paese" value="<?php echo "$row[paese]"; ?>"></td>
</tr>

<tr>
<td>Operatore:</td>
<td><input type="text" name="operatore" value="<?php echo "$row[operatore]"; ?>"></td>
</tr>

    <tr>
<td>Numero Spedizione:</td>
<td><input type="text" name="n_spedizione" value="<?php echo "$row[n_spedizione]"; ?>"></td>
</tr>

    <tr>
<td>Fornitore:</td>
<td><input type="text" name="fornitore" value="<?php echo "$row[fornitore]"; ?>"></td>
</tr>

    <tr>
<td>Ordine Fornitore:</td>
<td><input type="text" name="ord_forn" value="<?php echo "$row[ord_forn]"; ?>"></td>
</tr>

    <tr>
<td>Note Fornitore:</td>
<td><input type="text" name="n_fornitore" value="<?php echo "$row[n_fornitore]"; ?>"></td>
</tr>

    <tr>
<td>Corriere:</td>
<td><input type="text" name="corriere" value="<?php echo "$row[corriere]"; ?>"></td>
</tr>

    <tr>
<td>Note Corriere:</td>
<td><input type="text" name="n_corriere" value="<?php echo "$row[n_corriere]"; ?>"></td>
</tr>

    <tr>
<td>Riserva:</td>
<td><input type="text" name="riserva" value="<?php echo "$row[riserva]"; ?>"></td>
</tr>

    <tr>
<td>Marrara:</td>
<td><input type="text" name="marrara" value="<?php echo "$row[marrara]"; ?>"></td>
</tr>

    <tr>
<td>Note:</td>
<td><input type="text" name="note" value="<?php echo "$row[note]"; ?>"></td>
</tr>

    <tr>
<td>Esito:</td>
<td><input type="text" name="esito" value="<?php echo "$row[esito]"; ?>"></td>
</tr>

    <tr>
<td>Rientro:</td>
<td><input type="text" name="rientro" value="<?php echo "$row[rientro]"; ?>"></td>
</tr>

    <tr>
<td>Spese da Addebitare al Fornitore:</td>
<td><input type="text" name="ospese_forn" value="<?php echo "$row[spese_forn]"; ?>"></td>
</tr>

    <tr>
<td>Spese da Addebitare al Corriere:</td>
<td><input type="text" name="spese_corr" value="<?php echo "$row[spese_corr]"; ?>"></td>
</tr>

    <tr>
<td>Sostituzioni fornitori / Note di Credito e/o Sconti:</td>
<td><input type="text" name="sostituzione" value="<?php echo "$row[sostituzione]"; ?>"></td>
</tr>

    <tr>
<td>N°Fattura Originaria:</td>
<td><input type="text" name="n_fatt_orig" value="<?php echo "$row[n_fatt_orig]"; ?>"></td>
</tr>

    <tr>
<td>N°Nota a Storno:</td>
<td><input type="text" name="n_storno" value="<?php echo "$row[n_storno]"; ?>"></td>
</tr>

</table>
<input type="submit" value="Update">
</form>

Then I got stuck because I tried using a query like this in the edit.php file:

sql = "UPDATE gestionale SET n_ordine = ' ".$n_ordine." ', cliente = ' ".$cliente." ', data = ' ".$data." ', paese = ' ".$paese." ', operatore = ' ".$operatore." ', n_spedizione = ' ".$n_spedizione." ', fornitore = ' ".$fornitore." ', ord_forn = ' ".$ord_forn." ', n_fornitore = ' ".$n_fornitore." ', corriere = ' ".$corriere." ', n_corriere = ' ".$n_corriere." ', riserva = ' ".$riserva." ', marrara = ' ".$marrara." ', note = ' ".$note." ', esito = ' ".$esito." ', rientro = ' ".$rientro." ', spese_forn = ' ".$spese_forn." ', spese_corr = ' ".$spese_corr." ', sostituzione = ' ".$sostituzione." ', n_fatt_orig = ' ".$n_fatt_orig." ', n_storno = ' ".$n_storno." ' WHERE n_ordine = '".$_POST['n_ordine']."'";

if(mysqli_query($link, $sql)){

    echo "Update Done Correctly";

} else{

    echo "ERROR: $sql. " . mysqli_error($link);
}


mysqli_close($link);

And, when I update all, it gives me "Update Done Correctly", not a single error but, if i check the SQL table not a single changes is happend. Probably is something really simple in the "WHERE" condition but I can't see it. I also tried to change the code in this way sql = "UPDATE gestionale SET n_ordine = ' ".$POST['n_ordine']." ' and so on but same.

I hope anyone can see where are my errors.

Thanks a lot

Please change to . I mean, you have $row = array, so to access to some key, you should call $row['key'] - key as a string value. — Vasyl Zhuryk, Oct 15 '19 at 07:43

score 0 · Answer 1 · answered Oct 15 '19 at 07:38

1) You should clear this value before passing it to SQL query

$num = $_GET['n_ordine']; 
$query = "SELECT * FROM databasename WHERE n_ordine = '$num'";

2) You should use quotes while passing string keys from arrays:

$row[n_ordine] -> $row['n_ordine']

3) You can try to use PDO and bind params into the query, because the way you do it right now is not safe. https://www.php.net/manual/en/pdostatement.execute.php It should work

4) If you dont want to use PDO just var_dump($sql); in your last file and paste entire query - we will be able to check what is wrong. You can also paste result of SELECT * from gestionale - it will be helpful

Hello and thanks! I have to clear it in the edit.php file? I know that is not safe right now, i will check your advices as soon as possible! Now I'm trying to fix this problem ): Thanks! — Luchiii, Oct 15 '19 at 08:13

Editing SQL table with PHP from HTML form

1 Answers1