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 } );

Highlighted TextExpand

/**
 * JSON API FOR MYSQL
 */
 ////////////////////////////////////////////////////////////////////////////////////////////////////
 //         CONFIGURATION SETTING VARIABLES FOR DATABASE CONNECTION
 $HOST="localhost";
 $USER="root";
 $PASS="";
 $DBNAME = 'your_db_name';
 ///////////////////////////////////////////////////////////////////////////////////////////////////
 /**
  * CONNECT TO MYSQL
  */
$CON = mysql_connect($HOST,$USER,$PASS);
if(!$CON)
{
die("connection to database failed");
}
$dataselect = mysql_select_db($DBNAME,$CON);
if(!$dataselect)
{
die("Database namelist not selected".mysql_error());
}
    
    
/**
 * EXECUTE THE PASSED IN METHOD WITH OR WITHOUT PARAMETERS
 * (multiple parameters are specified by a ',' separated string)
 */
if(function_exists(stripslashes(trim($_GET['method'])))){  //IF THE FUNCTION EXISTS (IN THIS SCRIPT)
        $method = stripslashes(trim($_GET['method']));
        $params = str_replace("'", '',stripslashes(trim($_GET['params']))); //strip single quotes if used                                                           
        $opts= explode(',',$params); //turn the parameters string into an array
        $function = new ReflectionFunction($method);  //instantiate the function as an object
        $function->invokeArgs($opts); //invoke the function with an array of arguments (if given)
    }else{ //ELSE THE FUNCTION DOES NOT EXIST
        echo "error the function you called : ".$_GET['method']."(".$_GET['params'].")"." does not exist";
        exit;
    }
exit;
////////////////////////////////////////////////////////////////////////////////////////////////////////////
/**
 *  FUNCTIONS WHICH RETURN JSONP RESULTS
 */
/**
 * fetchTable()
 *  RETURNS AN ENTIRE MYSQL TABLE (JSONP)
 * @param mixed $dbtable
 * @return void
 */
function fetchTable($dbtable){
    $sql="SELECT * FROM $dbtable";
    $results = array();
    $user_sql = mysql_query($sql)or die(mysql_error());  
    while($row=mysql_fetch_array($user_sql,MYSQL_ASSOC)){
        $results[] = $row;
    }
    echo $_GET['jsoncallback'].'('.json_encode($results).')'; //ECHO RESULTS IN JSONP FORMAT
}

/**
 * showTables()
 * 
 * @param mixed $dbname
 * @return void
 */
function showTables($dbname){
    $sql = "SHOW FULL TABLES IN hskitts_$dbname";
    $results = array();
    $tables = mysql_query($sql)or die(mysql_error());
    while($row=mysql_fetch_array($tables,MYSQL_ASSOC)){
        $results[] = $row;
    }
    echo $_GET['jsoncallback']."(".json_encode($results).")";
}

/**
 * showColumns()
 * 
 * @param mixed $dbtable
 * @return void
 */
function showColumns($dbtable){
    $sql = "SHOW FULL COLUMNS IN $dbtable";
    $results = array();
    $columns = mysql_query($sql)or die(mysql_error());
    while($row=mysql_fetch_array($columns,MYSQL_ASSOC)){
        $results[] = $row;
    }
    echo $_GET['jsoncallback']."(".json_encode($results).")";
}

/**
 * _show_cols()
 * internal used by getSchema
 * SCRIPT UTILITY FUNCTION
 * @param mixed $dbtable
 * @return
 */
function _show_cols($dbtable){
    $sql = "SHOW FULL COLUMNS IN $dbtable";
    $results = array();
    $columns = mysql_query($sql)or die(mysql_error());
    while($row=mysql_fetch_array($columns,MYSQL_ASSOC)){
        $results[] = $row;
    }
    return $results;
}

/**
 * getSchema()
 * GET THE FULL SCHEMA MAP TABLES AND COLUMNS
 * OF A MYSQL DATABASE.  RETURN AS JSONP
 * @param mixed $dbname
 * @return void
 */
function getSchema($dbname){
    $schema=array();
    $sql = "SHOW TABLES IN $DBNAME";
    $results = array();
    $tables = mysql_query($sql)or die(mysql_error());
    while($row=mysql_fetch_array($tables,MYSQL_ASSOC)){
        $results[] = $row;
    }
    for($i=0;$i<count($results);$i++){
        foreach($results[$i] as $key => $value){
            $schema[$value] = _show_cols($value);
        }
    }
    echo $_GET['jsoncallback']."(".json_encode($schema).")";
}

Report this snippet Tweet

Comment:

You need to login to post a comment.

Posted By

Tagged

Versions (?)

Who likes this?

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

Related snippets

Choose a language for easy browsing: