Select multiple rows into array and send them via AJAX

Question

I am trying to run a SELECT query in PHP and then multiple rows are selected, but I need to fetch them into an array and then use: echo json_encode($array). After That I need to get this array into AJAX.

Here is the PHP code:

$val = $_POST['data1'];
$search = "SELECT * FROM employee WHERE emp_name = :val OR salary = :val OR date_employed = :val";
$insertStmt = $conn->prepare($search);
$insertStmt->bindValue(":val", $val);
$insertStmt->execute();
$insertStmt->fetchAll();
//echo "success";
//$lastid = $conn->lastInsertId();
$i = 0;
foreach($insertStmt as $row)
{
 $arr[$i] = $row;
 $i++;
}
echo json_encode($arr);

The problem is that I can't get all the lines of this array into AJAX so I can append them into some table. Here is the script:

var txt = $("#txtSearch").val();
$.ajax({
 url: 'search.php', // Sending variable emp, pos, and sal, into this url
 type: 'POST', // I will get variable and use them inside my PHP code using $_POST['emp']
 data: {
 data1: txt
 }, //Now we can use $_POST[data1];
 dataType: "json", // text or html or json or script
 success: function(arr) {
 for() {
 // Here I don't know how to get the rows and display them in a table
 }
 },
 error:function(arr) {
 alert("data not added");
 }
});

Answer 1

You need to loop over your "arr" data in the success callback. Something along the lines of:

var txt = $("#txtSearch").val();
$.ajax
({
 url: 'search.php', //Sending variable emp, pos, and sal, into this url
 type: 'POST', //I will get variable and use them inside my PHP code using $_POST['emp']
 data: {data1: txt},//Now we can use $_POST[data1];
 dataType: "json", //text or html or json or script
 success:function(arr)
 {
 var my_table = "";
 $.each( arr, function( key, row ) {
 my_table += "<tr>";
 my_table += "<td>"+row['employee_first_name']+"</td>";
 my_table += "<td>"+row['employee_last_name']+"</td>";
 my_table += "</tr>";
 });
 my_table = "<table>" + my_table + "</table>";
 $(document).append(my_table);
 },
 error:function(arr)
 {
 alert("data not added");
 }
 });

• androidnation: That is my problem. Can't know what to write inside $.each
• Simon Deconde: I see. I've edited my answer with a more concrete example on how to generate a HTML table with the results received via AJAX. Is that what you're after?
• androidnation: I tried it and still going to the error function and getting alert=Data not added
• Simon Deconde: Seems like the AJAX response returned by your server isn't valid, that's why the "error" callback is called instead of the "success" callback. Can you share the actual AJAX response returned by your server?
• androidnation: how to see the actual AJAX response, nothing appear in console

Answer 2

You could just return

json_encode($insertStmt->fetchAll());

Also, be sure to retrieve only characters in UTF-8 or JSON_encode will "crash".

• LoïcR: Refer to others answers and use each (:
• jeroen: I don't see how this answers the question, you've cleaned up the php but the result is exactly the same.

Answer 3

Your success function should be like this :

success:function(arr)
 {
 $.each(arr,function (i,item) {
 alert(item.YOUR_KEY);
 });
 }