How to Update Data into Database Using PHP/MySQLi

0
243

Today, you will learn How to Update Data into Database Using PHP/MySQLi. This method will show you on how to Update Data into Database with the use of update query statement in PHP and MySQLi.
Below are following step by step guide on how to Update Data Into Database Using PHP and MySQLi.

Lets Begin:

First Step: Create a MySQL Database and name it “userdb“.

Second: Do the following code for creating a table in the database that you have created.

  1. CREATE TABLE IF NOT EXISTS `tbluser` (
  2. `UserID` int(11) NOT NULL AUTO_INCREMENT,
  3. `Name` varchar(30) NOT NULL,
  4. `Username` varchar(30) NOT NULL,
  5. `Pass` varchar(90) NOT NULL,
  6. PRIMARY KEY (`UserID`)

Third Step:Insert the data using the following code below.

  1. INSERT INTO `tbluser` (`UserID`, `Name`, `Username`, `Pass`) VALUES
  2. (1, 'Janno Palacios', 'janobe', 'admin'),
  3. (2, 'Joken villanueva', 'joken', 'joken'),
  4. (3, 'kejie palacios', 'kenjie', 'kenjie');

Fourth Step: Create a CSS file for the layout of the page and name it “style.css“.

  1. #box{
  2. width: 100%;
  3. }
  4. .column {
  5. width: 33.33%;
  6. display: inline-block;
  7. }
  8. .form{
  9. padding: 2px;
  10. width: 100%;
  11. display: inline-block;
  12. }
  13.  
  14. .first-column {
  15. display: inline-block;
  16. width: 100px;
  17. height: 2px;
  18. margin: 2px;
  19. position: inherit;
  20. }
  21. .second-column{
  22. display: inline-block;
  23. width: 150px;
  24. height: 2px;
  25. margin: 2px;
  26. position: inherit;
  27. }
  28. .btn{
  29. width: 50px;
  30. height: 40px;
  31. margin: 2px;
  32. float: left;
  33. }
  34. .clear{
  35. clear: left;
  36. height: 15px;
  37. }
  38.  
  39. .table{
  40. width: 100%;
  41. border: solid 1px #ddd;
  42. }
  43. .table tr,
  44. .table td {
  45. position: inherit;
  46. border: solid 1px #ddd;
  47. }

Fifth Step: Create a landing page and name it “index.php“.

Sixth Step: Do the following code for retrieving data in the database.

  1. <html>
  2. <title>Update Data into Database Using PHP</title>
  3. <head>
  4.  
  5. <a href="http://style.css">http://style.css</a>
  6. &lt;h1 align="center"&gt;List of Users&lt;/h1&gt;
  7. &lt;/head&gt;
  8. &lt;?php
  9. $server="localhost";
  10. $userid ="root";
  11. $Password = "";
  12. $myDB = "userdb";
  13.  
  14. $con = mysqli_connect($server,$userid,$Password,$myDB);
  15.  
  16. echo "Failed to connect to MySQL: " . mysqli_connect_error();
  17. }
  18. ?&gt;
  19. &lt;body&gt;
  20. <div id="box">
  21. <div class="column"></div>
  22. <div class="column">
  23. <table class="table">
  24. <tr>
  25. <td>User ID</td>
  26. <td>Name</td>
  27. <td>Username</td>
  28. <td>Action</td>
  29. </tr>
  30. $sqli="SELECT * FROM tbluser";
  31. $result=mysqli_query($con,$sqli) or die("query error");
  32. while($row=mysqli_fetch_array($result)) {
  33. echo '<tr>';
  34. echo '<td>'.$row['UserID'].'</td>';
  35. echo '<td>'.$row['Name'].'</td>';
  36. echo '<td>'.$row['Username'].'</td>';
  37. echo '<td><a href="edit.php?id='.$row&#91;'UserID'].'">Edit</a></td>';
  38. echo '</tr>';
  39. }
  40. }
  41. ?&gt;</div>
  42. </table>
  43. </div>
  44. <div class="column"></div>
  45. &lt;/div&gt;
  46. &lt;/body&gt;
  47. &lt;/html&gt;
  48. &lt;?php mysqli_close($con); ?&gt;

