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