J. Doe J. Doe - 1 month ago 9
Ajax Question

How Can I get a full result from a sql query using php and then display that with javascript

I have a php script that executes a query. The result can however be multiple rows. How can I do a json_encode of the rows of data and then use javascript to get that data and display it in a table?

This file echos two json encoded lines, each representing one row

<?php
include("config.php");
session_start();
$user = $_SESSION["loggedIn"];
$sql = "SELECT * FROM tickets where SenderName =\"$user\" OR SenderName = \"Yoon\"";
$result = $conn->query($sql);
while($row = $result->fetch_assoc()){
echo json_encode($row);
}
?>


I wanna use the javascript script and ajax to get both the rows and then create a function that creates a table based on the result

<!DOCTYPE html>
<html>
<head>
<link rel="stylesheet" type="text/css" href="buttons.css">
<script type="text/javascript">
function processData() {
var httpRequest;
//alert("hi there");

if (window.XMLHttpRequest) { // Mozilla, Safari, ...
httpRequest = new XMLHttpRequest();
if (httpRequest.overrideMimeType) {
httpRequest.overrideMimeType('text/xml');
}
}
else if (window.ActiveXObject) { // IE
try {
httpRequest = new ActiveXObject("Msxml2.XMLHTTP");
}
catch (e) {
try {
httpRequest = new ActiveXObject("Microsoft.XMLHTTP");
}
catch (e) {}
}
}
if (!httpRequest) {
alert('Giving up :( Cannot create an XMLHTTP instance');
return false;
}
httpRequest.open('GET', 'userTables.php', true);

httpRequest.onreadystatechange = function() {
showTickets(httpRequest);
}
httpRequest.send();
}
function show(){
document.getElementById("buttons").style.visibility= "hidden" ;
}
function showTickets(httpRequest){
if (httpRequest.readyState == 4) {
if (httpRequest.status == 200){
alert("hi there");
var data = JSON.parse(httpRequest.responseText);
alert(data["SenderName"]); //I can only get the data in a key value array if i have just one row as the result
}
else{
alert('Problem with request');
}
}
}

function createTable(httpRequest){
//need to add innerhtml to some div that shows the table
}



</script>
</head>
<body>
<h1>Welcome! </h1>
<div id="buttons">
<button class="myButton" onclick="processData()">View My Tickets</button>
<button class="myButton">Submit Ticket</button>
<button class="myButton">Change my Password</button>
</div>
<div id = "table" >

</div>
</body>
</html>


I think the html will look something to display the table would look something like this:

<table align="left" cellspacing="5" cellpadding="8">
<tr><td align="left"><b>TicketNum</b></td>
<td align="left"><b>Recieved</b></td>
<td align="left"><b>SenderName</b></td>
<td align="left"><b>Sender Email</b></td>
<td align="left"><b>Subject</b></td>
<td align="left"><b>Tech</b></td>
<td align="left"><b>Status</b></td>
<td align="left"><b>Select</b></td></tr>';

// mysqli_fetch_array will return a row of data from the query
// until no further data is available
while($row = $result->fetch_assoc() ){

echo '<tr><td align="left">' .
row['TicketNum'] . '</td><td align="left">' .
row['Recieved'] . '</td><td align="left">' .
row['SenderName'] . '</td><td align="left">' .
row['SenderEmail'] . '</td><td align="left">' .
row['Subject'] . '</td><td align="left">' .
row['Tech'] . '</td><td align="left">' .
row['Status'] . '</td><td align="left">' .

'</td><td align="left">';
</table>';

Answer

1 be sure you are outputting all rows in php just once

$result = $conn->query($sql);
$data = array();
while($row = $result->fetch_assoc()){
    $data[] = $row;
}
exit(json_encode($data));  // there probably is nothing else, just exit

2 in your javascript just try to use console instead of alert - if you need help with this, it may be a new question, but google "console" debugging in firefox/chrome.

var data = JSON.parse(httpRequest.responseText);
console.log(data); // you will have an object of the rows now

3 now you need to use javascript to populate all the rows by creating html...try something like this

var data = JSON.parse(httpRequest.responseText);
for (var key in data) {
    // skip loop if the property is from prototype
    if (!data.hasOwnProperty(key)) continue;

    var row = data[key];
    console.log(row);
    // now you need to make your html using javascript
    // how to do that is a new question, imho
}