PHP-JQuery-Ajax: How to Populate table with all results from mysql query result (json encoded array)

Question

I've been trying to figure out how to load the results of a search into a table but no matter what I do, I'm only getting one single result instead of the 2 (sample size) rows that I have in the table in the db.

This is the MySQL Code:

if (isset($_POST['search_value'])) {
$search_value = mysqli_real_escape_string($conn, $_POST['search_value']);

$sql = "SELECT
        record_id,
        personal_id,
        name,
        status,
        entry_date
        FROM sample_db
        WHERE EmpID = '".$search_value."'";

$res = mysqli_query($conn, $sql) or die("Error: ".mysqli_error($conn));

$data = array();
while($row = mysqli_fetch_array($res)){
$data = array(
        'tb_record_id' => $row['record_id'],
        'tb_personal_id' => $row['personal_id'],
        'tb_name' => $row['name'],
        'tb_status' => $row['status'],
        'tb_entry_date' => $row['entry_date'],
        );
        }
        echo json_encode($data);

}

I've read several examples where the array is built as $data[] = array( data goes here) instead of $data = array(data goes here) but whenever I try $data[], it doesn't return anything to the table BUT the console log does show all the results within the array.

also, using dataType: 'json' doesn't seem to work either.

The only way I've tried this so far is by giving an id to each <td>.

ajax code

$.ajax({
        type : 'POST',
        url  : 'search_fetch.php',
        data : data,
        cache: false,
        success :  function(response)
                {
                result = jQuery.parseJSON(response);                        

                $("#list_p_id").append(result.tb_personal_id);
                $("#list_name").append(result.tb_name);
                $("#list_status").append(result.tb_status);                                     
                $("#list_date").append(result.tb_entry_date);
     }
});

How can I populate the table with all available results?

Also in case if helps, I am open to not use an array. I just don't know another way of how to send the results of a query to an ajax response.

you'll have to use `$data[] = array...` to get all results. Right now you're overwriting `$data` with every new result. And move the output (`echo jeson_encode($data)`) outside the while-loop. — Jeff, Sep 29 '16 at 16:57
then, in js: you don't need to parseJson, will already be done by jQuery, I think. Then use `result[0].tb_personal_id` to get your data. — Jeff, Sep 29 '16 at 16:59
Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, Sep 29 '16 at 17:00
@Jeff I tried your suggestion and removed the result = jQuery.parseJSON(response); then used $data[] = array(...) but it does not appear to work. The console.log(response); shows every record that was queried (as you mentioned $data[] would do) but the values are not passed to the table. I tried $("#list_personal_id").append(response[0].tb_personal_id); and also $("#list_personal_id").append(response.tb_personal_id); — BlueSun3k1, Sep 29 '16 at 17:20
Thank you @RiggsFolly , I will review the code and make it safer after I get it to work. — BlueSun3k1, Sep 29 '16 at 17:27

Lucha Laura Hardie · Accepted Answer · 2016-09-29T18:39:44.507

2

In your while loop, you need to push each row of results onto your array in a way that doesn't overwrite the entire array each time.

For example:

while($row = mysqli_fetch_array($res))
{
    array_push($data, array(
        'tb_record_id' => $row['record_id'],
        'tb_personal_id' => $row['personal_id'],
        'tb_name' => $row['name'],
        'tb_status' => $row['status'],
        'tb_entry_date' => $row['entry_date'],
        )
    );
}

When you do this, keep in mind that $data is now an array of arrays, and you will need to access the items accordingly in your ajax.

ADDITIONAL INFORMATION TO ANSWER YOUR QUESTION:

I didn't realize you also need information about how to display the resulting data in your table...

You are going to need to use some kind of dynamic code to product your HTML in order to assign each table cell with a unique id.

When you have done that, you will need to use a loop in your ajax code so you can assign the resulting data to each table cell and display it.

Currently your loop will just overwrite the data into the existing elements over and over, meaning you only get one row of information.

edited Sep 29 '16 at 18:39

answered Sep 29 '16 at 17:06

Lucha Laura Hardie

429
1
3
10

I got the same result as Jeff's suggestion. Your suggestion does fetch all of the records (seen in the console log) but nothing is passed to the table. Also, the first ); should not have the ; (it gave me an error of an unexpected ; so I had to remove it). – BlueSun3k1 Sep 29 '16 at 17:26
Maybe you can paste the json response into your example so we can see it? Something is wrong in your ajax now for retrieving the result. – Lucha Laura Hardie Sep 29 '16 at 17:29
Of course, this is the result I got. [{"tb_record_id":"1","tb_personal_id":"1059966","tb_name":"Test Name 1","tb_status":"Pending" ,"tb_entry_date":"2016-09-28 11:56:49"},{"tb_record_id":"2","tb_personal_id":"1023659","tb_name":"Test Name 2","tb_status":"Processing" ,"tb_entry_date":"2016-09-29 14:32:54"}] – BlueSun3k1 Sep 29 '16 at 17:36
So am I right that your response is an array of objects then? – Lucha Laura Hardie Sep 29 '16 at 17:41
have you tried a simple alert statement to see if you can at least get the information to echo? That way, you know if it is the way you are accessing the json that is wrong, or if it is the code you are using to appen the data with that is wrong. – Lucha Laura Hardie Sep 29 '16 at 17:46
The alert returned the following: [object Object],[object Object] – BlueSun3k1 Sep 29 '16 at 17:47
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/124577/discussion-between-lucha-laura-hardie-and-bluesun3k1). – Lucha Laura Hardie Sep 29 '16 at 17:49

PHP-JQuery-Ajax: How to Populate table with all results from mysql query result (json encoded array)

1 Answers1