Supports three mysql+sqlite+pdo PHP database classes at the same time

PHP Learning Tutorial Article Introduction: also supports three mysql+sqlite+pdo PHP database class use method://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

also supports three mysql+sqlite+pdo PHP database class usage methods:

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 is rerun. Unless

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 '". $table. "`");

$count = (int) ($this->result ($countSql, 0, "rowcount"));

return $count;

else if ($this->adapter = = "SQLite") {

$COUNTSQL = $this->query ("Select COUNT (*) as ' rowcount '". $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-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 ' schemata ' 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 many

$TABLESQL = $this->query ("SELECT ' table_name ', ' table_rows ' from ' TABLES ' WHERE ' table_schema ' = '". $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 '". $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 '". $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 '". $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;

}

}