Esta clase me sirvió para mis propósitos durante un tiempo que use sqlite, se puede cambiar facilmente para ser usada con mysql. Espero te sirva para darte una guía
Código PHP:
<?php
/********************* dbLink ******************/
/*
this class make all the querys to the database
*/
class dbLink {
public static $data_type = array();
public static $codes = array();
public $errors = array();
public $query_resource = false;
public static $linkId = false;
public $dbName = '/var/log/asterisk/cdr.db';
public $logFile = '/var/log/asterisk/econovoice/database.iss';
public function __construct() {
//sets the time header format, and other info stuff
$this->dateHeader = date('[Y - m - d / H : i : s]')."\n";
$this->document = $_SERVER['PHP_SELF'];
//set the error codes
$this->codes['errors'] = array();
$this->codes['errors'][0] = 'Sin errores.';
$this->codes['errors'][1] = 'No fué posible realizar la conexiónn a la base de datos. Revise su configuración.';
$this->codes['errors'][2] = 'No fué posible abrir el archivo "database.iss". Contacte a soporte técnico.';
$this->codes['errors'][3] = 'No fué posible escribir el archivo "database.iss". Contacte a soporte técnico.';
$this->codes['errors'][4] = 'No fué posible consultar la base de datos. Contacte a soporte técnico.';
//set database types regular expressions
$this->data_type['NUM'] = '[0-9]+|[0-9]\.[0-9]+';
$this->data_type['TXT'] = '.*';
$this->data_type['EMAIL'] = '^[0-9a-z\.\_\-]+@[0-9a-z\.\_\-]+(\.[a-z])*$';
$this->data_type['PHONE'] = '^[0-9\(\)\-]+$';
$this->data_type['DATE'] = '^[0-9]{4}[\-/][0-9]{2}[\-/][0-9]{2}$';
$this->data_type['PASS'] = '^.{6,15}$';
$this->data_type['ARRAY'] = '^.*$';
//open the database connection
$this->connect2DB();
return true;
}
//make the connection
public function connect2DB($db = "") {
$log = array();
$db = $db == "" ? $this->dbName : $db;
$link = file_exists($db) ? @sqlite_open($db) : false;
if ( $link ) {
$this->linkId = $link;
return true;
} else {
$this->errors[] = 1;
$log['error'] = $this->codes['errors'][1];
$this->writeLog($log);
return false;
}
}
//make a query
public function sqlQuery($sql) {
$sql = get_magic_quotes_gpc() === 1 ? $sql : addslashes($sql);
$rss = @sqlite_query($this->linkId,$sql);
if ( $rss !== false ) {
$this->query_resource = $rss;
return $rss;
} else {
$error_code = @sqlite_last_error($this->linkId);
$this->errors[] = 4;
$log['SQLQuery'] = $sql;
$log['SQLCode'] = $error_code;
$log['SQLError'] = @sqlite_error_string($error_code);
$this->writeLog($log);
return false;
}
}
//get the last query data
public function getRssData($rss = false) {
$rss = $rss !== false ? $rss : $this->query_resource;
if ( $rss === false ) {
return false;
} else {
$obj = @sqlite_fetch_object($rss);
return is_object($obj) ? $obj : false;
}
}
//get the rows of the last query
public function getRowsNumber($rss = false) {
$rss = $rss !== false ? $rss : $this->query_resource;
if ( $rss === false ) {
return false;
} else {
return sqlite_num_rows($rss);
}
}
//initialize the internal result pointer of the last result, or a specific one
public function rewindResults($rss = false) {
$rss = $rss !== false ? $rss : $this->query_resource;
if ( @sqlite_rewind($rss) ) {
return true;
} else {
return false;
}
}
//get the last id of an INSERT query
public function getLastId($rss = false) {
$rss = $rss !== false ? $rss : $this->query_resource;
if ( $rss === false ) {
return false;
} else {
$ret = sqlite_last_insert_rowid($rss);
return $ret;
}
}
//get the last id of a certain table
public function getLastIdFromTable($table,$field = 'id') {
$sql = "SELECT MAX(".$field.") AS lastone FROM ".$table."";
$rss = sqlite_query($this->linkId,$sql);
$obj = sqlite_fetch_object($rss);
return $obj->lastone;
}
//get a field value
public function getFieldValue($field, $table, $conditions = "") {
$where = $conditions != "" ? " WHERE ".$conditions : "";
$sql = "SELECT ".$field." AS returnValue FROM ".$table.$where;
$sql = get_magic_quotes_gpc() ? $sql : addslashes($sql);
$rss = @sqlite_query($this->linkId,$sql);
if ( $rss !== false ) {
$obj = @sqlite_fetch_object($rss);
$value = is_object($obj) ? $obj->returnValue : false;
return $value;
} else {
$error_code = @sqlite_last_error($this->linkId);
$this->errors[] = 4;
$log['SQLQuery'] = $sql;
$log['SQLCode'] = $error_code;
$log['SQLError'] = @sqlite_error_string($error_code);
$this->writeLog($log);
return false;
}
}
//writes a log if errors in the database querys
public function writeLog($logInfo = array()) {
$rs = @fopen($this->logFile,'a+');
if ( $rs !== false ) {
$log = $this->dateHeader;
$log .= "Documento: ".$this->document."\n";
$log .= "Error Codes: ".serialize($this->errors)."\n";
foreach ( $logInfo as $var => $value ) {
$log .= $var." = ".$value."\n";
}
$log .= "\n\n";
$writed = @fwrite($rs,$log);
if ( $writed !== false ) {
@fclose($rs);
return true;
} else {
$this->errors[] = 3;
return false;
}
} else {
$this->errors[] = 2;
return false;
}
}
//there are errors??
public function noSqlErrors() {
if ( empty($this->errors) ) {
return true;
} else {
return false;
}
}
//cut last chars of a string
public function stripLastChars($string, $offset = 2) {
$strlen = strlen($string);
$strcut = $strlen - $offset;
$substr = substr($string,0,$strcut);
return $substr;
}
//get the string errors
public function getLinkErrors() {
$error_string = "";
foreach ($this->errors as $ix => $code ) {
$error_string .= '<br /> :: '.$this->codes['errors'][$code].'<br />';
}
return $error_string;
}
//close the connection
public function __destruct() {
@sqlite_close($this->linkId);
return true;
}
}
?>