How to expose data from a mysql database via php/JSON for access via HTTP

Posted on by

Situation:
You got some (client) application, that needs data from a mysql database.
The application cannot connect to the database itself. It sends a HTTP GET/POST request to a webserver, that gets the data out of the mysql database and packs it into a JSON Array. This JSON Array is sent back to the client.

(Follow up situation, the client has to request and parse the data)

What you need:

– mysql database
– webserver with php

This is the php code for the webserver (inspired from )

<?php
  // connect to Database
  // database specifications, adapt your configuration
  $dbUri = "...";
  $dbUser = "...";
  $dbPassword = "...";
  $dbDatabaseName = "...";

  // establish connection
  $db = mysql_connect($dbUri, $dbUser, $dbPassword) OR
        die(mysql_error());

  mysql_select_db($dbDatabaseName, $db) OR
        die(mysql_error());

  // example query, adapt to your needs
  $sqlquery = "SELECT ... FROM ... WHERE ... ORDER BY ... ASC;";
  
  $result = mysql_query($sqlquery) OR
        die(mysql_error());
  
  if (mysql_errno()) {
    header("HTTP/1.1 500 Internal Server Error");
  } else {
    $rows = array();
    // fill array
    while($r = mysql_fetch_assoc($result)) {
        $rows[] = $r;
    }
    // encode array as JSON and print it
    print json_encode($rows);
  }
?>

Comments are closed.