Display and update MYSQL data via HTML form via PHP

Question

I am trying to figure out how to display all the rows of a database table in one page, all the values to be editable, and for there to be a single submit button at the end of it. I got half the equation figured out, but for some reason it is still not working.
What I currently have is a table displaying all the contents of a MYSQL table and all fields are editable. There is a submit button for all each field (which is not what I want, but willing to settle if I have to), but upon editing something from the database fields, it brings me to a page that gives me a syntax error:
"Error updating record: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE idnum = '0000'' at line 1"

The following is from FORM.PHP

<?php
include('config.php');
$result = mysqli_query($connect,"SELECT * FROM table123");
?>
<html>
<table>
<?php while ($res = mysqli_fetch_array($result)) { ?>
<tr>
        <form action="test.php" method="post">
            <td><input type="text" name="ret" value="<?php echo $res['ret']; ?>"></td>
            <td><input type="text" name="code" value="<?php echo $res['code']; ?>"></td>
            <td><input type="text" name="status" value="<?php echo $res['status']; ?>"></td>
            <td><input type="hidden" name="idnum" value="<?php echo $res['idnum']; ?>"></td>
            <td><input type="submit" name="update" value="Submit"></td>
        </form>
    </tr>
    <?php } ?>
</table>
</html>

The following is from TEST.PHP

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";
$connect = mysqli_connect($servername, $username, $password, $dbname);
if (!$connect) {
die("Connection failed: " . mysqli_connect_error());
}
if (isset($_POST['update'])) {
$sql = "UPDATE ssoretailerlist SET ret = '$_POST[ret]', code = '$_POST[code]', status = '$_POST[status]', WHERE idnum = '$_POST[idnum]'";
} else {
echo "Nothing was posted";
}
if (mysqli_query($connect, $sql)) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . mysqli_error($connect);
}
mysqli_close($connect);

Remove the comma before the WHERE. If you'd check for [mysqli errors](http://php.net/manual/en/mysqli.error.php), you'd see you have a syntax error. — aynber, Jan 03 '19 at 18:34
Thank you for that. That seems to have did the trick for that one. — hiter202, Jan 03 '19 at 18:55
Any idea on how to make it so that there is only ONE submit button at the bottom of the page rather than for every row? — hiter202, Jan 03 '19 at 18:55
Move the form and submit tags out of the loop, but then you're going to have to do some form tweaking with variable names, so that all of the inputs can be submitted at once. — aynber, Jan 03 '19 at 19:02
Your update query is vulnerable to SQL injection. See https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php for more info — WOUNDEDStevenJones, Jan 03 '19 at 20:08

score -1 · Answer 1 · answered Jan 03 '19 at 20:05

Syntax error is because you have an extra comma. Remove the comma before WHERE and you should be fine.

$sql = "UPDATE ssoretailerlist
        SET ret = '$_POST[ret]', code = '$_POST[code]', status = '$_POST[status]'
        WHERE idnum = '$_POST[idnum]'";

There is a submit button for all each field. Instead of creating a new form and submit for every row inside the loop, one them each once manually outside the loop.

<?php
include('config.php');
$result = mysqli_query($connect, "SELECT * FROM table123");
?>
<html>
<table>
<form action="test.php" method="post">
<?php while ($res = mysqli_fetch_array($result)) { ?>
    <tr>
    <td><input type="text" name="ret" value="<?php echo $res['ret']; ?>"/></td>
    <td><input type="text" name="code" value="<?php echo $res['code']; ?>"/></td>
    <td><input type="text" name="status" value="<?php echo $res['status']; ?>"/></td>
    <td><input type="hidden" name="idnum" value="<?php echo $res['idnum']; ?>"/></td>
    </tr>
<?php } ?>
</table>
<input type="submit" name="update" value="Submit"/>
</form>
</html>

You may want to also handle the output you're inserting into the form. If the data has double quotes in it, it may break your HTML. Check out htmlspecialchars(). Based on your column titles I don't think it would, but always good to keep in mind.

However, every single row has the exact same input names. This is a problem. How will it know which ret, code, status, or idnum to choose and associate together? First you want to turn the names into arrays. Then you want to loop through the idnum array and do multiple UPDATE queries accessing the same key location in the other arrays. Post a new question if you get stuck working on that.

And finally your config.php file is pretty necessary. You may want to read this thread about require_once() vs include(). It's good to throw an error and handle it if the include fails instead of continuing to process the rest of the script.

Display and update MYSQL data via HTML form via PHP

1 Answers1