Seventh Step: Do the following code for filling the corresponding data into the text field.

  1. &lt;?php
  2. $server="localhost";
  3. $userid ="root";
  4. $Password = "";
  5. $myDB = "userdb";
  6.  
  7. $con = mysqli_connect($server,$userid,$Password,$myDB);
  8.  
  9. # code...
  10. echo "Failed to connect to MySQL: " . mysqli_connect_error();
  11. }
  12.  
  13. if (isset($_GET['id'])) {
  14. # code...
  15. $sqli = "SELECT * FROM tbluser WHERE UserID=".$_GET['id'];
  16. $result = mysqli_query($con, $sqli);
  17. $details = mysqli_fetch_assoc($result);
  18. }else{
  19. header('Location: index.php');
  20. }
  21.  
  22. ?&gt;
  23. <a href="http://style.css">http://style.css</a>
  24. &lt;h1 align="center"&gt;Update Users&lt;/h1&gt;
  25. <div id="box">
  26. <div class="column"></div>
  27. <div class="column">
  28. <form class="form" action="edit.php " method="POST">
  29. <label class="first-column ">User ID:</label>
  30. " &gt;
  31. clear">
  32. &lt;label class="first-column "&gt;Name:&lt;/label&gt;
  33. &lt;input class="second-column" type="text" name="name" value="&lt;?php echo $details['Name']; ?&gt;" &gt;
  34. clear">
  35. &lt;label class="first-column"&gt;Username:&lt;/label&gt;
  36. &lt;input class="second-column" type="text" name="username" value="&lt;?php echo $details['Username']; ?&gt;" &gt;
  37. clear">
  38. &lt;label class="first-column "&gt;Password:&lt;/label&gt;
  39. &lt;input class="second-column" type="password" name="pass" value="&lt;?php echo $details['Pass']; ?&gt;" &gt;
  40. clear">
  41. first-column ">
  42. &lt;button class="btn second-column" name="save" type="submit"&gt;Save&lt;/button&gt;
  43. &lt;/form&gt;
  44.  
  45. &lt;h1 align="center"&gt;List of Users&lt;/h1&gt;
  46. &lt;table class="table" &gt;
  47. &lt;tr&gt;
  48. &lt;td&gt;User ID&lt;/td&gt;
  49. &lt;td&gt;Name&lt;/td&gt;
  50. &lt;td&gt;Username&lt;/td&gt;
  51. &lt;td&gt;Action&lt;/td&gt;
  52. &lt;/tr&gt;
  53. &lt;?php list(); ?&gt;
  54. &lt;/table&gt;
  55. &lt;/div&gt;
  56. column">
  57. &lt;/div&gt;
  58. &lt;?php
  59. function list(){
  60. $sqli = "SELECT * FROM `tbluser`";
  61. $result = mysqli_query($con,$sqli) or die("query error");
  62.  
  63. if ($result) {
  64. # code...
  65. while ($row = mysqli_fetch_array($result)) {
  66. # code...
  67. echo '&lt;tr&gt;';
  68. echo '&lt;td&gt;'.$row['UserID'].'&lt;/td&gt;';
  69. echo '&lt;td&gt;'.$row['Name'].'&lt;/td&gt;';
  70. echo '&lt;td&gt;'.$row['Username'].'&lt;/td&gt;';
  71. echo '&lt;td&gt;&lt;a href="edit.php?id='.$row['UserID'].'"&gt;Edit&lt;/a&gt;
  72. &lt;/td&gt;';
  73. echo '&lt;/tr&gt;';
  74. }
  75. }
  76. }
  77. mysqli_close($con);
  78. ?&gt;

Eight Step: Do the following code for updating data in the database.

  1. &lt;?php
  2. if (isset($_POST['save'])) {
  3. # code...
  4. $id = $_POST['UserID'];
  5. $name = $_POST['name'];
  6. $username = $_POST['username'];
  7. $pass = $_POST['pass'];
  8. $sqli = "UPDATE `tbluser` SET `Name`='{$name}', `Username`='{$username}', `Pass`='{$pass}' WHERE `UserID`='{$id}'";
  9. $res = mysqli_query($con,$sqli);
  10.  
  11. if ($res) {
  12. # code...
  13. echo "Data has been updated in the database.";
  14. header('Location: index.php');
  15. }
  16. }
  17. ?&gt;

If you have any questions or suggestion about how to Update Data Into Database Using PHP and MySQLi, please feel free to contact us at our contact page.You can subscribe this site to see more of my tutorials.

Facebook Comments
(Visited 262 times, 1 visits today)

LEAVE A REPLY

Please enter your comment!
Please enter your name here