Dynamically load mysql data using jquery and php

In this guide, I’ll be showing how you can dynamically retrieve data from mysql database using Jquery and Php. When I say dynamically, it means that there is no need to reload the page every time you try to access data from the database. This would be nice, since it will take lesser time for the user to actually see the results of his query.

Requirements:

I assume that you already know how to retrieve data from mysql database using Php. And basic knowledge of using Jquery.

First thing that you need to do is to import Jquery library:

<script type="text/javascript" src="../js/jq.js"></script>

Then create an input box. Where the user will input his query:

<input type="text" id="inp" name="inp"/></span>

Then create a div which we will use later to display the results:

<div class="results"></div>

Declare another script on the head portion of your project:

<script type="text/javascript">

</script>

Inside the new script that you’ve just created. Declare a function that would listen to the keyup event.

$(function(){

    $(#inp).keyup(function(){

    });

});

Here’s what happens. The function that you have declared will monitor any changes to the input box which has the id ‘inp’. And every time you hit any key on the keyboard. The action which is to be performed inside that event will be executed.

Inside the keyup function. Declare a variable that would store the value which is inputted in the input box.

var inpvalue= $('#inp').val();

Next thing that you need to do, is to declare another jquery function. This is the most important part of this tutorial, since this will be the one who will actually fetch the value from the database.

