BlueSun3k1 BlueSun3k1 - 2 months ago 5
Ajax Question

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

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.

Answer

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.