Hi Everyone, I have posted a PHP script that will return Exhbit formatted JSON from a MySQL database, I figure that some people will find it useful!
See: http://www.jonbogacki.com/2011/05/how-to-use-exhibit-with-a-mysql-database/ <?php /* * Script: Exhibit JSON server-side script for PHP and MySQL */ /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * Easy set variables */ /* Database connection information */ $gaSql['user'] = "YourUsername"; $gaSql['password'] = "YourPassword"; $gaSql['db'] = "YourDatabase"; $gaSql['server'] = "YourSQLServer"; /* DB table to use */ $sTable = "YourDataTable"; /* Indexed column (Primary Key column or similar) */ $sIndexColumn = "YourIDColumn"; /* Array of database columns which should be read and sent back to Exhibit in JSON form. */ $aColumns = array( 'Column1_label', 'Column2', 'Column3', 'Column4'); /* Exhibit requires that each item have a "label" and/or an "id" field. * Set the below variables to identify which columns are your "label" and/or "id" fields * (or leave them blank if your columns are named correctly) */ $item_id = ''; $item_label = 'Column1_label'; /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * No need it edit below this line [image: :D] */ /* * MySQL connection */ $gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) or die( 'Could not open connection to server' ); mysql_select_db( $gaSql['db'], $gaSql['link'] ) or die( 'Could not select database '. $gaSql['db'] ); /* * SQL queries * Get data to display */ $sQuery = " SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))." FROM $sTable "; $rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error()); /* Data set length after filtering */ $sQuery = " SELECT FOUND_ROWS() "; $rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error()); $aResultFilterTotal = mysql_fetch_array($rResultFilterTotal); $iFilteredTotal = $aResultFilterTotal[0]; /* Total data set length */ $sQuery = " SELECT COUNT(".$sIndexColumn.") FROM $sTable "; /* * Output */ $output = array( "items" => array() ); while ( $aRow = mysql_fetch_array( $rResult ) ) { $row = array(); for ( $i=0 ; $i<count($aColumns) ; $i++ ) { if ( $aColumns[$i] == $item_label ) { $row["label"] = $aRow[ $aColumns[$i] ]; } else if ( $aColumns[$i] == $item_id ) { $row["id"] = $aRow[ $aColumns[$i] ]; } else { $row[$aColumns[$i]] = $aRow[ $aColumns[$i] ]; } } $output['items'][] = $row; } echo json_encode( $output ); ?> Eagerly awaiting the first version of Exhibit 3 to play with - keep up the good work MIT! Jon -- You received this message because you are subscribed to the Google Groups "SIMILE Widgets" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/simile-widgets?hl=en.
