Publish MySQL table(s) as JSON using PHP webservice

api.php

<?php

# Exposes public API to retrieve select data from the Corvallis
# Recycles database.
#
# useage: HTTP GET call to <yoururl>?cmp=1&cat=1&itm=1
#
# note 1: You can request only specific tables if you desire, 
# simply set the table flag to 0 if not desired.
# 
# note 2: You will ALWAYS get any additional join table(s)
# that relate to the primary table selected. Example:
# if you request only the companies table, the returned
# JSON will encapsulate both the Companies Table + the 
# CompaniesCategories join table.  In this application, the
# odds are very high that if a primary table was altered, 
# the join tables related to that primary table also 
# were altered. 

include('includes/connectdb.php');

# check that a get request exists
if( !( (isset($_GET['cmp'])  && ($_GET['cmp'] == 1) ) ||
       (isset($_GET['cat'])  && ($_GET['cat'] == 1) ) ||
       (isset($_GET['itm'])  && ($_GET['itm'] == 1) ) ) ) {
  header('HTTP/1.1 400 Bad Request');
  die('HTTP/1.1 400 Bad Request');
}

# flags for if join tables are needed.  Join tables will always be included
# if a table that references them is requested.
$needCmpCat = FALSE;
$needItmCat = FALSE;

# connect to database
$mysqli = new mysqli($hostname, $username, $password, $db_name);
if ($mysqli->connect_error) {
  die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
}

# retrieve all Companies
if(isset($_GET['cmp']) && ($_GET['cmp'] == 1) ) { 
  if(!$results = $mysqli->query("SELECT * FROM Companies")) {
    die('Companies Query Failed');
  }

  while( $row = $results->fetch_assoc() )
  {
    $companyObj[] = $row;
  }

  # add companies to data array
  $data['Companies'] = $companyObj;

  # set flag to get CompanyCategory table later
  $needCmpCat = TRUE;
}

# retrieve all Categories
if(isset($_GET['cat']) && ($_GET['cat'] == 1) ) { 
  if(!$results = $mysqli->query("SELECT * FROM Categories")) {
    die('Categories Query Failed');
  }

  while( $row = $results->fetch_assoc() )
  {
    $categoryObj[] = $row;
  }

  # add categories to data object
  $data['Categories'] = $categoryObj;

  # set flags to get both CompanyCategory and ItemCategory tables later
  $needCmpCat = TRUE;
  $needItmCat = TRUE;
}

# retrieve all Items
if(isset($_GET['itm']) && ($_GET['itm'] == 1) ) { 
  if(!$results = $mysqli->query("SELECT * FROM Items")) {
    die('Items Query Failed');
  }

  while( $row = $results->fetch_assoc() )
  {
    $itemObj[] = $row;
  }

  # add items to data object
  $data['Items'] = $itemsObj;

  # set flag to get ItemCategory table later
  $needItmCat = TRUE;
}


if( $needCmpCat  ) {
  # retrieve all CompanyCategories
  if(!$results = $mysqli->query("SELECT * FROM CompanyCategories")) {
    die('CompanyCategory Query Failed');
  }

  while( $row = $results->fetch_assoc() )
  {
    $ccObj[] = $row;
  }

  # add CompanyCategories to data object
  $data['CompanyCategories'] = $ccObj;
}

# retrieve all ItemCategories
if( $needItmCat ) {
  if(!$results = $mysqli->query("SELECT * FROM ItemCategories")) {
    die('ItemCategory Query Failed');
  }

  while( $row = $results->fetch_assoc() )
  {
    $icObj[] = $row;
  }

  # add ItemCategories to data object
  $data['ItemCategories'] = $icObj;
}


$jsonReturn = json_encode(array(
    "Database" => $data,
    )
);
echo $jsonReturn;
  
?>