How to Return Web Service Data from a Database - dummies

How to Return Web Service Data from a Database

By Steve Suehring, Janet Valade

A frequent use of web services is to retrieve information from a database. Here, you look at returning simple data from a database. You will use a database that marks whether or not a certain website is up and operational.

Create the database

The web service then simply returns “Up” or “Down” based on the contents of the database table. You use a database called sites for this example. Therefore, the first step is to create the database itself, with the command:

mysqladmin -u <yourUser> -p create sites

The <yourUser> in that command would be the user that you have that can create databases. If you’re using a shared hosting provider, you might not be able to create databases. If that’s the case, then you can use whatever database the hosting provider has created for you. If you’re using a MySQL server on your local computer, then the user is probably called root.

The database table will be called siteStatus and the CREATE statement for it is as follows:

CREATE TABLE siteStatus (
siteURL VARCHAR(255),
siteStatus VARCHAR(10)

You can enter that SQL into the MySQL Command Line Interface (CLI) to create the table. Be sure to connect to or use the sites database when creating the table, with the command:

CONNECT sites;


USE sites;

Once the database has been created, a single row can be added for this demonstration:

INSERT INTO siteStatus (siteURL,siteStatus) VALUES ('','Up');

Create the web service

The web service is created by setting up the MySQL connection, querying the database, and then returning the data. Of course, there’s also error handling, in case something goes wrong with the query.

Here is the code for creating this web service.

$header = "Content-Type: application/json";
$dbLink = mysqli_connect('localhost','USER','PASSWORD','sites');
if (!$dbLink) {
    $row = array("siteStatus" => "Database Error");
    print json_encode($row);
} else {
    $query = "SELECT siteStatus FROM siteStatus WHERE siteURL = ''";
    if ($result = mysqli_query($dbLink,$query)) {
        $row = $result->fetch_array(MYSQLI_ASSOC);
        if (is_null($row)) {
            $row = array("siteStatus" => "Error - Site Not Found");
    } else {
        $row = array("siteStatus" => "General Error");
    print json_encode($row);
} // End else condition (for database connection)

The code contains a good amount of error handling, including error handling if the database connection can’t be established, if there’s a problem with the query, or if the site wasn’t found. In all these cases, the end result is that output is sent to the user thanks to the json_encode($row).

This is an important point with web services: Send output back to the web service consumer indicating that there was an error, rather than merely exiting.

You should always include feedback in the output of the web service for error conditions so that the person calling the web service can handle the error.