Mike Heavers

08/19/2015

Building off the previous PHP tutorial in which we insert data into a mySQL database with PHP, this is a quick example of how to output data from the database to JSON, so that we can utilize it via AJAX in javascript.

Starting with the PHP script:

<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "mydatabase";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$sql = "SELECT id, content FROM mytable";
$result = mysqli_query($conn, $sql);
$json = array();

if (mysqli_num_rows($result) > 0) {
    // output data of each row
    while($row = mysqli_fetch_assoc($result)) {
        $test_data[]=$row;
    }
    $json['responses']=$test_data;
} else {
    echo "No content yet";
}

mysqli_close($conn);
echo json_encode($json);

?>

Here, we're simply establishing a database connection, and then select our desired data from the table and assign it to a variable:

$sql = "SELECT id, content FROM creations";
$result = mysqli_query($conn, $sql);

Next we establish a variable for the json data:

$json = array();

Then we check to make sure data has been returned from the database, and if so, we loop through each table row, and add our content from the SQL select statement (id,content) to our $json array:

if (mysqli_num_rows($result) > 0) {
    // output data of each row
    while($row = mysqli_fetch_assoc($result)) {
        $test_data[]=$row;
        //echo "<div class='response'>" . $row["content"] . "</div>";
    }
    $json['responses']=$test_data;
}

Then we close the database connection, and echo out our final $json array using PHP's built-in json_encode method.

If we want to use this data in jQuery / ajax, we could simply:

$(document).ready(function(){
    $.ajax({
      dataType: "json",
      url: 'retrieve.php',
      success: success
    });

    function success(data){
        var responseData = data.responses;
        for (var i=0;i<responseData.length;i++){
            var responseText = responseData[i].content;
            var responseID = responseData[i].id;
            var $response = $('<div class="response">').text(responseID + ": " + responseText);
            $('.response-container').append($response);
        }
    }

});

Here we construct an ajax request and call the PHP script we just built (retrieve.php), and when we have successfully retrieved our database data, we call the "success" function:

$.ajax({
      dataType: "json",
      url: 'retrieve.php',
      success: success
    });

In the success function, we pass all our data, and access the "responses" array:

function success(data){
        var responseData = data.responses;
}

Then we loop through all that data and output each row to the DOM:

for (var i=0;i<responseData.length;i++){
            var responseText = responseData[i].content;
            var responseID = responseData[i].id;
            var $response = $('<div class="response">').text(responseID + ": " + responseText);
            $('.response-container').append($response);
        }