J. Doe J. Doe - 6 months ago 16
PHP Question

Change Database Values with one submit click PHP

I am tired of this code. Really. I have been working yesterday to get this fixed and it's just not working. I would appriciate if anyone would give them their input.

I found an old CMS with some great features. However, that CMS is from 2008 and is still running on MySQL and is not being updated anymore. So I took a few scripts out of it. One is being able to adjust multiple database rows

on
and
off
with just 1 click on the submit button.

The database looks like this.



The value means on or off. In dutch it is
aan
and
uit
. I'm making a Dutch CMS so I'm creating it in Dutch language for the most part so my customer can understand it too.

The code from the 2008 CMS looks like this: http://pastebin.com/EkmB8rFr (I'm pasting it in Pastebin because it's messy code and SO is too small for this).

So my job is to convert this 2008 code into 2016 code with all the SQL injections fixed and all. Here's my result:

<?php
if(isset($_POST['opslaan'])) {
$stmt = $dbConnection->prepare('SELECT * FROM settings');
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
$id = $_POST[$row["id"]];
$row = $row["id"];
$stmt1 = $dbConnection->prepare('UPDATE settings SET value = ? WHERE id = ?');
$stmt1->bind_param('ss', $id, $id);
$stmt1->execute();
}

//header('Location: index.php?page=instellingen');
} else {
$stmt = $dbConnection->prepare('SELECT * FROM settings ORDER BY id ASC');
$stmt->execute();

$result = $stmt->get_result();
?>
<form method="POST" action="">
<table>
<tr>
<th>Naam</th>
<th>Laatst gewijzigd</th>
<th>Waarde</th>
</tr>
<?php
while ($row = $result->fetch_assoc()) {
?>
<tr>
<td><?php echo $row["code"]; ?></td>
<td><?php echo $row["updated"]; ?></td>
<td>
<select name="<?php echo $row["id"]; ?>">
<option value="aan"<?php if($row["value"] == "aan") {echo ' selected';} ?>>Aan</option>
<option value="uit"<?php if($row["value"] == "uit") {echo ' selected';} ?>>Uit</option>
</select>
</td>
</tr>
<?php } ?>
</table>
<input type="submit" value="Opslaan" name="opslaan">
</form>
<?php
}


This code is just not working like the 2008 code and I just don't know what is going on. The error is that it puts the ID in the database and not the value of the
<select>
.





The output should be like this:


Answer

It looks like some things are mixed up in your update. I think this should work better:

while ($row = $result->fetch_assoc()) {

    $id = $row["id"];                       // get the id from the row
    $value = $_POST[$id];                   // find the corresponding POST value

    $stmt1 = $dbConnection->prepare('UPDATE settings SET value = ? WHERE id = ?');

    $stmt1->bind_param('ss', $value, $id);  // Bind the correct variables here
    $stmt1->execute();
}