Assumptions<\/h2>\n\n\n\n
We’re going to assume you already have a development environment setup with PHP, MySQL, and Apache and that you have a database setup with a user to edit.<\/p>\n\n\n\n
Here is our database schema<\/p>\n\n\n\n And here are our test users<\/p>\n\n\n\n Let’s create a file on our server called display.php<\/strong>. In this file, we’ll connect to the database and display all of our users on the page.<\/p>\n\n\n Next, we will query for all of the users.<\/p>\n\n\n Then we will output all of the user information in a simple table. First, let’s set up our basic table.<\/p>\n\n\n Now, we need to populate the table with the data from our $results array. We’ll loop through the results and add them to the table.<\/p>\n\n\n Next, we’ll modify our table to add an edit link to each row.<\/p>\n\n\n At this point I’m just going to add a little bit of CSS to make our table look better then we’ll be done with the display.php<\/strong> file.<\/p>\n\n\n Your table should look like this.<\/p>\n\n\n\n Next, we need to make our edit.php<\/strong> file. This file will display the data for the selected row and allow you to change it. We’ll start with our database connection.<\/p>\n\n\n Next, we’ll grab the variable for ID<\/strong> from our URL. If you click at the edit link from display.php you’ll see something like this in the address bar: http:\/\/localhost:8888\/edit.php?id=3<\/strong> . We need to grab the number 3 and query for that record.<\/p>\n\n\n Now we need to query for that record. To do that we’ll create a prepared statement. What you will see is a query that has a question mark in it. That question mark is a template. The ? will be replaced by the value that we include in bind_parm. In bind param you see: $stmt->bind_param(“i”, $id); The first parameter, “i”, is saying that the first parameter passed should be an integer. The $id is passing in the value we grabbed from the get variable.<\/p>\n\n\n Next, we need to use the result data to populate our edit form. We’ll set up our form. I also added just a few lines of CSS to change our display.<\/p>\n\n\n Now your form should look something like this.<\/p>\n\n\n\n <\/p>\n\n\n\n Next, we need to get our result and populate the form using $row = $result->fetch_assoc();<\/strong>. Then we’ll fill in our form values. We’re also going to change the input for ID to make it readonly so you cannot change ID.<\/p>\n\n\n Ok. Now when the form is submitted it is sent back to the same page, edit.php<\/strong>. In edit.php we will check for the name and username. If they are present we will update the database.<\/p>\n\n\n There is a lot of code changed above but mostly you want to look inside if(isset($_GET[‘name’]) and isset($_GET[‘username’])) {<\/strong>. We’re simply checking if the GET variables are set and if they are we set up our prepared statement and execute it. You’ll notice the bind_params: $update->bind_param(“ssi”, $_GET[‘name’], $_GET[‘username’], $id);<\/strong>. Here we’re saying the first two params are string and the last is integer. Then we add those variables in order.<\/p>\n\n\n\n Now your form should look like this.<\/p>\n\n\n\n <\/p>\n\n\n\n Then we can edit the row and click submit to see our changes reflected in the screen. In my case, I changed Adrienne’s username to “bob”.<\/p>\n\n\n\n The last thing I want to do is add a link back to the main page, display.php<\/strong>.<\/p>\n\n\n![\"\"](\"https:\/\/teamtutorials.com\/wp-content\/uploads\/2019\/11\/mysql_01-1-1024x476.jpg\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/teamtutorials.com\/wp-content\/uploads\/2019\/11\/mysql_02-1-1024x513.jpg\")
<\/figure>\n\n\n\nSetup the Database Connection<\/h2>\n\n\n\n
\n<?php\n$servername = "localhost";\n$username = "root";\n$password = "root";\n$dbname = "test_db";\n\n$conn = new mysqli($servername, $username, $password, $dbname);\n\nif ($conn->connect_error) {\n die("MySQL Connection failed: " . $conn->connect_error);\n}\n\n$conn->close();\n?>\n\n<\/pre><\/div>\n\n\n\n<?php\n$servername = "localhost";\n$username = "root";\n$password = "root";\n$dbname = "test_db";\n\n$conn = new mysqli($servername, $username, $password, $dbname);\n\nif ($conn->connect_error) {\n die("MySQL Connection failed: " . $conn->connect_error);\n}\n\nif (!$results = $conn->query("SELECT * FROM users")){\n echo "Select statement failed: (" . $mysqli->errno . ") " . $mysqli->error;\n}\n\n$conn->close();\n?>\n\n<\/pre><\/div>\n\n\n\n<?php\n$servername = "localhost";\n$username = "root";\n$password = "root";\n$dbname = "test_db";\n\n$conn = new mysqli($servername, $username, $password, $dbname);\n\nif ($conn->connect_error) {\n die("MySQL Connection failed: " . $conn->connect_error);\n}\n\nif (!$results = $conn->query("SELECT * FROM users")){\n echo "Select statement failed: (" . $mysqli->errno . ") " . $mysqli->error;\n}\n\n$conn->close();\n?>\n\n<table>\n <tr>\n <th>ID<\/th>\n <th>Name<\/th>\n <th>Username<\/th>\n <\/tr>\n <tr>\n <td><\/td>\n <td><\/td>\n <td><\/td>\n <\/tr>\n<\/table>\n\n<\/pre><\/div>\n\n\n\n<?php\n$servername = "localhost";\n$username = "root";\n$password = "root";\n$dbname = "test_db";\n\n$conn = new mysqli($servername, $username, $password, $dbname);\n\nif ($conn->connect_error) {\n die("MySQL Connection failed: " . $conn->connect_error);\n}\n\nif (!$results = $conn->query("SELECT * FROM users")){\n echo "Select statement failed: (" . $mysqli->errno . ") " . $mysqli->error;\n}\n\n$conn->close();\n?>\n\n<table>\n <tr>\n <th>ID<\/th>\n <th>Name<\/th>\n <th>Username<\/th>\n <\/tr>\n <?php while($row = $results->fetch_assoc()){ ?>\n <tr>\n <td><?= $row['id']?><\/td>\n <td><?= $row['name']?><\/td>\n <td><?= $row['username']?><\/td>\n <\/tr>\n<?php }?>\n<\/table>\n\n<\/pre><\/div>\n\n\n\n<?php\n$servername = "localhost";\n$username = "root";\n$password = "root";\n$dbname = "test_db";\n\n$conn = new mysqli($servername, $username, $password, $dbname);\n\nif ($conn->connect_error) {\n die("MySQL Connection failed: " . $conn->connect_error);\n}\n\nif (!$results = $conn->query("SELECT * FROM users")){\n echo "Select statement failed: (" . $mysqli->errno . ") " . $mysqli->error;\n}\n\n$conn->close();\n?>\n\n<table>\n <tr>\n <th>ID<\/th>\n <th>Name<\/th>\n <th>Username<\/th>\n <th>actions<\/th>\n <\/tr>\n <?php while($row = $results->fetch_assoc()){ ?>\n <tr>\n <td><?= $row['id']?><\/td>\n <td><?= $row['name']?><\/td>\n <td><?= $row['username']?><\/td>\n <td><a href="edit.php?id=<?= $row['id']?>">edit<\/a><\/td>\n <\/tr>\n<?php }?>\n<\/table>\n\n<\/pre><\/div>\n\n\n\n<style>\n table{\n border-collapse: collapse;\n }\n table th, table td{\n border:1px solid #ccc;\n padding:.3em;\n margin:0;\n }\n table th{\n background: #778899;\n }\n table tr:nth-child(odd) {\n background: #efefef;\n}\n<\/style>\n\n<?php\n$servername = "localhost";\n$username = "root";\n$password = "root";\n$dbname = "test_db";\n\n$conn = new mysqli($servername, $username, $password, $dbname);\n\nif ($conn->connect_error) {\n die("MySQL Connection failed: " . $conn->connect_error);\n}\n\nif (!$results = $conn->query("SELECT * FROM users")){\n echo "Select statement failed: (" . $mysqli->errno . ") " . $mysqli->error;\n}\n\n$conn->close();\n?>\n\n<table>\n <tr>\n <th>ID<\/th>\n <th>Name<\/th>\n <th>Username<\/th>\n <th>actions<\/th>\n <\/tr>\n <?php while($row = $results->fetch_assoc()){ ?>\n <tr>\n <td><?= $row['id']?><\/td>\n <td><?= $row['name']?><\/td>\n <td><?= $row['username']?><\/td>\n <td><a href="edit.php?id=<?= $row['id']?>">edit<\/a><\/td>\n <\/tr>\n<?php }?>\n<\/table>\n<\/pre><\/div>\n\n\n![\"\"](\"https:\/\/teamtutorials.com\/wp-content\/uploads\/2019\/11\/mysql_03.jpg\")
<\/figure>\n\n\n\nEditing the Data Using PHP and MySQLi<\/h2>\n\n\n\n
\n<?php\n$servername = "localhost";\n$username = "root";\n$password = "root";\n$dbname = "test_db";\n\n$conn = new mysqli($servername, $username, $password, $dbname);\n\nif ($conn->connect_error) {\n die("MySQL Connection failed: " . $conn->connect_error);\n}\n\n$conn->close();\n?>\n<h2>Edit Record<\/h2>\n<\/pre><\/div>\n\n\n\n<?php\n$servername = "localhost";\n$username = "root";\n$password = "root";\n$dbname = "test_db";\n\n$conn = new mysqli($servername, $username, $password, $dbname);\n\nif ($conn->connect_error) {\n die("MySQL Connection failed: " . $conn->connect_error);\n}\n\n$id = $_GET["id"];\n\n$conn->close();\n?>\n<h2>Edit Record<\/h2>\n<\/pre><\/div>\n\n\n\n<?php\n$servername = "localhost";\n$username = "root";\n$password = "root";\n$dbname = "test_db";\n\n$conn = new mysqli($servername, $username, $password, $dbname);\n\nif ($conn->connect_error) {\n die("MySQL Connection failed: " . $conn->connect_error);\n}\n\n$id = $_GET["id"];\n\n\/\/prepared prepared statement\n$stmt = $conn->prepare("SELECT * FROM users WHERE id = ?");\n\/\/bind the paremeters\n$stmt->bind_param("i", $id);\n\/\/execute the statement\n$stmt->execute();\n\nif($stmt->affected_rows === 0) {\n echo ('No rows updated');\n}else{\n $result = $stmt->get_result();\n}\n\n$stmt->close();\n$conn->close();\n?>\n<h2>Edit Record<\/h2>\n<\/pre><\/div>\n\n\n\n<style>\n label{\n width:80px;\n display:inline-block;\n }\n div{\n margin-bottom:.5em;\n }\n<\/style>\n\n<?php\n$servername = "localhost";\n$username = "root";\n$password = "root";\n$dbname = "test_db";\n\n$conn = new mysqli($servername, $username, $password, $dbname);\n\nif ($conn->connect_error) {\n die("MySQL Connection failed: " . $conn->connect_error);\n}\n\n$id = $_GET["id"];\n\n\/\/prepared prepared statement\n$stmt = $conn->prepare("SELECT * FROM users WHERE id = ?");\n\/\/bind the paremeters\n$stmt->bind_param("i", $id);\n\/\/execute the statement\n$stmt->execute();\n\nif($stmt->affected_rows === 0) {\n echo ('No rows updated');\n}else{\n $result = $stmt->get_result();\n}\n\n$stmt->close();\n$conn->close();\n?>\n<h2>Edit Record<\/h2>\n\n<form method="post" action="edit.php">\n <div><label>id<\/label><input type="text" name="id" id="id" value=""\/><\/div>\n <div><label>name<\/label><input type="text" name="name" id="name" value=""\/><\/div>\n <div><label>username<\/label><input type="text" name="username" id="username" value=""\/><\/div>\n <input type="submit" value="Submit">\n<\/form>\n\n<\/pre><\/div>\n\n\n![\"\"](\"https:\/\/teamtutorials.com\/wp-content\/uploads\/2019\/11\/mysql_04.jpg\")
<\/figure>\n\n\n\n\n<style>\n label{\n width:80px;\n display:inline-block;\n }\n div{\n margin-bottom:.5em;\n }\n<\/style>\n\n<?php\n$servername = "localhost";\n$username = "root";\n$password = "root";\n$dbname = "test_db";\n\n$conn = new mysqli($servername, $username, $password, $dbname);\n\nif ($conn->connect_error) {\n die("MySQL Connection failed: " . $conn->connect_error);\n}\n\n$id = $_GET["id"];\n\n\/\/prepared prepared statement\n$stmt = $conn->prepare("SELECT * FROM users WHERE id = ?");\n\/\/bind the paremeters\n$stmt->bind_param("i", $id);\n\/\/execute the statement\n$stmt->execute();\n\nif($stmt->affected_rows === 0) {\n echo ('No rows updated');\n}else{\n $result = $stmt->get_result();\n}\n\n$stmt->close();\n$conn->close();\n\n$row = $result->fetch_assoc();\n?>\n<h2>Edit Record<\/h2>\n\n<form method="post" action="edit.php">\n <div><label>id<\/label><input type="text" name="id" id="id" value="<?= $row['id']?>" readonly\/><\/div>\n <div><label>name<\/label><input type="text" name="name" id="name" value="<?= $row['name']?>"\/><\/div>\n <div><label>username<\/label><input type="text" name="username" id="username" value="<?= $row['username']?>"\/><\/div>\n <input type="submit" value="Submit">\n<\/form>\n\n<\/pre><\/div>\n\n\n\n<style>\n label{\n width:80px;\n display:inline-block;\n }\n div{\n margin-bottom:.5em;\n }\n<\/style>\n\n<?php\n$servername = "localhost";\n$username = "root";\n$password = "root";\n$dbname = "test_db";\n\n$conn = new mysqli($servername, $username, $password, $dbname);\n\nif ($conn->connect_error) {\n die("MySQL Connection failed: " . $conn->connect_error);\n}\n\n$id = $_GET["id"];\n\nif(isset($_GET['name']) and isset($_GET['username'])) {\n \/\/ prepare the update statement\n $update = $conn->prepare("UPDATE users SET users.name = ?, users.username = ? WHERE users.id = ?;");\n \/\/bind the paremeters\n $update->bind_param("ssi", $_GET['name'], $_GET['username'], $id);\n \/\/execute the statement\n $update->execute();\n\n if($update->affected_rows === 0) {\n echo ('No rows updated');\n }else{\n echo ("{$update->affected_rows} rows updated");\n }\n}\n\n\/\/prepared prepared statement\n$stmt = $conn->prepare("SELECT * FROM users WHERE id = ?");\n\/\/bind the paremeters\n$stmt->bind_param("i", $id);\n\/\/execute the statement\n$stmt->execute();\n\nif($stmt->affected_rows === 0) {\n echo ('No rows updated');\n}else{\n $result = $stmt->get_result();\n}\n\n$stmt->close();\n$conn->close();\n\n$row = $result->fetch_assoc();\n?>\n<h2>Edit Record<\/h2>\n\n<form method="get" action="edit.php">\n <div><label>id<\/label><input type="text" name="id" id="id" value="<?= $row['id']?>" readonly\/><\/div>\n <div><label>name<\/label><input type="text" name="name" id="name" value="<?= $row['name']?>"\/><\/div>\n <div><label>username<\/label><input type="text" name="username" id="username" value="<?= $row['username']?>"\/><\/div>\n <input type="submit" value="Submit">\n<\/form>\n\n<\/pre><\/div>\n\n\n![\"\"](\"https:\/\/teamtutorials.com\/wp-content\/uploads\/2019\/11\/mysql_05.jpg\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/teamtutorials.com\/wp-content\/uploads\/2019\/11\/mysql_06.jpg\")
<\/figure>\n\n\n\n\n<style>\n label{\n width:80px;\n display:inline-block;\n }\n div{\n margin-bottom:.5em;\n }\n<\/style>\n\n<?php\n$servername = "localhost";\n$username = "root";\n$password = "root";\n$dbname = "test_db";\n\n$conn = new mysqli($servername, $username, $password, $dbname);\n\nif ($conn->connect_error) {\n die("MySQL Connection failed: " . $conn->connect_error);\n}\n\n$id = $_GET["id"];\n\nif(isset($_GET['name']) and isset($_GET['username'])) {\n \/\/ prepare the update statement\n $update = $conn->prepare("UPDATE users SET users.name = ?, users.username = ? WHERE users.id = ?;");\n \/\/bind the paremeters\n $update->bind_param("ssi", $_GET['name'], $_GET['username'], $id);\n \/\/execute the statement\n $update->execute();\n\n if($update->affected_rows === 0) {\n echo ('No rows updated');\n }else{\n echo ("{$update->affected_rows} rows updated");\n }\n}\n\n\/\/prepared prepared statement\n$stmt = $conn->prepare("SELECT * FROM users WHERE id = ?");\n\/\/bind the paremeters\n$stmt->bind_param("i", $id);\n\/\/execute the statement\n$stmt->execute();\n\nif($stmt->affected_rows === 0) {\n echo ('No rows updated');\n}else{\n $result = $stmt->get_result();\n}\n\n$stmt->close();\n$conn->close();\n\n$row = $result->fetch_assoc();\n?>\n<h2>Edit Record<\/h2>\n\n<form method="get" action="edit.php">\n <div><label>id<\/label><input type="text" name="id" id="id" value="<?= $row['id']?>" readonly\/><\/div>\n <div><label>name<\/label><input type="text" name="name" id="name" value="<?= $row['name']?>"\/><\/div>\n <div><label>username<\/label><input type="text" name="username" id="username" value="<?= $row['username']?>"\/><\/div>\n <input type="submit" value="Submit">\n<\/form>\n<a href="display.php?id=<?= $row['id']?>">Back to Display<\/a>\n\n<\/pre><\/div>\n\n\n
![\"\"](\"https:\/\/teamtutorials.com\/wp-content\/uploads\/2019\/11\/mysql_07.jpg\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/teamtutorials.com\/wp-content\/uploads\/2019\/11\/mysql_08.jpg\")
<\/figure>\n\n\n\n