Scary Scary - 16 days ago 4
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

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.

Comments