Generating JSON String with PHP

Today I’m going to share this code I used to generate a JSON string with data from MySQL database.

For those not yet familiar, JSON is a lightweight data interchange format (like XML but it is lightweight).

It has been used by companies like Google and Facebook in their APIs.

Recently, I needed a JSON string to get data from the web to the Android app I’m working on.

The PHP file gets a parameter company_id to select few data related to a company.

Please note that this is not a production ready code, but is very useful to get you started and can serve as quick reference.

PHP Code

Here’s a short PHP code to generate JSON string. It is really easy compared to using XML.

<?php
// connection to the database
$host = "your_host";
$username = "your_username";
$password = "your_password";
$db_name = "your_database_name";

try {
    $con = new PDO("mysql:host={$host};dbname={$db_name}", $username, $password);
}catch(PDOException $exception){
    echo "Connection error: " . $exception->getMessage();
}

// parameter
$company_id = isset($_GET['company_id']) ? $_GET['company_id'] : die();

// SQL query and prepared statement
$stmt = $con->prepare("SELECT id, name, description FROM document_sequences WHERE company_id = :company_id");
$stmt->bindParam(':company_id', $company_id);
$stmt->execute();

// get the results in array
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

// you can remove this line if you want
$results = array('DocumentSequence' => $results);

// now show the json tring
echo json_encode($results);
?>

Viewing JSON String

The PHP code above generates JSON string that may cause real pain in your heart, it looks like this:

{"DocumentSequence":[{"id":"4","name":"Store two","description":"Document sequence for store 2."},{"id":"5","name":"Store One","description":"Document Sequence for store 1."},{"id":"6","name":"Store 3","description":"Document sequence for store three."}]}

But the good news is, there’s an excellent JSON viewer online that we can use for a better JSON vieweing. It is called Online JSON Viewer.

You just have to copy your JSON string (or load via URL) to the “Text” tab and then click the “Viewer” tab. JSON string above will now look like this: