Easy and Simple Adding/Inserting Data into MySQL Database using PHP

Submitted by: 
Language: 
Visitors have accessed this post 1566 times.

This tutorial will show you an easy and simple way in adding/inserting data into mysql database using 3 methods which are MySQLi Object-oriented, MySQLi Procedural and PDO. This tutorial does not include a good design but will give you knowledge in adding/inserting data into mysql database using the 3 methods.

Creating our Database

First, we're going to create a database that contains our data.
1. Open phpMyAdmin.
2. Click databases, create a database and name it as "add".
3. After creating a database, click the SQL and paste the below code. See image below for detailed instruction.

  1. CREATE TABLE `user` (
  2. `userid` INT(11) NOT NULL,
  3. `firstname` VARCHAR(30) NOT NULL AUTO_INCREMENT,
  4. `lastname` VARCHAR(30) NOT NULL,
  5. `added_via` VARCHAR(20) NOT NULL,
  6. PRIMARY KEY (`userid`)
  7. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

add

Creating our Add form and Add Script using the 3 methods

Lastly, we create our add form that will add the data into our database. We name this page as "index.php". We also include the 3 methods of adding into this page. To create the page, open your HTML code editor and paste the code below after the tag.

  1. <!DOCTYPE html>
  2. <html>
  3. <head>
  4. <title>Adding/Inserting Data into MySQL</title>
  5. </head>
  6. <body>
  7. <div>
  8. <h2>Add Form</h2>
  9. <form method="POST">
  10. <label>Firstname: <input type="text" name="firstname"></label>
  11. <label>Lastname: <input type="text" name="lastname"></label>
  12. </br></br>
  13. <input type="submit" value="MySQLi Object-oriented" name="mysqli_oop">
  14. <input type="submit" value="MySQLi Procedural" name="mysqli_procedural">
  15. <input type="submit" value="PDO" name="pdo">
  16. </form>
  17.  
  18. <?php
  19. //MySQLi Object-oriented
  20. if (isset($_POST['mysqli_oop'])){
  21.  
  22. // Create connection
  23. $con = new mysqli("localhost", "root", "", "add");
  24. // Check connection
  25. if ($con->connect_error) {
  26. die("Connection failed: " . $con->connect_error);
  27. }
  28.  
  29. $firstname=$_POST['firstname'];
  30. $lastname=$_POST['lastname'];
  31.  
  32. $sql = "INSERT INTO `user` (firstname, lastname, added_via)
  33. VALUES ('$firstname', '$lastname', 'MySQLi Object-oriented')";
  34.  
  35. if ($con->query($sql) === TRUE) {
  36. echo "New record created successfully";
  37. } else {
  38. echo "Error: " . $sql . "<br>" . $con->error;
  39. }
  40.  
  41. $con->close();
  42. }
  43. //MySQLi Procedural
  44. elseif (isset($_POST['mysqli_procedural'])){
  45.  
  46. // Create connection
  47. $con = mysqli_connect("localhost", "root", "", "add");
  48. // Check connection
  49. if (!$con) {
  50. die("Connection failed: " . mysqli_connect_error());
  51. }
  52.  
  53. $firstname=$_POST['firstname'];
  54. $lastname=$_POST['lastname'];
  55.  
  56. $sql = "INSERT INTO `user` (firstname, lastname, added_via)
  57. VALUES ('$firstname', '$lastname', 'MySQLi Procedural')";
  58.  
  59. if (mysqli_query($con, $sql)) {
  60. echo "New record created successfully";
  61. } else {
  62. echo "Error: " . $sql . "<br>" . mysqli_error($con);
  63. }
  64.  
  65. mysqli_close($con);
  66. }
  67. //PDO
  68. elseif (isset($_POST['pdo'])){
  69.  
  70. $servername = "localhost";
  71. $username = "root";
  72. $password = "";
  73. $dbname = "add";
  74.  
  75. try {
  76. $con = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  77. // set the PDO error mode to exception
  78. $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  79.  
  80. $firstname=$_POST['firstname'];
  81. $lastname=$_POST['lastname'];
  82.  
  83. $sql = "INSERT INTO `user` (firstname, lastname, added_via)
  84. VALUES ('$firstname', '$lastname', 'PDO')";
  85. // use exec() because no results are returned
  86. $con->exec($sql);
  87. echo "New record created successfully";
  88. }
  89. catch(PDOException $e)
  90. {
  91. echo $sql . "<br>" . $e->getMessage();
  92. }
  93.  
  94. $con = null;
  95. }
  96. ?>
  97.  
  98. </div>
  99. <br>
  100. <div>
  101. <table border="1">
  102. <thead>
  103. <th>UserID</th>
  104. <th>Firstname</th>
  105. <th>Lastname</th>
  106. <th>Added via</th>
  107. </thead>
  108. <tbody>
  109. <?php
  110. $con = mysqli_connect("localhost","root","","add");
  111.  
  112. // Check connection
  113. {
  114. echo "Failed to connect to MySQL: " . mysqli_connect_error();
  115. }
  116.  
  117. $query=mysqli_query($con,"select * from `user`");
  118. while($row=mysqli_fetch_array($query)){
  119. ?>
  120. <tr>
  121. <td><?php echo $row['userid']; ?></td>
  122. <td><?php echo $row['firstname']; ?></td>
  123. <td><?php echo $row['lastname']; ?></td>
  124. <td><?php echo $row['added_via']; ?></td>
  125. </tr>
  126. <?php
  127. }
  128.  
  129. ?>
  130. </tbody>
  131. </table>
  132. </div>
  133. </body>
  134. </html>

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.

Virus note: All files are scanned once-a-day by SourceCodester.com for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:

1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You may insert videos with [video:URL]
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe> [video]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.