Retrieve data from mysql table using ajax

Question

I'm trying to show MySQL data using Ajax. Unfortunately, I am unable to find the correct way. I was trying to show MySQL data on a select box. When I click on "select category" option then all category will show as dropdown.

here is my HTML code.

<!DOCTYPE html>
 <html>
 <head>
 <title>PHP MySQL Insert Tutorial</title>
 <script src='https://code.jquery.com/jquery-2.1.3.min.js'></script>
 </head>

 <body>
 <select id='category'>

 </select>
 <script src='fetch.js'></script>
 </body>
</html>

I have used this JS code to send request. Here is my JS code.

$('#category').onclick(function(){
     $.getJSON(
         'fetch.php',

         function(result){
             $('#category').empty();
             $.each(result.result, function(){
             $('#category').append('<option>'+this['category']+'</option>');
             });
         }
     );
});

I have used this php code to complete ajax request and database connection. Here is my PHP code.

<?php
 define('HOST','localhost');
 define('USERNAME', 'root');
 define('PASSWORD','');
 define('DB','ajax');

 $con = mysqli_connect(HOST,USERNAME,PASSWORD,DB);

 $category = $_GET['category'];

 $sql = "select category from ajaxx where category='$category'";

 $res = mysqli_query($con,$sql);

 $result = array();

 while($row = mysqli_fetch_array($res)){
 array_push($result, 
 array('category'=>$row[0]));
 }

 echo json_encode(array('result'=>$result));


    enter code here

 mysqli_close($con);
?>

Not working.. but thank you for your time. Do you have more free time to solve my problem, please! — Hasem Uddin, Jun 07 '16 at 18:51
I'm clicking on select box but nothing display from database. — Hasem Uddin, Jun 07 '16 at 18:53
Is the AJAX request made? What is the server's response? You have to at least *try* to debug this. — David, Jun 07 '16 at 18:53
You want the datas to fetched in page load? I mean is there any chance that the mysql array will update dynamically without the need of refresh? — , Jun 07 '16 at 18:55
ajax request made but nothing display from server. Maybe there is wrong on my js file. — Hasem Uddin, Jun 07 '16 at 18:55

score 1 · Answer 1 · answered Jun 07 '16 at 18:57

1

When you make the AJAX request, it's to this URL:

fetch.php

But then in the server-side code, you try to get a query string value:

$category = $_GET['category'];

You can't get a query string value that you never provided. So when you build your SQL query (which is wide open to SQL injection by the way), there's nothing to get from the database.

If you want to use a query string value, you have to provide one:

$.getJSON(
     'fetch.php?category=someValue',
     function(result){
         //...
     }
 );

What value you provide or where you get that value is up to you. (Perhaps from $('#category').val()?) But it has to exist before you can use it.

answered Jun 07 '16 at 18:57

David

208,112
36
198
279

$category = $_GET['category']; by this code i wanted to get value from category column from my database. – Hasem Uddin Jun 07 '16 at 19:02
@HasemUddin: Then you're going to want to start with some introductory PHP tutorials, because `$_GET` is *not* how you connect to a database. It's how you get values from the query string on the request URL. – David Jun 07 '16 at 19:03
@HasemUddin Do not confuse **(1)** `category`, the ID for your select element, **(2)** `category`, the name of the variable being passed through AJAX, and **(3)** `category` the field name in your table. Perhaps change a couple of them to keep life simpler and your hair non-grey. – cssyphus Jun 07 '16 at 19:11

score 1 · Answer 2 · edited May 23 '17 at 10:29

You may have confused two things: (a) initially fetching the HTML code to populate the options of your <select> control, and (b) Catching the selected option and using it to perform another DB query, returning new data.

Please review this modified (untested) code sample:

<!DOCTYPE html>
 <html>
 <head>
 <title>PHP MySQL Insert Tutorial</title>
 <script src='https://code.jquery.com/jquery-2.1.3.min.js'></script>
 </head>

 <body>
 <select id='category'>

 </select>
 <div id="resultDIV"></div>
 <script src='fetch.js'></script>
 </body>
</html>

javascript/jQuery:

    //Run on document ready to populate the dropdown box
    $(document).ready(function(){
    $.getJSON(function(){
        'fetch.php',
        function(result){
            $('#category').empty();
            $.each(result.result, function(){
                $('#category').append('<option>'+this['category']+'</option>');
            });
        }
    });

    $(document).on('click', '#category', function(){
        //run on click to take dropdown value and perform lookup
        myCat = $(this).val();
        $.ajax({
            type: 'post',
             url: 'getcategory.php',
            data: 'category=' +myCat,
            success: function(d){
                //if (d.length) alert(d);
                $('#resultDIV').html(d);
            }
        });
    });

}); //END document.ready

I have used this php code to complete ajax request and database connection. Here is my PHP code.

<?php
    /*** getcategory.php ***/

    define('HOST','localhost');
    define('USERNAME', 'root');
    define('PASSWORD','');
    define('DB','ajax');

    $con = mysqli_connect(HOST,USERNAME,PASSWORD,DB);

    $category = $_GET['category'];

    $sql = "select category from ajaxx where category='$category'";

    $res = mysqli_query($con,$sql);

    $result = array();

    while($row = mysqli_fetch_array($res)){
    array_push($result, 
    array('category'=>$row[0]));
    }

    echo json_encode(array('result'=>$result));


    enter code here

    mysqli_close($con);
?>

Here are some basic, simple AJAX examples to study (the three links at the bottom, but also note the information from the first link). Copy them to your server and make them work - play around with them:

AJAX request callback using jQuery

I didn't even notice the overarching logic problem that the OP is trying to use a list's change event to populate itself. Good catch! — David, Jun 07 '16 at 19:06
Notice: Undefined index: category in C:\xampp\htdocs\get-data-ajax\getcategory.php on line 11 {"result":[]} i got this notice after clicking. — Hasem Uddin, Jun 07 '16 at 19:13
Yeah, probably. This is undebugged code, just to give you an idea what to do. The first thing to try is to change your `getcategory.php` file to just have this: `` and uncomment the `alert()` instruction in the `$.ajax()` success function. Then start adding more in. — cssyphus, Jun 07 '16 at 19:15

score 0 · Answer 3 · answered Jun 07 '16 at 20:14

Your ajax code needs some changes :

<!DOCTYPE html>
 <html>
 <head>
 <title>PHP MySQL Insert Tutorial</title>
 <script src='https://code.jquery.com/jquery-2.1.3.min.js'></script>
 <script type="text/javascript">
function myAjax ()
{ $.ajax( { type    : 'POST',
            data    : { 'category' : $('#txt_cat').val() }, // SEND CATEGORY.
            url     : 'fetch.php',
            success : function ( result )
                      { $( '#category' ).empty();
                        var arr = JSON.parse( result );
                        var sel = document.getElementById("category");
                        for ( i = 0; i < arr.length; i++ )
                        { var option = document.createElement( "option" );
                          option.text = arr[ i ];
                          sel.add( option );
                        }
                      },
            error   : function ( xhr )
                     { alert( "error" );
                     }
          }
        );
}
 </script>
 </head>
 <body>
   Enter category <input type="text" id="txt_cat"/>
   <button onclick="myAjax()">Click here to fill select</button>
   <select id='category'>
     <option> - empty - </option>
   </select>
 </body>
</html>

fetch.php

<?php
$category = $_POST[ "category" ];           // CATEGORY FROM HTML FILE.
// CONNECT TO DATABASE AND QUERY HERE.
$result = Array( "111","222","333","444" );  // SAMPLE DATA.
echo json_encode( $result );
?>

Retrieve data from mysql table using ajax

3 Answers3