Ajax Add & Retrieve MySQL records using jQuery & PHP

by

I love jQuery framework, feels like we can do some awesome things easily. If you are familiar with the basic of jQuery framework too, the next thing you have to do is learn to use jQuery Ajax to add and retrieve records from MySql database. Let’s take a look at how we implement real time add and retrieve records using Ajax.

Database
sample database comment table columns id, name and message

+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(5)       | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20)  | NO   |     | NULL    |                |
| message | varchar(120) | NO   |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+

comment.php

<html>
   <head>

   	   <script type="text/javascript" src="js/jquery-1.7.1.min.js"></script>
   	   <script type="text/javascript" src="js/jquery-ui-1.8.17.custom.min.js"></script>
   	   
       <script type="text/javascript">
               $(document).ready(function(){
               	    $("#button").click(function(){

                          var name=$("#name").val();
                          var message=$("#message").val();

                          $.ajax({
                              type:"post",
                              url:"process.php",
                              data:"name="+name+"&message="+message,
                              success:function(data){
                                 $("#info").html(data);
                              }

                          });

                    });
               });
       </script>
   </head>

   <body>
        <form>
               name : <input type="text" name="name" id="name"/>
               </br>
               message : <input type="text" name="message" id="message" />
               </br>
               <input type="button" value="Send Comment" id="button">
               
               <div id="info" />
        </form>
   </body>
</html>

process.php

<?php
  mysql_connect("localhost","root","");
  mysql_select_db("php_databaseajax");

  $name=$_POST["name"];
  $message=$_POST["message"];

  $query=mysql_query("INSERT INTO comment(name,message) values('$name','$message') ");

  if($query){
  	echo "Your comment has been sent";
  }
  else{
  	echo "Error in sending your comment";
  }
?>

Try to input some comments using that form, here’s what i got
add

The record i just submitted is successfully added to the database. Although we can’t see right now but it can be checked directly in your MySql.

+----+----------------+---------------------------+
| id | name           | message                   |
+----+----------------+---------------------------+
|  1 | Agung Setiawan | Cool comment              |
|  2 | Nusantara      | Indonesia is hot but nice |
+----+----------------+---------------------------+

What is the value of the comment feature of web app if visitors can’t see their own comment. Now, this is our time to be a hero… 😀
Edit both files that we have created to add the ability to retrieve records from database.

comment.php

<html>
   <head>

   	   <script type="text/javascript" src="js/jquery-1.7.1.min.js"></script>
   	   <script type="text/javascript" src="js/jquery-ui-1.8.17.custom.min.js"></script>
   	   
       <script type="text/javascript">
               $(document).ready(function(){

                    function showComment(){
                      $.ajax({
                        type:"post",
                        url:"process.php",
                        data:"action=showcomment",
                        success:function(data){
                             $("#comment").html(data);
                        }
                      });
                    }

                    showComment();


               	    $("#button").click(function(){

                          var name=$("#name").val();
                          var message=$("#message").val();

                          $.ajax({
                              type:"post",
                              url:"process.php",
                              data:"name="+name+"&message="+message+"&action=addcomment",
                              success:function(data){
                                showComment();
                                 
                              }

                          });

                    });
               });
       </script>
   </head>

   <body>
        <form>
               name : <input type="text" name="name" id="name"/>
               </br>
               message : <input type="text" name="message" id="message" />
               </br>
               <input type="button" value="Send Comment" id="button">
               
               <div id="info" />
               <ul id="comment"></ul>
        </form>
   </body>
</html>

process.php

<?php
  mysql_connect("localhost","root","");
  mysql_select_db("php_databaseajax");

  
  $action=$_POST["action"];

  if($action=="showcomment"){
     $show=mysql_query("Select * from comment order by id desc");

     while($row=mysql_fetch_array($show)){
     	echo "<li><b>$row[name]</b> : $row[message]</li>";
     }
  }
  else if($action=="addcomment"){
  	$name=$_POST["name"];
    $message=$_POST["message"];

  	 $query=mysql_query("INSERT INTO comment(name,message) values('$name','$message') ");

     if($query){
     	echo "Your comment has been sent";
     }
     else{
     	echo "Error in sending your comment";
     }
  }
?>

Now it is more cool with visitor’s comment directly show up after Send Button is clicked
add

49 thoughts on “Ajax Add & Retrieve MySQL records using jQuery & PHP”

  1. Hi,
    That is very neat and just what I was looking for
    Quick question. If i have several topics on a page and hence different add comment boxes for each topic, how would i change the code?

    Best,
    Rob

    Reply

  2. Its like you read my mind! You seem to know a lot about this, like you wrote the book in it or something.
    I think that you can do with some pics to drive the
    message home a bit, but other than that, this is excellent
    blog. A fantastic read. I will certainly be back.

    Reply

  3. Struggling to get your car paid off? Are you mad about the
    last deal you got? Perhaps you’re looking for an automobile now, and you’re not sure what to
    do differently. You’re in the same boat with many other people. Keep reading to find out information regarding what to do next time you enter a dealership.

    Reply

  5. The simplest tutorial by far. Might just add-up, I wanted to auto refresh div every X seconds, would you advise to execute a db query or use rss as source feed.

    TIA.

    Reply

  14. how to fetch data one by one and selecting at at particular option then it will show wrong answer or right answer,so plz help me to try this problem and also to use different id

    Reply

  21. i used your first part of tutorial to insert into database but everything except date is not getting inserted to database.Is it because i am using a jquery based calender in my form?please help me out i am new to ajax

    Reply

  38. Hi this session is helpful. but still I want to do something different if you can help me
    If I want to do like “Select * from comment where id=?” then how to do it using JQuery and Ajax.

    Reply

  39. awesome explanation i try to do this but having a lot of bug to send a data string on ajax file finally i have see this article i got a solicitation its working thanks for sharing this article

    Reply

  41. fetch data from mysql database without click on any button…. ( like chat box of facebook where we type text after enter we see result )

    Reply

  42. I didnt read all the post but i scrolled to bottom and and top like 5 times just to see the fish animation. This really won my heart. Amazing!

    🙂
    You can show / teach me how it is made. I have given you my Email address.

    Reply

  44. while i m fetching data using ajax, in that data i have a button which not working while i m trying to call a javascript function.

    Reply

  47. Fantastic Info! But I???¨º?¨¨m having some trouble trying to load your blog. I have read it many times just before and never gotten a thing like this, but now when I attempt to load some thing it just takes a bit whilst (5-10 minutes ) after which just stops. I hope i don???¨º?¨¨t have spyware or a thing. Does anyone know what the difficulty might be?
    Boite communication Skikda http://www.foodspotting.com/5001066

    Reply

Leave a comment