Posted By

halk on 11/16/12


Tagged

mysql jquery json api JSONP getJson


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

balmeet80


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


 / Published in: PHP
 

This is a public jsonp api. I use it with jQuery Like This: $.getJSON("http://yourdomain/yourpath/jsonpapi.php?method=getSchema&params=yourdb_name&jsoncallback=?", function(data){ //DO SOMETHING WITH THE DATA HERE } );

  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. }
  39. ////////////////////////////////////////////////////////////////////////////////////////////////////////////
  40. /**
  41.  * FUNCTIONS WHICH RETURN JSONP RESULTS
  42.  */
  43. /**
  44.  * fetchTable()
  45.  * RETURNS AN ENTIRE MYSQL TABLE (JSONP)
  46.  * @param mixed $dbtable
  47.  * @return void
  48.  */
  49. function fetchTable($dbtable){
  50. $sql="SELECT * FROM $dbtable";
  51. $results = array();
  52. $user_sql = mysql_query($sql)or die(mysql_error());
  53. while($row=mysql_fetch_array($user_sql,MYSQL_ASSOC)){
  54. $results[] = $row;
  55. }
  56. echo $_GET['jsoncallback'].'('.json_encode($results).')'; //ECHO RESULTS IN JSONP FORMAT
  57. }
  58.  
  59. /**
  60.  * showTables()
  61.  *
  62.  * @param mixed $dbname
  63.  * @return void
  64.  */
  65. function showTables($dbname){
  66. $sql = "SHOW FULL TABLES IN hskitts_$dbname";
  67. $results = array();
  68. $tables = mysql_query($sql)or die(mysql_error());
  69. while($row=mysql_fetch_array($tables,MYSQL_ASSOC)){
  70. $results[] = $row;
  71. }
  72. echo $_GET['jsoncallback']."(".json_encode($results).")";
  73. }
  74.  
  75. /**
  76.  * showColumns()
  77.  *
  78.  * @param mixed $dbtable
  79.  * @return void
  80.  */
  81. function showColumns($dbtable){
  82. $sql = "SHOW FULL COLUMNS IN $dbtable";
  83. $results = array();
  84. $columns = mysql_query($sql)or die(mysql_error());
  85. while($row=mysql_fetch_array($columns,MYSQL_ASSOC)){
  86. $results[] = $row;
  87. }
  88. echo $_GET['jsoncallback']."(".json_encode($results).")";
  89. }
  90.  
  91. /**
  92.  * _show_cols()
  93.  * internal used by getSchema
  94.  * SCRIPT UTILITY FUNCTION
  95.  * @param mixed $dbtable
  96.  * @return
  97.  */
  98. function _show_cols($dbtable){
  99. $sql = "SHOW FULL COLUMNS IN $dbtable";
  100. $results = array();
  101. $columns = mysql_query($sql)or die(mysql_error());
  102. while($row=mysql_fetch_array($columns,MYSQL_ASSOC)){
  103. $results[] = $row;
  104. }
  105. return $results;
  106. }
  107.  
  108. /**
  109.  * getSchema()
  110.  * GET THE FULL SCHEMA MAP TABLES AND COLUMNS
  111.  * OF A MYSQL DATABASE. RETURN AS JSONP
  112.  * @param mixed $dbname
  113.  * @return void
  114.  */
  115. function getSchema($dbname){
  116. $schema=array();
  117. $sql = "SHOW TABLES IN $DBNAME";
  118. $results = array();
  119. $tables = mysql_query($sql)or die(mysql_error());
  120. while($row=mysql_fetch_array($tables,MYSQL_ASSOC)){
  121. $results[] = $row;
  122. }
  123. for($i=0;$i<count($results);$i++){
  124. foreach($results[$i] as $key => $value){
  125. $schema[$value] = _show_cols($value);
  126. }
  127. }
  128. echo $_GET['jsoncallback']."(".json_encode($schema).")";
  129. }

Report this snippet  

You need to login to post a comment.