Scary Scary - 11 months ago 41
MySQL Question

update mysql database with form php $stmt

I'm having issues with updating my MySQL database with form inputs. I believe its an issue with the ID or something, but I am unsure.

Here is the full document:

<?php

session_start();

$_SESSION["message"] = '<p class="message">Client updated successfully!</div>';

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "ssl";
$dbh = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);

?>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Contact Manager - Client Directory</title>
<link type="text/css" rel="stylesheet" href="assets/custom/css/main.css">
<link type="image/ico" rel="icon" href="assets/custom/images/favicon.ico">
</head>
<body>
<div id="container">
<div class="header">
<div class="logo">
<h1>Contact Manager</h1>
</div>
<nav>
<ul>
<a href="index.php"><li>Home</li></a>
<a href="clientdirectory.php"><li>Client Directory</li></a>
<a href="admin.php"><li>Admin</li></a>
</ul>
</nav>
</div> <!-- header div -->
<div class="clear"></div>
<div class="content">
<div class="inner-container">
<div class="inner-header">
<h2>Control Panel > Update Clients</h2>
<?php
$stmt = $dbh->prepare('select id, firstname, lastname, username, password, email, phone from users');
$stmt->execute();
$result = $stmt->fetchall(PDO::FETCH_ASSOC);
foreach ($result as $row) {
echo '<div class="employee-inner">';
echo '<div class="employee">';
echo '<h3>ID</h3>' . '<p>' . $row['id'] . '</p>';
echo '<div class="clear"></div>';
echo '</div>';
echo '<form enctype="multipart/form-data" action="updateclients.php" method="POST">';
echo '<input class="update" type="text" name="firstname" placeholder=' . $row['firstname'] . ' required />';
echo "<br />";
echo '<input class="update" type="text" name="lastname" placeholder=' . $row['lastname'] . ' required />';
echo "<br />";
echo '<input class="update" type="text" name="username" placeholder=' . $row['username'] . ' required />';
echo "<br />";
echo "<input class='update' type='password' name='password' placeholder='Password' required />";
echo "<br />";
echo '<input class="update" type="text" name="email" placeholder=' . $row['email'] . ' required />';
echo "<br />";
echo '<input class="update" type="text" name="phone" placeholder=' . $row['phone'] . ' required />';
echo "<br />";
echo '<input class="update-submit" type="submit" name="update" value="Update Client" />';
echo '</form>';
echo '<a href="deleteclients.php?id='.$row['id'].'"><button>Delete Client</button></a>';
echo '</div>';
}
if (isset($_GET['update'])) {
$employeeid = $_GET['id'];
$firstname = $_GET['firstname'];
$lastname = $_GET['lastname'];
$username = $_GET['username'];
$password = $_GET['password'];
$email = $_GET['email'];
$phone = $_GET['phone'];
$encrypted = md5("encrypted".$password);
$stmt = $dbh->prepare("update users set firstname='" . $firstname . "', lastname='" . $lastname . "', username='" . $username . "'. password='" . $password . "', email='" . $email . "', phone='" . $phone . "' values (:firstname, :lastname, :username, :encrypted, :email, :phone);");
$stmt->bindParam(':firstname', $firstname);
$stmt->bindParam(':lastname', $lastname);
$stmt->bindParam(':username', $username);
$stmt->bindParam(':encrypted', $encrypted);
$stmt->bindParam(':email', $email);
$stmt->bindParam(':phone', $phone);
$stmt->execute();
echo '<p class="message">Client updated successfully!</p>';
}
?>
</div>
</div>
<div class="clear"></div>
<footer>
<p>Copyright &copy 2016 Content Manager. All rights reserved.</p>
</footer
</div> <!-- content div -->
</div> <!-- container div -->
</body>
</html>


Any information will help. Thanks and I appreciate it. There are no PHP errors, but it doesn't update the database.

Answer Source

You have bits of both UPDATE and INSERT syntax in your statement. It should just be:

$stmt = $dbh->prepare("update users set firstname=:firstname, lastname=:lastname, username=:username, password=:password, email=:email, phone=:phone
                        where id = :id");

You need to bind an additional parameter:

$stmt->bindParam(':id', $id);

Another problem is that your form uses method="POST". That means all the parameters will be in $_POST, not $_GET, so change all those variables.