Skip to content

Instantly share code, notes, and snippets.

@junajan
Created December 16, 2019 17:30
Show Gist options
  • Save junajan/cccbe80fc3cde74ec883b56bb09b26f5 to your computer and use it in GitHub Desktop.
Save junajan/cccbe80fc3cde74ec883b56bb09b26f5 to your computer and use it in GitHub Desktop.
<?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