<?php | |
/* | |
# | |
# Trida zajistujici praci s databazi. | |
# Pokud se planuje pouziti jine databaze, pak je nutne vytvorit kopii teto tridy s prikazi pro zvolenou | |
# databazi, ulozit ji pod nazvem_databaze.class.php | |
# | |
*/ | |
class MySQL { | |
private $nastaveni = array(); | |
private $handler = false; | |
private $loguj = false; | |
private $mysqlshow = false; | |
private $mysqlshowGet = true; | |
private $pdo = NULL; | |
private $lastExec = NULL; | |
private $prefix = ""; | |
private $lastResult = NULL; | |
public $show_errors = false; | |
public $show_sql = false; | |
private $organizeByColumn = false; | |
private $organizeOnlyColumn = false; | |
private $profilTable = array (); | |
const DO_PROFILING_DEATH = false; | |
const PROFILING_FILE = "mysql_profiling.txt"; | |
public $REDIS = false; | |
# pripojeni k databazi | |
function __construct ( $nastaveni = NULL, $loguj = false, $REDIS = false ){ | |
if ( $REDIS ) | |
$this -> REDIS = $REDIS; | |
if ( isset ( $_GET["mysqlshow"] ) and $this -> mysqlshowGet ) | |
$this -> mysqlshow = true; | |
$this -> nastaveni = $nastaveni; | |
$this -> loguj = $loguj; | |
if ( $nastaveni === NULL ) { | |
echo "Nebylo nastaveno pripojeni k databazi."; | |
exit; | |
} | |
if ( defined ( "DB_SHOW_ERRORS") and DB_SHOW_ERRORS ) | |
$this -> show_errors = true; | |
try { | |
$this -> pdo = new PDO( "mysql:host=". $nastaveni["SQL_HOST"].";dbname=".$nastaveni["SQL_DBNAME"], $nastaveni["SQL_USERNAME"], $nastaveni["SQL_PASSWORD"] ); | |
unset ( $this -> nastaveni["SQL_PASSWORD"] ); | |
} catch ( PDOException $e ) { | |
die ( "Cant connect to database." ); | |
} | |
$error = $this -> pdo -> errorInfo(); | |
if( $error[0] != "") { | |
print "<p>DATABASE CONNECTION ERROR:</p>"; | |
die ( print_r($error, true ) ); | |
} | |
$this -> sql ( "SET character_set_client=" . (( isset ( $nastaveni["character_set_client"] ) ) ? $nastaveni["character_set_client"] : "'utf8'" )); | |
$this -> sql ( "SET character_set_connection=" . ( ( isset ( $nastaveni["character_set_connection"] ) ) ? $nastaveni["character_set_connection"] : "'utf8'" ) ); | |
$this -> sql ( "SET character_set_results=" . ( ( isset ( $nastaveni["character_set_results"] ) ) ? $nastaveni["character_set_results"] : "'utf8'" ) ); | |
if ( isset ( $_GET["mysqlshow"] ) and isset ( $nastaveni["mysqlshow"] ) and $nastaveni["mysqlshow"] === true ) $this -> mysqlshow = true; | |
} | |
function getError () { | |
return $this -> pdo -> errorInfo(); | |
} | |
function processParams ( $params ) { | |
$newParam = array (); | |
foreach ( $params as $var => $val ) { | |
if ( ! is_int ( $var ) ) | |
$newParam[":".$var] = $val; | |
else | |
$newParam[$var] = $val; | |
} | |
return $newParam; | |
} | |
function setFetchByColumn ( $cn ) { | |
$this -> organizeByColumn = $cn; | |
} | |
function setFetchOnlyColumn ( $cn ) { | |
$this -> organizeOnlyColumn = $cn; | |
} | |
function fetchByColumn ( $data, $cn ) { | |
$newData = array (); | |
foreach ( $data as $var => $val ) { | |
if ( ! isset ( $val[$cn] ) ) { | |
echo "Column '".$cn."' not find.<br />"; | |
return false; | |
} | |
if ( $this -> organizeOnlyColumn ) { | |
if ( ! isset ( $val[ $this -> organizeOnlyColumn] ) ) | |
return false; | |
$newData[ $val[ $cn ] ] = $val[ $this -> organizeOnlyColumn]; | |
} else | |
$newData[ $val[ $cn ] ] = $val; | |
} | |
$this -> organizeByColumn = false; | |
return $newData; | |
} | |
function execPDO ( $SQL, $params ) { | |
$fetched = false; | |
if ( self::DO_PROFILING_DEATH ) { | |
if ( ! isset ( $this ->profilTable[$SQL] )) | |
$this ->profilTable[$SQL] = 0; | |
$this ->profilTable[$SQL]++; | |
} | |
try { | |
if ( $this -> REDIS ) { | |
$c = $this -> REDIS -> dbGetCache ( $SQL, $params ); | |
if ( $c ) | |
return $c; | |
} | |
$ps = $this -> pdo-> prepare( $SQL); | |
$params = $this -> processParams ( $params ); | |
if ( $this -> mysqlshow or $this -> show_sql) | |
echo $SQL." | Params: " . print_r ( $params, true )."<br />\n"; | |
if ( $ps ) { | |
$this -> lastExec = $ps -> execute( $params ); | |
} | |
if ( $ps ->errorCode() != '0000' and $this -> show_errors ) | |
{ | |
echo "PDOStatement::errorCode(): "; | |
print_r( $ps ->errorInfo() ); | |
$backtrace = debug_backtrace(); | |
if ( isset ( $backtrace[1] ) ){ | |
$c = $backtrace[1]; | |
echo "FILE: ".$c["file"]." | Line: ".$c["line"] ." | Function: " .$c["function"]."<br />\n"; | |
} | |
return false; | |
} | |
$ps -> setFetchMode(PDO::FETCH_ASSOC ); | |
$this -> lastResult = $ps; | |
$fetched = $ps ->fetchAll(); | |
if ( $this -> organizeByColumn != false ) | |
$fetched = $this -> fetchByColumn ( $fetched, $this -> organizeByColumn ); | |
} catch( PDOException $e) { | |
echo $e->getMessage(); | |
return false; | |
} | |
if ( $this -> REDIS ) | |
$this -> REDIS -> dbSaveCache ( $SQL, $params, $fetched ); | |
return $fetched; | |
} | |
# vnitrni funkce databazove tridy | |
function proved_sql ( $sql, $args ) { | |
$args = $this -> getArguments ( $args ); | |
$vysledek = $this -> execPDO ( $sql, $args ); | |
return $vysledek; | |
} | |
function sql ( $sql, $args = null ) { | |
return $this -> proved_sql ( $sql, $args ); | |
} | |
function processData ( $vysledek ) { | |
$i = 0; | |
while ( $udaje[$i++] = mysql_fetch_assoc($vysledek) ); | |
if ( $i == 1) return false; | |
/* | |
# zmena - 21.8.2010 | |
# Smazani posledniho (vzdy) prazdneho prvku pole. | |
*/ | |
unset ( $udaje[$i-1] ); | |
return $udaje; | |
} | |
function writeToLog ( $line) { | |
return false; | |
if ( ! $fh = fopen( DIR."logs/mysql.log", 'a+') ) | |
return false; | |
$line = date ( "d.m.Y h.i.s" )."|".$line; | |
if ( isset ( $_SERVER["REMOTE_ADDR"] ) ) | |
$line = $_SERVER["REMOTE_ADDR"]."|".$line; | |
fwrite( $fh, $line); | |
fclose( $fh ); | |
return true; | |
} | |
# insert do databaze | |
function insert ($tabulka, $hodnoty ) { | |
$params = array (); | |
$paramsIndex = 0; | |
$str1 = $str2 = ""; | |
foreach ( $hodnoty as $var => $val ) { | |
if ( $var[0] == "." ) { | |
$var = ltrim ( $var, "." ); | |
$str1 .= "`{$var}`, "; | |
$str2 .= "{$val} , "; | |
} else { | |
$str1 .= "`{$var}`, "; | |
$str2 .= "? , "; | |
$params[ $paramsIndex++ ] = $val; | |
} | |
} | |
$str1 = trim ( $str1, ", " ); | |
$str2 = trim ( $str2, ", " ); | |
$sql = "INSERT INTO {$tabulka} ( "; | |
$sql .= $str1; | |
$sql .= ") VALUES ( "; | |
$sql .= $str2; | |
$sql .= " );"; | |
$this -> execPDO ( $sql, $params ); | |
return $this -> lastExec; | |
} | |
function comma ( $str ) { | |
return "'".$str."'"; | |
} | |
function commaAll ( $arr ) { | |
foreach ( $arr as $var => $val ) | |
$arr[$var] = $this -> comma ( $val ); | |
return $arr; | |
} | |
# update radku v databazi | |
function update ($tabulka, array $hodnoty, $podminky, $args = array(), $limit = false ) { | |
$str1 = $str2 = ""; | |
$params = array (); | |
$paramsIndex = 0; | |
$args = $this -> getArguments ( $args ); | |
foreach ( $hodnoty as $var => $val ) { | |
if ( $var[0] == "." ) { | |
$var = ltrim ( $var, "." ); | |
$str1 .= "`{$var}` = {$val} , "; | |
} else { | |
$str1 .= "`{$var}` = ? , "; | |
$params[ $paramsIndex++ ] = $val; | |
} | |
} | |
foreach ( $args as $var => $val ) { | |
if ( is_string ( $var ) ) | |
$params[ $var ] = $val; | |
else | |
$params[ $paramsIndex++ ] = $val; | |
} | |
$str1 = trim ( $str1, ", " ); | |
$sql = "UPDATE {$tabulka} SET "; | |
$sql .= $str1; | |
$sql .= " WHERE "; | |
$sql .= $podminky ; | |
if ( isset ( $limit ) and is_int ( $limit ) ) | |
$sql .= " LIMIT ".$limit; | |
$sql .= ";"; | |
$this -> execPDO ( $sql, $params ); | |
return $this -> lastExec; | |
} | |
function getRow () { | |
if ( ! $this ->lastResult ) | |
return false; | |
$data = $this ->lastResult -> fetchAll(); | |
return $data[0]; | |
} | |
function getRows () { | |
if ( ! $this ->lastResult ) | |
return false; | |
$data = $this ->lastResult -> fetchAll(); | |
return $data; | |
} | |
# podle, smer | |
function getDirection ( $podle = "", $smer = "" ) { | |
$razeni = ""; | |
if( $podle != "" ) { | |
$razeni = "ORDER BY $podle"; | |
if ( $smer != "" ) | |
$razeni .= " ".$smer; | |
} | |
return $razeni; | |
} | |
function getArguments ( $args = array () ) { | |
$newArgs = array (); | |
if ( $args === NULL ) | |
$args = array (); | |
if ( ! is_array ( $args ) ) | |
$newArgs[0] = $args; | |
else | |
$newArgs = $args; | |
return $newArgs; | |
} | |
# vypisovaci funkce: | |
function get ( $co, $kde, $podminka = "1=1", $args = array () , $podle = "", $smer = "", $limit = 1 ) { | |
$args = $this -> getArguments ( $args ); | |
$razeni = $this -> getDirection ( $podle, $smer ); | |
$sql = "SELECT $co FROM {$kde} WHERE $podminka ".$razeni." LIMIT {$limit};"; | |
$vysledek = $this -> execPDO ( $sql, $args ); | |
if ( ! $vysledek ) | |
return false; | |
if ( $limit == 1 ) | |
return $vysledek[0]; | |
return $vysledek; | |
} | |
function getItem ( $co, $kde, $podminka, $args = array () , $podle = "", $smer = "", $limit = 1 ) { | |
return $this -> get ( $co, $kde, $podminka, $args , $podle, $smer, $limit ); | |
} | |
function getValue ( $co, $kde, $podminka, $args = array () , $podle = "", $smer = "", $limit = 1 ) { | |
$c = $this -> get ( $co, $kde, $podminka, $args , $podle, $smer, $limit ); | |
if ( isset ( $c[$co] ) ) | |
return $c[$co]; | |
return $c; | |
} | |
function getData ($co, $kde, $podminka = "1=1", $args = array (), $podle = "", $smer = "", $limit = "" ){ | |
if ( $limit != "" ) $limit = " LIMIT ".$limit; | |
$args = $this -> getArguments ( $args ); | |
$razeni = $this -> getDirection ( $podle, $smer ); | |
$sql = "SELECT $co FROM {$kde} WHERE $podminka ".$razeni.$limit.";"; | |
$vysledek = $this -> execPDO ( $sql, $args ); | |
return $vysledek; | |
} | |
function getDataBySet ($co, $kde, $podminka, $set, $podle = "", $smer = "", $limit = "" ){ | |
if ( $limit != "" ) $limit = " LIMIT ".$limit; | |
$razeni = $this -> getDirection ( $podle, $smer ); | |
foreach ( $set as $var => $val ) { | |
$c = implode ( ", ", $val ); | |
$podminka .= " ".$var." IN( {$c} ) "; | |
} | |
$sql = "SELECT $co FROM {$kde} WHERE $podminka ".$razeni.$limit.";"; | |
$vysledek = $this -> execPDO ( $sql, $args ); | |
return $vysledek; | |
} | |
function getDataUnion ( $co, $co2, $kde, $kde2, $podminka, $podminka2, $podle_ceho = "ID",$podle_ceho2 = "ID", $smerem = "ASC",$smerem2 = "ASC", $limit = "",$limit2 = "" ){ | |
if ( $limit != "" ) $limit = " LIMIT ".$limit; | |
if ( $limit2 != "" ) $limit2 = " LIMIT ".$limit2; | |
echo "DB function ". __FUNCTION__ ." NOT IMPLEMENTED\n<br />"; | |
$sql = "SELECT $co FROM {$kde} WHERE $podminka UNION ALL SELECT $co2 FROM {$kde2} WHERE $podminka2 ORDER BY $podle_ceho $smerem".$limit.";"; | |
$vysledek = $this -> proved_sql ( $sql ); | |
return $this -> processData ( $vysledek );; | |
} | |
function getAll ($co, $kde, $podminka, $args = array () ){ | |
$args = $this -> getArguments ( $args ); | |
$sql = "SELECT $co FROM {$kde} WHERE $podminka;"; | |
$vysledek = $this -> execPDO ( $sql, $args ); | |
return $vysledek; | |
} | |
function getRangeData ($co, $kde, $podminka, $args = array (), $podle = "", $smer = "", $od = 0, $kolik = 100 ){ | |
$args = $this -> getArguments ( $args ); | |
$razeni = $this -> getDirection ( $podle, $smer ); | |
$sql = "SELECT $co FROM {$kde} WHERE {$podminka} {$razeni} LIMIT $od, $kolik;"; | |
$vysledek = $this -> execPDO ( $sql, $args ); | |
return $vysledek; | |
} | |
function getRandData ($co, $kde, $podminka, $smer, $pocet ) { | |
//~ "SELECT TOP 5 * FROM e_anglictina WHERE Typ='0' ORDER BY RAND()" | |
echo "DB function ". __FUNCTION__ ." NOT IMPLEMENTED\n<br />"; | |
$sql = "SELECT $co FROM {$kde} WHERE $podminka ORDER BY RAND() $smer limit $pocet;"; | |
$vysledek = $this -> proved_sql ( $sql ); | |
$i = 0; | |
while ($udaje[$i++] = mysql_fetch_assoc($vysledek) ); | |
unset ( $udaje[$i-1] ); | |
return $udaje; | |
} | |
# pocetni skripty | |
function getCount ( $co, $kde, $podminka, $args = array () ) { | |
$args = $this -> getArguments ( $args ); | |
$sql = "SELECT COUNT($co) AS pocet FROM {$kde} WHERE $podminka"; | |
if ( $vysledek = $this -> execPDO ( $sql, $args ) ); | |
return $vysledek[0]["pocet"]; | |
return 0; | |
} | |
function getCountID ( $kde, $podminka, $args = array () ) { | |
return $this -> getCount ( "ID", $kde, $podminka, $args ); | |
} | |
function getIndexed ( $index, $col, $kde, $podminka = "1=1", $args = array (), $podle = "", $smer = "", $limit = "" ){ | |
if ( $limit != "" ) $limit = " LIMIT ".$limit; | |
$args = $this -> getArguments ( $args ); | |
$razeni = $this -> getDirection ( $podle, $smer ); | |
$sql = "SELECT {$col}, {$index} FROM {$kde} WHERE $podminka ".$razeni.$limit.";"; | |
$vysledek = $this -> execPDO ( $sql, $args ); | |
$out = array (); | |
if ( $vysledek ) foreach ( $vysledek as $var => $val ) | |
$out[$val[$index]] = $val[$col]; | |
return $out; | |
} | |
# overovaci skripty | |
function exist ( $kde, $podminka, $args = array (), $co = "ID" ) { | |
$args = $this -> getArguments ( $args ); | |
$sql = "SELECT {$co} FROM `{$kde}` WHERE $podminka LIMIT 1;"; | |
if ( $vysledek = $this -> execPDO ( $sql, $args ) ); | |
return true; | |
return false; | |
} | |
function vratID () { | |
return $this -> getLastID (); | |
} | |
function getLastID () { | |
return $this -> pdo -> lastInsertID(); | |
} | |
function lastInsertedID () { | |
return $this -> getLastID(); | |
} | |
function lastInsertId () { | |
return $this -> getLastID(); | |
} | |
function lastID () { | |
return $this -> getLastID(); | |
} | |
function insertedID () { | |
return $this -> getLastID(); | |
} | |
function getTableLastVal ( $table, $collumn = "ID" ) { | |
if ( $c = $this -> get ( $collumn, $table, "1=1", $collumn, "DESC" ) ) | |
return $c[ $collumn ]; | |
return 0; | |
} | |
function getTableFirstVal ( $table, $collumn = "ID" ) { | |
if ( $c = $this -> get ( $collumn, $table, "1=1", $collumn, "ASC" ) ) | |
return $c[ $collumn ]; | |
return 0; | |
} | |
function getTableLastID ( $table ) { | |
$this -> getTableLastVal ( $table, "ID" ); | |
return 0; | |
} | |
function getTableFirstID ( $table ) { | |
$this -> getTableFirstVal ( $table, "ID" ); | |
return 0; | |
} | |
# mazani z databaze | |
function deleteID ( $kde, $id, $limit = NULL ) { | |
return $this -> deleter ( $kde, "ID=?", $id, $limit ); | |
} | |
# smazani v databazi | |
function delete ( $tabulka, $podminka, $args = array (), $limit = NULL) { | |
if ( $limit != NULL ) | |
$limit = "LIMIT ".$limit; | |
$args = $this -> getArguments ( $args ); | |
$sql = "DELETE FROM {$tabulka} WHERE $podminka $limit;"; | |
$this -> execPDO ( $sql, $args ); | |
return $this -> lastExec; | |
} | |
# alias k delete | |
function deleter ($tabulka, $podminka, $args = array (), $limit = NULL) { | |
return $this -> delete ( $tabulka, $podminka, $args, $limit); | |
} | |
# destructor | |
function __destruct ( ) { | |
// destroy PDO object => close DB connection | |
$this -> pdo = null; | |
if ( self::DO_PROFILING_DEATH ) { | |
if ( !file_exists( DIR.self::PROFILING_FILE )) | |
$profilingFileContent = ""; | |
else | |
$profilingFileContent = file_get_contents( DIR.self::PROFILING_FILE ); | |
$profilingFileContent = explode ( "\n", $profilingFileContent ); | |
$profiling = array (); | |
foreach ( $profilingFileContent as $var => $val ) { | |
$c = explode ( "~", $val ); | |
if ( isset ( $c[1] )) | |
$profiling[$c[1]]=$c[0]; | |
} | |
foreach ( $this -> profilTable as $p => $c ) { | |
if ( isset ( $profiling[$p])) | |
$profiling[$p] += $c; | |
else | |
$profiling[$p] = $c; | |
} | |
$out = array (); | |
foreach ( $profiling as $v => $c ) | |
$out[] = $c."~".$v; | |
file_put_contents( DIR.self::PROFILING_FILE, implode( "\n", $out) ); | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment