PHP Tutorial Introduction: three MySQL + SQLite + pdo php database classes are supported at the same time: // mysqlconnect $ db = newSQL (mysql: host = localhost; database = 21andy_blog;, 21andy.com _ user, 21andy.com _ passwo
PHP Tutorial Introduction: three MySQL + SQLite + pdo php database classes are supported at the same time: // mysql connect $ db = new SQL (mysql: host = localhost; database = 21andy_blog;, 21andy.com _ user, 21andy.com _ password); // PDO SQLite3 connect $ db = new SQL (pdo: database =/21andy.com/21andy.s
Three MySQL + SQLite + pdo php database classes are supported at the same time:
// Mysql connect
$ Db = new SQL ('MySQL: host = localhost; database = 21andy_blog; ', '21andy. com_user', '21andy. com_password ');
// PDO SQLite3 connect
$ Db = new SQL ('pdo: database =/21andy.com/21andy.sqlite3 ;');
// SQLite2 connect
$ Db = new SQL ('sqlite: database =/21andy.com/21andy.sqlite ;');
Sqldbs. class. php file
/*
SQL Buddy-Web based MySQL administration
Sqldbs. class. php
-SQL class
MIT license
*/
Class SQL {
Var $ adapter = "";
Var $ method = "";
Var $ version = "";
Var $ conn = "";
Var $ options = "";
Var $ errorMessage = "";
Var $ db = "";
Function SQL ($ connString, $ user = "", $ pass = ""){
List ($ this-> adapter, $ options) = explode (":", $ connString, 2 );
If ($ this-> adapter! = "Sqlite "){
$ This-> adapter = "mysql ";
}
$ OptionsList = explode (";", $ options );
Foreach ($ optionsList as $ option ){
List ($ a, $ B) = explode ("=", $ option );
$ Opt [$ a] = $ B;
}
$ This-> options = $ opt;
$ Database = (array_key_exists ("database", $ opt ))? $ Opt ['database']: "";
If ($ this-> adapter = "sqlite" & substr (sqlite_libversion (), 0, 1) = "3" & class_exists ("PDO ") & in_array ("sqlite", PDO: getAvailableDrivers ())){
$ This-> method = "pdo ";
Try
{
$ This-> conn = new PDO ("sqlite:". $ database, null, null, array (PDO: ATTR_PERSISTENT => true ));
}
Catch (PDOException $ error ){
$ This-> conn = false;
$ This-> errorMessage = $ error-> getMessage ();
}
} Else if ($ this-> adapter = "sqlite" & substr (sqlite_libversion (), 0, 1) = "2" & class_exists ("PDO ") & in_array ("sqlite2", PDO: getAvailableDrivers ())){
$ This-> method = "pdo ";
Try
{
$ This-> conn = new PDO ("sqlite2:". $ database, null, null, array (PDO: ATTR_PERSISTENT => true ));
}
Catch (PDOException $ error ){
$ This-> conn = false;
$ This-> errorMessage = $ error-> getMessage ();
}
} Else if ($ this-> adapter = "sqlite "){
$ This-> method = "sqlite ";
$ This-> conn = sqlite_open ($ database, 0666, $ sqliteError );
} Else {
$ This-> method = "mysql ";
$ Host = (array_key_exists ("host", $ opt ))? $ Opt ['host']: "";
$ This-> conn = @ mysql_connect ($ host, $ user, $ pass );
}
If ($ this-> conn & $ this-> method = "pdo "){
$ This-> conn-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_SILENT );
}
If ($ this-> conn & $ this-> adapter = "mysql "){
$ This-> query ("set names 'utf8 '");
}
If ($ this-> conn & $ database ){
$ This-> db = $ database;
}
}
Function isConnected (){
Return ($ this-> conn! = False );
}
Function close (){
Return $ this-> disconnect ();
}
Function disconnect (){
If ($ this-> conn ){
If ($ this-> method = "pdo "){
$ This-> conn = null;
} Else if ($ this-> method = "mysql "){
Mysql_close ($ this-> conn );
$ This-> conn = null;
} Else if ($ this-> method = "sqlite "){
Sqlite_close ($ this-> conn );
$ This-> conn = null;
}
}
}
Function getAdapter (){
Return $ this-> adapter;
}
Function getMethod (){
Return $ this-> method;
}
Function getOptionValue ($ optKey ){
If (array_key_exists ($ optKey, $ this-> options )){
Return $ this-> options [$ optKey];
} Else {
Return false;
}
}
Function selectDB ($ db ){
If ($ this-> conn ){
If ($ this-> method = "mysql "){
$ This-> db = $ db;
Return (mysql_select_db ($ db ));
} Else {
Return true;
}
} Else {
Return false;
}
}
Function query ($ queryText ){
If ($ this-> conn ){
If ($ this-> method = "pdo "){
$ QueryResult = $ this-> conn-> prepare ($ queryText );
If ($ queryResult)
$ QueryResult-> execute ();
If (! $ QueryResult ){
$ ErrorInfo = $ this-> conn-> errorInfo ();
$ This-> errorMessage = $ errorInfo [2];
}
Return $ queryResult;
} Else if ($ this-> method = "mysql "){
$ QueryResult = @ mysql_query ($ queryText, $ this-> conn );
If (! $ QueryResult ){
$ This-> errorMessage = mysql_error ();
}
Return $ queryResult;
} Else if ($ this-> method = "sqlite "){
$ QueryResult = sqlite_query ($ this-> conn, $ queryText );
If (! $ QueryResult ){
$ This-> errorMessage = sqlite_error_string (sqlite_last_error ($ this-> conn ));
}
Return $ queryResult;
}
} Else {
Return false;
}
}
// Be careful using this function-when used with pdo, the pointer is moved
// To the end of the result set and the query needs to be rerun. Unless you
// Actually need a count of the rows, use the isResultSet () function instead
Function rowCount ($ resultSet ){
If (! $ ResultSet)
Return false;
If ($ this-> conn ){
If ($ this-> method = "pdo "){
Return count ($ resultSet-> fetchAll ());
} Else if ($ this-> method = "mysql "){
Return @ mysql_num_rows ($ resultSet );
} Else if ($ this-> method = "sqlite "){
Return @ sqlite_num_rows ($ resultSet );
}
}
}
Function num_rows ($ res ){
Return $ this-> rowCount ($ res );
}
Function isResultSet ($ resultSet ){
If ($ this-> conn ){
If ($ this-> method = "pdo "){
Return ($ resultSet = true );
} Else {
Return ($ this-> rowCount ($ resultSet)> 0 );
}
}
}
Function fetch ($ res ){
Return $ this-> fetchAssoc ($ res );
}
Function fetchArray ($ resultSet ){
If (! $ ResultSet)
Return false;
If ($ this-> conn ){
If ($ this-> method = "pdo "){
Return $ resultSet-> fetch (PDO: FETCH_NUM );
} Else if ($ this-> method = "mysql "){
Return mysql_fetch_row ($ resultSet );
} Else if ($ this-> method = "sqlite "){
Return sqlite_fetch_array ($ resultSet, SQLITE_NUM );
}
}
}
Function fetchAssoc ($ resultSet ){
If (! $ ResultSet)
Return false;
If ($ this-> conn ){
If ($ this-> method = "pdo "){
Return $ resultSet-> fetch (PDO: FETCH_ASSOC );
} Else if ($ this-> method = "mysql "){
Return mysql_fetch_assoc ($ resultSet );
} Else if ($ this-> method = "sqlite "){
Return sqlite_fetch_array ($ resultSet, SQLITE_ASSOC );
}
}
}
Function affectedRows ($ resultSet ){
If (! $ ResultSet)
Return false;
If ($ this-> conn ){
If ($ this-> method = "pdo "){
Return $ resultSet-> rowCount ();
} Else if ($ this-> method = "mysql "){
Return @ mysql_affected_rows ($ resultSet );
} Else if ($ this-> method = "sqlite "){
Return sqlite_changes ($ resultSet );
}
}
}
Function result ($ resultSet, $ targetRow, $ targetColumn = ""){
If (! $ ResultSet)
Return false;
If ($ this-> conn ){
If ($ this-> method = "pdo "){
If ($ targetColumn ){
$ ResultRow = $ resultSet-> fetch (PDO: FETCH_ASSOC, PDO: FETCH_ORI_ABS, $ targetRow );
Return $ resultRow [$ targetColumn];
} Else {
$ ResultRow = $ resultSet-> fetch (PDO: FETCH_NUM, PDO: FETCH_ORI_ABS, $ targetRow );
Return $ resultRow [0];
}
} Else if ($ this-> method = "mysql "){
Return mysql_result ($ resultSet, $ targetRow, $ targetColumn );
} Else if ($ this-> method = "sqlite "){
Return sqlite_column ($ resultSet, $ targetColumn );
}
}
}
Function listDatabases (){
If ($ this-> conn ){
If ($ this-> adapter = "mysql "){
Return $ this-> query ("show databases ");
} Else if ($ this-> adapter = "sqlite "){
Return $ this-> db;
}
}
}
Function listTables (){
If ($ this-> conn ){
If ($ this-> adapter = "mysql "){
Return $ this-> query ("show tables ");
} Else if ($ this-> adapter = "sqlite "){
Return $ this-> query ("SELECT name FROM sqlite_master WHERE type = 'table' order by name ");
}
}
}
Function hasCharsetSupport ()
{
If ($ this-> conn ){
If ($ this-> adapter = "mysql" & version_compare ($ this-> getVersion (), "4.1", "> ")){
Return true;
} Else {
Return false;
}
}
}
Function listCharset (){
If ($ this-> conn ){
If ($ this-> adapter = "mysql "){
Return $ this-> query ("show character set ");
} Else if ($ this-> adapter = "sqlite "){
Return "";
}
}
}
Function listCollation (){
If ($ this-> conn ){
If ($ this-> adapter = "mysql "){
Return $ this-> query ("show collation ");
} Else if ($ this-> adapter = "sqlite "){
Return "";
}
}
}
Function insertId (){
If ($ this-> conn ){
If ($ this-> method = "pdo "){
Return $ this-> conn-> lastInsertId ();
} Else if ($ this-> method = "mysql "){
Return @ mysql_insert_id ($ this-> conn );
} Else if ($ this-> method = "sqlite "){
Return sqlite_last_insert_rowid ($ this-conn );
}
}
}
Function escapeString ($ toEscape ){
If ($ this-> conn ){
If ($ this-> method = "pdo "){
$ ToEscape = $ this-> conn-> quote ($ toEscape );
$ ToEscape = substr ($ toEscape, 1,-1 );
Return $ toEscape;
} Else if ($ this-> adapter = "mysql "){
Return mysql_real_escape_string ($ toEscape );
} Else if ($ this-> adapter = "sqlite "){
Return sqlite_escape_string ($ toEscape );
}
}
}
Function getVersion (){
If ($ this-> conn ){
// Cache
If ($ this-> version ){
Return $ this-> version;
}
If ($ this-> adapter = "mysql "){
$ VerSql = mysql_get_server_info ();
$ Version = explode ("-", $ verSql );
$ This-> version = $ version [0];
Return $ this-> version;
} Else if ($ this-> adapter = "sqlite "){
$ This-> version = sqlite_libversion ();
Return $ this-> version;
}
}
}
// Returns the number of rows in a table
Function tableRowCount ($ table ){
If ($ this-> conn ){
If ($ this-> adapter = "mysql "){
$ CountSql = $ this-> query ("select count (*) AS 'rowcount' FROM '". $ table ."'");
$ Count = (int) ($ this-> result ($ countSql, 0, "RowCount "));
Return $ count;
} Else if ($ this-> adapter = "sqlite "){
$ CountSql = $ this-> query ("select count (*) AS 'rowcount' FROM '". $ table ."'");
$ Count = (int) ($ this-> result ($ countSql, 0, "RowCount "));
Return $ count;
}
}
}
// Gets column info for a table
Function describeTable ($ table ){
If ($ this-> conn ){
If ($ this-> adapter = "mysql "){
Return $ this-> query ("DESCRIBE '". $ table ."'");
} Else if ($ this-> adapter = "sqlite "){
$ ColumnSql = $ this-> query ("SELECT SQL FROM sqlite_master where tbl_name = '". $ table ."'");
$ ColumnInfo = $ this-> result ($ columnSql, 0, "SQL ");
$ ColumnStart = strpos ($ columnInfo ,'(');
$ Columns = substr ($ columnInfo, $ columnStart + 1,-1 );
$ Columns = split (', [^ 0-9]', $ columns );
$ ColumnList = array ();
Foreach ($ columns as $ column ){
$ Column = trim ($ column );
$ ColumnSplit = explode ("", $ column, 2 );
$ ColumnName = $ columnSplit [0];
$ ColumnType = (sizeof ($ columnSplit)> 1 )? $ ColumnSplit [1]: "";
$ ColumnList [] = array ($ columnName, $ columnType );
}
Return $ columnList;
}
}
}
/*
Return names, row counts etc for every database, table and view in a JSON string
*/
Function getMetadata (){
$ Output = '';
If ($ this-> conn ){
If ($ this-> adapter = "mysql" & version_compare ($ this-> getVersion (), "5.0.0", "> = ")){
$ This-> selectDB ("information_schema ");
$ SchemaSql = $ this-> query ("SELECT 'schema _ name' FROM 'schema' order by 'schema _ name '");
If ($ this-> rowCount ($ schemaSql )){
While ($ schema = $ this-> fetchAssoc ($ schemaSql )){
$ Output. = '{"name": "'. $ schema ['schema _ name']. '"';
// Other interesting columns: TABLE_TYPE, ENGINE, TABLE_COLUMN and more
$ TableSql = $ this-> query ("SELECT 'Table _ name', 'Table _ ROWS 'FROM 'tables' WHERE 'Table _ scheme' = '". $ schema ['schema _ name']. "'Order BY 'Table _ name '");
If ($ this-> rowCount ($ tableSql )){
$ Output. = ', "items ":[';
While ($ table = $ this-> fetchAssoc ($ tableSql )){
If ($ schema ['schema _ name'] = "information_schema "){
$ CountSql = $ this-> query ("select count (*) AS 'rowcount' FROM '". $ table ['Table _ name']. "'");
$ RowCount = (int) ($ this-> result ($ countSql, 0, "RowCount "));
} Else {
$ RowCount = (int) ($ table ['Table _ ROWS ']);
}
$ Output. = '{"name": "'. $ table ['Table _ name']. '", "rowcount":'. $ rowCount .'},';
}
If (substr ($ output,-1) = ",")
$ Output = substr ($ output, 0,-1 );
$ Output. = ']';
}
$ Output. = '},';
}
$ Output = substr ($ output, 0,-1 );
}
} Else if ($ this-> adapter = "mysql "){
$ SchemaSql = $ this-> listDatabases ();
If ($ this-> rowCount ($ schemaSql )){
While ($ schema = $ this-> fetchArray ($ schemaSql )){
$ Output. = '{"name": "'. $ schema [0]. '"';
$ This-> selectDB ($ schema [0]);
$ TableSql = $ this-> listTables ();
If ($ this-> rowCount ($ tableSql )){
$ Output. = ', "items ":[';
While ($ table = $ this-> fetchArray ($ tableSql )){
$ CountSql = $ this-> query ("select count (*) AS 'rowcount' FROM '". $ table [0]. "'");
$ RowCount = (int) ($ this-> result ($ countSql, 0, "RowCount "));
$ Output. = '{"name": "'. $ table [0]. '", "rowcount":'. $ rowCount .'},';
}
If (substr ($ output,-1) = ",")
$ Output = substr ($ output, 0,-1 );
$ Output. = ']';
}
$ Output. = '},';
}
$ Output = substr ($ output, 0,-1 );
}
} Else if ($ this-> adapter = "sqlite "){
$ Output. = '{"name": "'. $ this-> db .'"';
$ TableSql = $ this-> listTables ();
If ($ tableSql ){
$ Output. = ', "items ":[';
While ($ tableRow = $ this-> fetchArray ($ tableSql )){
$ CountSql = $ this-> query ("select count (*) AS 'rowcount' FROM '". $ tableRow [0]. "'");
$ RowCount = (int) ($ this-> result ($ countSql, 0, "RowCount "));
$ Output. = '{"name": "'. $ tableRow [0]. '", "rowcount":'. $ rowCount .'},';
}
If (substr ($ output,-1) = ",")
$ Output = substr ($ output, 0,-1 );
$ Output. = ']';
}
$ Output. = '}';
}
}
Return $ output;
}
Function error (){
Return $ this-> errorMessage;
}
}