A Simple Example for Converting MySQL Data Result to JSON with PHP

Generally, most PHP applications work with MySQL as the back-end database and within PHP we can extract the data via the MySQLi class. When it comes to thinking about JSON with PHP and MySQL, it can be rather daunting. This is the precise reason for writing this tutorial, to ultimately demystify the subject.

A Simple Example for Converting MySQL Data Result to JSON with PHP

Generally, most PHP applications work with MySQL as the back-end database and within PHP we can extract the data via the MySQLi class. When it comes to thinking about JSON with PHP and MySQL, it can be rather daunting. This is the precise reason for writing this tutorial, to ultimately demystify the subject.

The two demos in this tutorial show that you can convert your result set to JSON without even looping through and reading the data within a while loop first. Unless of course, you need to do this, but if not, the script is going to be quicker.

Prerequisites

To get started with this tutorial on how to convert data from the MySQL Engine to JSON, you must have the following environment at hand.

  • >= PHP Version 5.0.7 Installed
  • MySQL Installed
  • A web server (Apache).
  • An IDE, preferably one that can process PHP nicely so it’s easier on the eye.

If you’ve got these in place already, then we can crack on and get into the exciting part.

MySQL Fetch Assoc to JSON

Let us get going with the first example, creating JSON from the MySQLi function, fetch_assoc. The important piece of code within the following script is json_encode, this is where the magic happens.

try {
            // Try Connect to the DB with new MySqli object - Params {hostname, userid, password, dbname}
            $mysqli = new mysqli("localhost", "root", "", "mysqli_examples");

            
            $statement = $mysqli->prepare("select username, first_name, last_name, gender from users limit 10");


            $statement->execute(); // Execute the statement.
            $result = $statement->get_result(); // Binds the last executed statement as a result.

            echo json_encode(($result->fetch_assoc())); // Parse to JSON and print.
  
        } catch (mysqli_sql_exception $e) { // Failed to connect? Lets see the exception details..
            echo "MySQLi Error Code: " . $e->getCode() . "<br />";
            echo "Exception Msg: " . $e->getMessage();
            exit(); // exit and close connection.
        }

        $mysqli->close(); // finally, close the connection

Copy

The above code printed the following, formatted JSON to the page.

{
	"username": "rogers63",
	"first_name": "david",
	"last_name": "john",
	"gender": "Female"
}

Copy

A very nice web tool to always check if your JSON is correctly formatted is a website called JSONLint. I highly recommend using it every time you create JSON as not only does it validate the data for you, it error checks and tell you exactly where the erroneous line lays. Of course, this is great for debugging. See below the JSONLinter in action.

JSON Validation Fetch Assoc

MySQL Fetch Array to JSON PHP

Using fetch array to convert to JSON may be a bit over the top, depending on what you want. The reason being is that fetch_array outputs your data with an associative and a numbered index. Meaning that the JSON will then hold both of these when converted. Here is the example, with the json_encode line being the most important –

try {
            // Try Connect to the DB with new MySqli object - Params {hostname, userid, password, dbname}
            $mysqli = new mysqli("localhost", "root", "", "mysqli_examples");

            
            $statement = $mysqli->prepare("select username, first_name, last_name, gender from users limit 10");


            $statement->execute(); // Execute the statement.
            $result = $statement->get_result(); // Binds the last executed statement as a result.

            echo json_encode(($result->fetch_array())); // Parse to JSON and print.
  
        } catch (mysqli_sql_exception $e) { // Failed to connect? Lets see the exception details..
            echo "MySQLi Error Code: " . $e->getCode() . "<br />";
            echo "Exception Msg: " . $e->getMessage();
            exit(); // exit and close connection.
        }

        $mysqli->close(); // finally, close the connection

Copy

Which outputs the following JSON –

{
	"0": "rogers63",
	"username": "rogers63",
	"1": "david",
	"first_name": "david",
	"2": "john",
	"last_name": "john",
	"3": "Female",
	"gender": "Female"
}

Copy

Again, quickly drop this into the JSONLint tool to make sure we have valid JSON.

JSON validation fetch array php

The output, as explained before, has two types of access keys, an integer, and a string. If you don’t need both, you could use a parameter within the fetch_array function to filter either one out. See my in-depth post about MySQLi functions here.

Summary

MySQL To JSON really is that simple, with use of the excellent json_encode function we can parse MySQL results extremely quickly and easily. The function can be used with many other data formats too, it doesn’t have to be a result from MySQL, it can be a manually created array, for instance.

Check out the json_encode function on the PHP documentation for further reading. Feel free to use any of the code in this post in your own projects. If you have any questions, leave it in the comments and I will get back to you as soon as I can.

Finally, I mentioned the JSONLint web tool and would strongly advise you to check it out. It’s an invaluable, free tool that helps when working with JSON no matter what you’re trying to achieve

Tags: