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

Source: Internet
Author: User
Tags foreach explode getmessage connect mysql mysql connect php file sqlite

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;

}

}



Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.