PHP AJAX: retrieve data from mysql and show them in form

Question

I am trying to create a autofill form. I have a drop down menu and some form fields like text boxes. When user click on drop down menu options Ajax call initiate and it will check data in Mysql database. if record found then it will display data in relevant text box.

My code is working fine but i am not able to return data from mysql database.

<script type="text/javascript">
$(function() {
    $("#autofill").change(function() {
        var data1= $('option:selected', this).text();
                        $.ajax({
                    type: "GET",
                    url:"autofill.php",
                    cache: false,
                    dataType: 'json',
                    data: 'action1=' + data1,
                    beforeSend: function() { 
                        $("#validation-errors").hide().empty(); 
                    },
                    success: function(data) {
                        if(data.success == false)
                            {
                                var arr = data.errors;
                                $.each(arr, function(index, value)
                                {
                                    if (value.length != 0)
                                    {
                                        $("#validation-errors").append('<div class="alert alert-danger"><strong>'+ value +'</strong><div>');
                                    }
                                });
                                $("#validation-errors").show(); 
                                 $('html, body').animate({scrollTop: $("#validation-errors").offset().top}, 2000);
                                btn.button('reset');                            
                            } else {
                                data = JSON.parse( data );
                               $('#title').val('data.title');
                                $('#total').val('data.total');
                                 $('html, body').animate({scrollTop: $("#features-left-image").offset().top}, 2000);
                            }
                    },
                    error: function(xhr, textStatus, thrownError) {
                        alert('Something went to wrong.Please Try again later...');
                        btn.button('reset');
                        alert(thrownError);
                    }
                });             
                return false;
            });

    });
</script>

autofill.php

<?php 
require_once('include/db.php');
if(isset($_GET['action1'])){
$search = strip_tags(trim($_GET['action1'])); 
$search = strtolower($search);
$search="%".$search."%";

$result = $mysqli->prepare("SELECT * FROM posting WHERE title LIKE ?");
$result ->bind_param("s", $search);
$result->execute();
$result->store_result(); 
//$result->bind_result($title);
//$data[]=$result;

echo json_encode($data);
}

?>

As per my understanding problem is around.

//$result->bind_result($title);
//$data[]=$result;

echo json_encode($data);

Please advise me to fix this issue.

Thanks

Answer 1

There are many issues in the logic,

$result = $mysqli->prepare("SELECT * FROM posting WHERE title LIKE ?");
$result ->bind_param("s", $search);
$result->execute();
$result->store_result(); 
//$result->bind_result($title);
//$data[]=$result;

echo json_encode($data);

You will get multiple columns from your SELECT statement and you're binding just one column in your bind_result($title) that will throw a warning, and also you're not fetching your data which will actually extract the data from result set and bind it to your variable.

Second, if you want to populate the field you would expect one result set right?(judging from your javascript) your query with LIKE clause might probably return more than one result, you will have to modify your query so that you can get a unique result.

Now lastly, what you're trying to achieve in your PHP code can more easily done with PDO rather than mysqli, one of the reasons being, you can easily fetch the result set with fetch_all function and json_encode it directly, mysqli has fetch_all too but I believe that is only compatible with the latest versions.

If you want to keep using MySQLI the solution would be this,

$result->bind_result($title, $total, ...); //bind all your columns here
$data = [];

while($stmt->fetch()){
$data['title'] = $title;
.
.
.
//fetch all the columns you need likewise.
}

echo json_encode($data);

I'd still suggest that you should migrate to PDO, which will help you to do all this stuff with much more ease, read more about PDO here: http://php.net/manual/en/class.pdostatement.php

Answer 2

You can do like this:

$data = array();
while ($result->fetch()) {
$data['title'] = $title;
$data['field2'] = $field2;
...
}
// echo '<pre>'.$data.'</pre>';
echo json_encode($data);

In jQuery code:

data = $.parseJSON(data);
var title = data.title;
var field2 = data.field2;
alert(title);

Answer 3

You can remove space here:

$result ->bind_param("s", $search);

$result->bind_param("s", $search);