Pages

Wednesday, December 19, 2012

PHP: A webservice to query MySQL database via JSON


Here is a very basic webservice to query mysql database via GET or POST methods. It will return the result in json format.
<?php
/**
 * @filename service.php
 * @author Abhishek Kumar
**/

include 'config.php';

function db_query($iQuery, $iReturn)
{
 $output = array();
 if($connection = mysql_connect(DB_HOST, DB_USER, DB_PASS))
  if($database = mysql_select_db(DB_SCHEMA))
   if($result = mysql_query($iQuery))
   {
    if($iReturn)
    {
     while ($data[] = mysql_fetch_array($result, MYSQL_ASSOC));
     mysql_free_result($result);
     $output['success'] = $data;
    }
    else
     $output['success'] = $result;
    mysql_close($connection);    
   }
   else
    $output['failure'] = mysql_error();  
  else
   $output['failure'] = mysql_error();
 else
  $output['failure'] = mysql_error();
 
 return $output;
}

$query = (!isset($_REQUEST['query']))? NULL : $_REQUEST['query'];
$type = (!isset($_REQUEST['type']))? 0 : $_REQUEST['type'];

$output = db_query($query, ($type == 1));

print json_encode($output);
 
?>

<?php
/**
 * @filename config.php
 * @author Abhishek Kumar
**/

$isRemote = false;

if($isRemote)
{
 define('DB_HOST','www.domain.com');
 define('DB_USER','admin');
 define('DB_PASS','xyz');
}
else
{
 define('DB_HOST','127.0.0.1');
 define('DB_USER','root');
 define('DB_PASS','');
}

define('DB_SCHEMA','test');

?>
We can also make the service.php as class. See below:
<?php
/**
 * @filename dbagent.php
 * @author Abhishek Kumar
**/

include 'config.php';

class dbagent
{
    public static function query($iQuery, $iReturn)
    {
        $output = array();
        if ($connection = mysql_connect(DB_HOST, DB_USER, DB_PASS)) {
            if ($database = mysql_select_db(DB_SCHEMA)) {
                if ($result = mysql_query($iQuery)) {
                    if ($iReturn) {
                        $output['num_rows'] = mysql_num_rows($result);
                        while ($data[] = mysql_fetch_array($result, MYSQL_ASSOC));
                        mysql_free_result($result);
                        $output['success'] = $data;
                    } else {
                        $output['affected_rows'] = mysql_affected_rows();
                        $output['insert_id']     = mysql_insert_id();
                        $output['success']       = $result;
                    }
                    mysql_close($connection);
                } else
                    $output['failure'] = mysql_error();
            } else
                $output['failure'] = mysql_error();
        } else
            $output['failure'] = mysql_error();
        
        return $output;
    }
}
 
?>

No comments:

Post a Comment