$.ajax({
    type: 'POST',  
    data: ({p : inpval}),
    url: 'listercust.php',
    success: function(data) {
         $('.results').html(data); 
 });

Here’s the code explanation:

  • type:the form method. This will either be POST or GET. POST is more secure than GET. Since users will be able to modify the data directly from the url if you use GET. And if you use POST, data will be hidden and you won’t be able to modify or tamper it.
  • data:this is the data that will be passed to the sql query in the php file that you will specify in the url.
  • url: this is the actual php file which has the query in it. Be sure to define everything that you need to actually query the database in this file.
  • success: this is where you define the callback function. A callback function is the function that will be called after the ajax function executed perfectly. This is where you will display the actual result that has been generated by the php file.

Then all you have to do now is to display the result of the query by changing the html property of the div with the class result. And the parameter would be the data fetched from the ajax function:

$('.result').html(data);

Overall this is what the code will look like:

$(function(){
            $('#inp').keyup(function(){

            var inpval=$('#inp').val();

            $.ajax({
                type: 'POST',
                data: ({p : inpval}),
                url: 'listercust.php',
                success: function(data) {
                     $('.result').html(data);

          }
        });
    });
});


The php file

The  second part of this tutorial would be the the php file that has the query on it.

First thing that you need to do is to declare all the connections needed to connect to the database. I won’t be discussing it here since its  one of the basics.

Declare a php variable that would receive the value from the ajax function that you have created earlier. Remember that the parameter ‘p’   should match the value that you declared on the ajax function.

$name=$_POST['p'];

This part of the function:

data: ({p : inpval}),

All you have to do now is to make a query that would use the data inputted by the user:

$query=mysql_query(“SELECT * FROM table

WHERE fieldx like ‘$name%’”);

Then display the fetch values:

while($row=mysql_fetch_assoc($query)){
    $row['name'];

}

Conclusion

That is how you fetch data dynamically from mysql database using jquery.

35 thoughts on “Dynamically load mysql data using jquery and php

  1. i see that this is good for retrieving a single field value, how about if i have 5 fields and i want to get all the data from mysql and display it on the form with the 5 text fields, how will i do that?

    Thanks

    • Here:

      $(‘#txt’).blur(function(){

      var id = $(this).val();
      $.post(‘selector.php’, {‘id’ : id}, function(data){
      var new_data = JSON.parse(data);

      $(‘#container’).html(new_data['address']);
      });
      });

      You can just reference the other fields like this:
      new_data['age']
      new_data['full_name']

      But of course your php file should be echoing out a JSON string. And that is why I used the JSON.parse() method to convert the JSON string into an array. Feel free to ask if something is not clear.

  2. Great article, but one error that took me a few minutes to sort out.

    you show the div with the class name results in the line

    but in the jquery ajax query you miss the s from the class name.

    $(‘.result’).html(data);

    make this

    $(‘.results’).html(data);

    and it all works fine.

  3. Hi, I tried to use your code for my project and for strange reason its not working when I use where. I have feeling value of my code does not pass to the php file.

    Here is my code

    $(function() {
    $(“#airline”).change(function() {
    var airValue = $(this).val();

    $.ajax({
    url: ‘getAirLine.php’,
    type: ‘POST’,
    data: {p : airValue},
    datatye: ‘text’,
    success: function(data) {
    $(“#airlineInfo”).html(data);
    }
    });
    });
    });

    Garuda Indonesia
    Merpati
    Lion Air
    Batavia Air
    Sriwijaya AIr
    Air Asia
    Wings Air
    Garuda Citilink

    And here is my php

    I really appreciate of your suggestion/opinion

    Cheers

    • Are you using Linux? The first thing I noticed was the name of your php file: getAirLine.php you might want to change it to just lowercase letters.
      Can you install firebug or check on the network tab of chrome dev tools(ctrl + shift + i) whether the request is successful or not?
      Then comment your error here or better yet Google it.
      You may also want to remove the php open and close tags on your comment as I don’t see any PHP code(seems like wordpress doesn’t want it).
      Consider checking out the documentation of chrome dev tools or firebug if you haven’t use it yet. It’s really important that
      you are using these kinds of tools so you don’t need to guess what’s the problem.

      • I’m using win 7. I’ve tried to check on firebug and chrome dev tools, however, I noticed there is no response when I click one of the option. It’s stuck on waiting.

        Here is my code again

        PHP

        $con = mysql_connect(“localhost”,”casper99″,”321010″);
        if (!$con)
        {
        die(‘Could not connect: ‘ . mysql_error());
        }
        mysql_select_db(“trifi_db”, $con);

        $name = $_POST['p'];
        $query = mysql_query(“SELECT * FROM airline WHERE code LIKE ‘$name%’”);

        while($row=mysql_fetch_assoc($query))
        echo $row['airlineName'];

        and for the page

        $(function() {
        $(“#airline”).change(function() {
        var airValue = $(this).val();

        $.ajax({
        url: ‘getAirLine.php’,
        type: ‘POST’,
        data: {p : airValue},
        datatye: ‘text’,
        success: function(data) {
        $(“#airlineInfo”).html(data);
        }
        });
        });
        });
        /script

        select id = “airline” size = “4″ Multiple
        Garuda Indonesia
        Merpati
        Lion Air
        Batavia Air
        Sriwijaya AIr
        Air Asia
        Wings Air
        Garuda Citilink
        /select

        div id = “airlineInfo”

        /div
        /div

        If I remove “where” condition on php, it will result the whole data from mysql.However, if I use “where” condition, there is no result.

        I really appreciate for your help.

      • seems like a problem in the query. Did you try to run getairline.php separately by supplying a value for $name? did it return anything? Run the same query in phpmyadmin and supply the same value, if you didn’t get anything then your problem is that query doesn’t actually return any results.

  4. mine its working, but mostly, I Finally got it (the way it really works) !!!!
    thank you so very much!!!
    Best Regards!!!

  5. The code as posted is good for simplicity in this example, however it should never be used as posted in a production system. The code posted allows for a sql injection attack.

    To learn more about SQL injection and how to prevent it in a PHP/MySQL application see http://us2.php.net/manual/en/function.mysql-real-escape-string.php

    Here’s a better example (fragment) that trys to mitigate the risk:
    $name = mysql-real-escape-string($_POST['p']);
    $query = mysql_query(“SELECT * FROM airline WHERE code LIKE ‘$name%’”);

    • without the mysql-real-escape-string someone could possibly enter
      ; select * from mysql.user;
      which may give back all the info about your mysql users on the server. or even something more mischievious like
      ;insert into table values…
      or
      ;delete * from airline;
      or even
      ;drop table airline;

  6. Thank you for this useful post. This is what I need in what I am doing right now. By the way, how can I display the result in a jquery dialog box? Sorry, I am just starting to learn jquery and I still can’t figure it out. Thanks in advance =D

      • What if i want to view a particular data retrieved from MySQL displayed in a table, and each row is appended with a link. Then if the link is clicked, a certain info regarding the row would be displayed.

        Can’t I do it like the code snippet below? It was not working when I executed it. Thank you in advance again.
        .
        .
        .

        $(function() {
        $(‘.demo’).click(function(){
        $.ajax({
        type: ‘POST’,
        data: ({p : this.id}),
        url: ‘View.php’,
        success: function(data) {
        $(‘#my_modal_dialog’).html(data);
        }
        });
        });
        });

        .
        .
        .
        <a href="#" id="”” class=”demo”>View

  7. For some reason, reading the variable passed from javascript does not work.
    $name=$_POST['p'];
    I am using PHP 5.4.11-1

    /var/log/http/error_log does not show anything. The code in the php file runs fine.
    However, if I print the variable $name after the line
    $name=$_POST['p'];
    The screen goes blank. Is there any other method of fetching the data in PHP 5.4.11-1 ?
    I am using the same syntax in your example as below
    $.ajax({
    type: ‘POST’,
    data: ({p : inpval}),
    url: ‘listercust.php’,
    success: function(data) {
    $(‘.results’).html(data);
    });

    • try to use firebug or chrome dev tools. In the network tab > headers > parameters. Then check to see if the variable that you are passing is indeed there. I don’t see any reason why $_POST wouldn’t work in PHP 5.4.11-1.

      • Thanks Wern for the helpful hint. I found that the subsequent mysql query which uses the passed variable does work correctly. I wanted to check if the variable was correctly passed and so I used print which does not work. Still not able to figure out why this variable does not print when its a single text variable.

  8. Great day!!

    I have a problem about my code.

    Database:
    order_no order_name
    23532 House of Glass BC
    32532 Inner Weather
    53254 Fernwood Fitness
    53253 Aspect Joinery
    more.. more..

    Picture:http://i.imgur.com/yQAErOk.jpg
    let say that: i have a 2 textbox as you see the image..
    ORDER NUMBER 1.
    ORDER NAME 2.

    Ive already finished the 2 case but the 3rd case not yet done kindly help me please
    case 1: If the order number is empty says “this field is required”.
    case 2: If you enter invalid order number says “This order number does not exists”.

    case 3: When If you enter valid order number automatically display order name in the textbox ORDER NAME without clicking a button..

    see my post here:
    http://www.dreamincode.net/forums/topic/313773-problem-about-ajax/page__p__1811019__fromsearch__1&#entry1811019

    • it seems like you’re working with wordpress. I’m actually currently working with a wordpress plugin so I’m going to recommend using wpdb http://codex.wordpress.org/Class_Reference/wpdb instead of plain mysql.
      For case 3, you can do something like:

      $response = array();
      if($order_id == ”){
      $response['error'] = ‘No order id’;
      }else{
      $order = $wpdb->get_row(“SELECT order_name FROM tbl WHERE order_num = ‘$order_num’”)
      if($wpdb->num_rows > 0){
      $order_name = $order->order_name;
      $response['order_name'] = $order_name;
      }else{
      $response['error'] = ‘Invalid order id’
      }
      }
      echo json_encode($response);

      Then you can just display the error if its present.

      • thank ^_^
        where can i put that sir?

        this my original code:
        I use the get_var here, how about getting the default value?
        function.php

        add_action(‘wp_ajax_nopriv_checkOrdrNum’,’checkOrdrNum’);
        add_action(‘wp_ajax_checkOrdrNum’,’checkOrdrNum’);
        function checkOrdrNum(){
        global $wpdb,$current_user;
        $orderno= $_POST['orderno'];
        if ($_POST['id']==0) {
        $ordr_num=$wpdb->get_var(‘SELECT COUNT(*) FROM art_work_history WHERE cus_id = ‘ . $current_user->ID . ‘ AND (status=”Unallocated” || status=”In Progress”) AND isweb = 0 AND order_no = ‘.$orderno);
        } else {
        $ordr_num=$wpdb->get_var(‘SELECT COUNT(*) FROM art_work_history a JOIN art_work_proof b ON a.order_no = b.order_no WHERE a.cus_id=’ . $current_user->ID . ‘ AND isweb = 0 AND (a.status=”Customer Proof” || a.status=”Alterations” || a.status = “Alteration Pending”) AND b.order_no=’.$orderno);
        }

        echo json_encode(array(‘order_no’ => $ordr_num));
        exit;
        }

        this is my original validationForm.js

        jQuery.validator.addMethod(“ordrNum”,function(value,element){
        var isExist=false;
        $.ajax({
        type : “POST”,
        async: false,
        data : “action=checkOrdrNum&id=0&orderno=” + value,
        url : ajaxurl,
        dataType:”json”,
        success: function (data) {
        //console.log(data["order_no"]);
        if (data["order_no"] >0) {
        isExist=true;
        }
        }
        });
        //console.log(isExist);
        return isExist;//true;

        },”This order number does not exist”);

        $(document).on(‘keyup’, ‘#order_numbr’, function() {
        if( $(‘form’).valid() ) {
        $.post( ajaxurl, {“action”:”checkOrdrNum”,”id”:0,”orderno”: $(this).val()},
        function( data ) {
        $(‘#ur_name1′).val(‘Order ‘ + data.order_no);
        },
        ‘json’ );
        }
        });

  9. Hey, I am using similar kind of code for search function and it fetches the data from database in div which displays blocks on keyup. Now I want keyboards down key to work and go into that div box like any other search engine..How can I implement that. Need that urgently

  10. Thank you very much for your post. I have one problem which is I could not show the result into a list box. Please advise me about this matter.

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