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

Vishnu Nair · Accepted Answer · 2016-02-28 10:36:25Z

2

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

answered Feb 28, 2016 at 10:36

Vishnu Nair

2,43317 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Roxx Over a year ago

I tried your code and i can see data is retuning under response tab in firebug. but it is not coming in text box. it seems like issue with ajax code.

Roxx Over a year ago

{"title":"3rd posting"} this is what i am getting.

Vishnu Nair Over a year ago

I think in your javascript code, you should do this, $('#title').val(data.title); , you don't need quotes around it, same for total.

Roxx Over a year ago

thanks. but it seems like issue with success: function(data) { as i tried alert('test'); but it doesn't showing alert box

Roxx Over a year ago

Issue resolved. I removed // dataType: 'json',. Voted up and answer accepted.

score 1 · Accepted Answer · 2016-02-28 11:07:27Z

1

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);

edited Feb 28, 2016 at 11:07

answered Feb 28, 2016 at 10:16

user2404892

4 Comments

Roxx Over a year ago

what about $result->bind_result. do i need to declare all fields name?

user2404892 Over a year ago

Yes if you want whole fields data you need to declare all fields.

Roxx Over a year ago

your code is working but not able to show the json data in text box.

Roxx Over a year ago

Your answer helped me to fix the issue. but i also got helped from @vincent. Voted up your answer

user2404892 · Accepted Answer · 2016-02-28 10:03:56Z

0

You can remove space here:

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

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

answered Feb 28, 2016 at 10:03

user2404892

5 Comments

Roxx Over a year ago

Mate, thanks for your answer but code is working fine till here $result->store_result(); in autofill.php

user2404892 Over a year ago

You can try $result->get_result($title);

Roxx Over a year ago

Actually i need to return all data from table in json format. Thats what i am missing at the moment

user2404892 Over a year ago

Are you getting data in $data or $result?

Roxx Over a year ago

i am getting data till here $result->bind_result($title); i am not sure how to return all data from table in JSON format

Collectives™ on Stack Overflow

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

3 Answers 3

5 Comments

4 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

4 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related