How to Insert Data into MySQL Database using PHP, MySQLi, AJAX and JQuery

Language

This tutorial will show you how to insert data into mysql database using PHP, MySQLi, AJAX and JQuery. You might wonder why use AJAX and JQuery when you can insert into database using PHP and MySQLi. Yes, but with the use of AJAX and JQuery, you don't need to reload the page in doing an action. This tutorial will not give you a good design but will give you idea on the topic.

Creating our Database

First, we're going to create our database. This will store the data that we are going to insert. 1. Open phpMyAdmin. 2. Click databases, create a database and name it as "ajax_insert. 3. After creating a database, click the SQL and paste the below code. See image below for detailed instruction.
  1. CREATE TABLE `post` (
  2. `postid` INT(11) NOT NULL AUTO_INCREMENT,
  3. `post_text` VARCHAR(200) NOT NULL,
  4. `date_time` datetime NOT NULL,
  5. PRIMARY KEY(`postid`)
  6. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ajax_insert

Creating our Connection

Next step is to create a database connection and save it as "conn.php". This file will serve as our bridge between our form and our database. To create the file, open your HTML code editor and paste the code below after the tag.
  1. <?php
  2.  
  3. //MySQLi Procedural
  4. $conn = mysqli_connect("localhost","root","","ajax_insert");
  5. if (!$conn) {
  6. die("Connection failed: " . mysqli_connect_error());
  7. }
  8.  
  9. ?>

Creating our Form with our Output

Next, we create our add form and a page to output our added data. We name this as "index.php". Also included in this page, the location of our jquery script which will be included in the file of this tutorial.
  1. <!DOCTYPE html>
  2. <html lang = "en">
  3. <meta charset = "UTF-8" name = "viewport" content = "width=device-width, initial-scale=1"/>
  4. <title>Inserting Data into MySQL Database using PHP, AJAX, JQuery</title>
  5. </head>
  6. <h2>What's on your Mind?</h2>
  7. <div id = "post_form">
  8. <form>
  9. <textarea id = "post" class = "form-control"></textarea><br>
  10. <button type = "button" id = "add_post">POST</button>
  11. </form>
  12. <br>
  13. </div>
  14. <div id = "result"></div>
  15. </body>
  16. <script src = "jquery-3.1.1.js"></script>
  17. <script type = "text/javascript">
  18. $(document).ready(function(){
  19. displayResult();
  20. /* ADDING POST */
  21.  
  22. $('#add_post').on('click', function(){
  23. if($('#post').val() == ""){
  24. alert('Please enter something first');
  25. }else{
  26.  
  27. $post = $('#post').val();
  28.  
  29. $.ajax({
  30. type: "POST",
  31. url: "add_post.php",
  32. data: {
  33. post: $post,
  34.  
  35. },
  36. success: function(){
  37.  
  38. displayResult();
  39. }
  40. });
  41. }
  42. });
  43. /***** *****/
  44. });
  45.  
  46. function displayResult(){
  47. $.ajax({
  48. url: 'add_post.php',
  49. type: 'POST',
  50. async: false,
  51. data:{
  52. res: 1
  53. },
  54. success: function(response){
  55. $('#result').html(response);
  56. }
  57. });
  58. }
  59.  
  60. </html>

Creating our Add Code

Last, we create our add code which will add our inputted data into our database. We name this as "add_post.php".
  1. <?php
  2. include ('conn.php');
  3. if(isset($_POST['post'])){
  4. $post = addslashes($_POST['post']);
  5. mysqli_query($conn,"insert into `post` (`post_text`, `date_time`) values ('$post', NOW())") or die(mysqli_error());
  6. }
  7. ?>
  8. <?php
  9. if(isset($_POST['res'])){
  10. ?>
  11. <?php
  12. $query=mysqli_query($conn,"select * from `post` order by `date_time` desc") or die(mysqli_error());
  13. while($row=mysqli_fetch_array($query)){
  14. ?>
  15. <div>
  16. Date: <?php echo date('M-d-Y h:i A',strtotime($row['date_time'])); ?> <br>
  17. Post: <?php echo $row['post_text']; ?>
  18. </div>
  19. <br>
  20. <?php
  21. }
  22. }
  23. ?>

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