Ajax Add & Retrieve MySQL records using jQuery & PHP

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

Advertisements

44 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

  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.

  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.

  4. 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.

  5. 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

  6. 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

  7. 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.

  8. 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

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

  10. 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.

  11. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s