Advanced Search

JSONP Public API with jQuery getJSON (callback) (MySql)

/ Published in: PHP
Save to your folder(s)
This is a public jsonp api. I use it with jQuery Like This:
$.getJSON("http://yourdomain/yourpath/jsonp_api.php?method=getSchema&params=your_db_name&jsoncallback=?",
function(data){
//DO SOMETHING WITH THE DATA HERE
}
);

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. /**
  2.  * JSON API FOR MYSQL
  3.  */
  4.  ////////////////////////////////////////////////////////////////////////////////////////////////////
  5.  //         CONFIGURATION SETTING VARIABLES FOR DATABASE CONNECTION
  6.  $HOST="localhost";
  7.  $USER="root";
  8.  $PASS="";
  9.  $DBNAME = 'your_db_name';
  10.  ///////////////////////////////////////////////////////////////////////////////////////////////////
  11.  /**
  12.   * CONNECT TO MYSQL
  13.   */
  14. $CON = mysql_connect($HOST,$USER,$PASS);
  15. if(!$CON)
  16. {
  17. die("connection to database failed");
  18. }
  19. $dataselect = mysql_select_db($DBNAME,$CON);
  20. if(!$dataselect)
  21. {
  22. die("Database namelist not selected".mysql_error());
  23. }
  24.  
  25.  
  26. /**
  27.  * EXECUTE THE PASSED IN METHOD WITH OR WITHOUT PARAMETERS
  28.  * (multiple parameters are specified by a ',' separated string)
  29.  */
  30. if(function_exists(stripslashes(trim($_GET['method'])))){  //IF THE FUNCTION EXISTS (IN THIS SCRIPT)
  31.         $method = stripslashes(trim($_GET['method']));
  32.         $params = str_replace("'", '',stripslashes(trim($_GET['params']))); //strip single quotes if used                                                           
  33.         $opts= explode(',',$params); //turn the parameters string into an array
  34.         $function = new ReflectionFunction($method);  //instantiate the function as an object
  35.         $function->invokeArgs($opts); //invoke the function with an array of arguments (if given)
  36.     }else{ //ELSE THE FUNCTION DOES NOT EXIST
  37.         echo "error the function you called : ".$_GET['method']."(".$_GET['params'].")"." does not exist";
  38.         exit;
  39.     }
  40. exit;
  41. ////////////////////////////////////////////////////////////////////////////////////////////////////////////
  42. /**
  43.  *  FUNCTIONS WHICH RETURN JSONP RESULTS
  44.  */
  45. /**
  46.  * fetchTable()
  47.  *  RETURNS AN ENTIRE MYSQL TABLE (JSONP)
  48.  * @param mixed $dbtable
  49.  * @return void
  50.  */
  51. function fetchTable($dbtable){
  52.     $sql="SELECT * FROM $dbtable";
  53.     $results = array();
  54.     $user_sql = mysql_query($sql)or die(mysql_error());  
  55.     while($row=mysql_fetch_array($user_sql,MYSQL_ASSOC)){
  56.         $results[] = $row;
  57.     }
  58.     echo $_GET['jsoncallback'].'('.json_encode($results).')'; //ECHO RESULTS IN JSONP FORMAT
  59. }
  60.  
  61. /**
  62.  * showTables()
  63.  * 
  64.  * @param mixed $dbname
  65.  * @return void
  66.  */
  67. function showTables($dbname){
  68.     $sql = "SHOW FULL TABLES IN hskitts_$dbname";
  69.     $results = array();
  70.     $tables = mysql_query($sql)or die(mysql_error());
  71.     while($row=mysql_fetch_array($tables,MYSQL_ASSOC)){
  72.         $results[] = $row;
  73.     }
  74.     echo $_GET['jsoncallback']."(".json_encode($results).")";
  75. }
  76.  
  77. /**
  78.  * showColumns()
  79.  * 
  80.  * @param mixed $dbtable
  81.  * @return void
  82.  */
  83. function showColumns($dbtable){
  84.     $sql = "SHOW FULL COLUMNS IN $dbtable";
  85.     $results = array();
  86.     $columns = mysql_query($sql)or die(mysql_error());
  87.     while($row=mysql_fetch_array($columns,MYSQL_ASSOC)){
  88.         $results[] = $row;
  89.     }
  90.     echo $_GET['jsoncallback']."(".json_encode($results).")";
  91. }
  92.  
  93. /**
  94.  * _show_cols()
  95.  * internal used by getSchema
  96.  * SCRIPT UTILITY FUNCTION
  97.  * @param mixed $dbtable
  98.  * @return
  99.  */
  100. function _show_cols($dbtable){
  101.     $sql = "SHOW FULL COLUMNS IN $dbtable";
  102.     $results = array();
  103.     $columns = mysql_query($sql)or die(mysql_error());
  104.     while($row=mysql_fetch_array($columns,MYSQL_ASSOC)){
  105.         $results[] = $row;
  106.     }
  107.     return $results;
  108. }
  109.  
  110. /**
  111.  * getSchema()
  112.  * GET THE FULL SCHEMA MAP TABLES AND COLUMNS
  113.  * OF A MYSQL DATABASE.  RETURN AS JSONP
  114.  * @param mixed $dbname
  115.  * @return void
  116.  */
  117. function getSchema($dbname){
  118.     $schema=array();
  119.     $sql = "SHOW TABLES IN $DBNAME";
  120.     $results = array();
  121.     $tables = mysql_query($sql)or die(mysql_error());
  122.     while($row=mysql_fetch_array($tables,MYSQL_ASSOC)){
  123.         $results[] = $row;
  124.     }
  125.     for($i=0;$i<count($results);$i++){
  126.         foreach($results[$i] as $key => $value){
  127.             $schema[$value] = _show_cols($value);
  128.         }
  129.     }
  130.     echo $_GET['jsoncallback']."(".json_encode($schema).")";
  131. }

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.