<?php
/**
* Database: MySQL connection
*
*/
class Database{
protected $database;
protected $user;
protected $password;
protected $host = "localhost";
protected $server = "localhost";
protected $connection;
protected $connected;
/**
* @param string $database
* @param string $user
* @param string $password
* @param string $host
* @param string $server
*/
public function __construct($database, $user, $password, $host, $server){
$this->database = !empty($database) ? $database : DATABASE;
$this->user = !empty($user) ? $user : DBUSER;
$this->password = !empty($password) ? $password : DBPASSWORD;
(!empty($host) && $host!=null) ? $this->host = $host : '';
(!empty($server) && $server!=null) ? $this->server = $server : '';
}
/**
* @return string error
*/
public function connect(){
$this->connection = mysql_connect($this->server,$this->user,$this->password);
mysql_select_db($this->database, $this->connection) or die ('Konnte die MySql-Datenbank <b>'.$this->database.'</b> nicht auswählen.<br />
Fehler: <i>'.mysql_error().'</i>');
// convert mysql to utf-8
mysql_query("SET NAMES 'utf8'");
mysql_query("SET CHARACTER SET 'utf8'");
$this->connected = true;
}
/**
*/
public function disconnect(){
mysql_close($this->connection);
$this->connected = false;
}
/**
* @param string $query
* @return string $result
*/
public function query($query){
$result = mysql_query($query);
if (!$result) {
// try connecting if connected = false
if(!$this->connected){
$this->connect();
$result = mysql_query($query) or die(mysql_error());
mysql_close($this->connection);
}elseif(!$result && $this->connected){
$message = 'Invalid query: ' . mysql_error() . "<br />\n";
$message .= 'Whole query: ' . $query;
die($message);
}
}
return $result;
}
/**
* @param string $query
* @return string $result
*/
public function fetch($query){
$result = mysql_query($query);
if (!$result) {
$message = 'Invalid query: ' . mysql_error() . "<br />\n";
$message .= 'Whole query: ' . $query;
die($message);
}
while($row = mysql_fetch_assoc($result)){
$data[] = $row;
}
return $data;
}
/**
* @param string $table
* @param string || array $fields
* @param string || array $values
* @param string || array $check_existence
*/
public function insert($table, $fields, $values, $check_existence = false){
// check for sufficient arguments
if( empty($table) || empty($fields) || empty($values) ){
return 'Error: Missing argument for method insert in your database class.';
}
//////////
// prepare $fields
if( !is_array( $fields ) ){
$fields = explode(',',$fields);
}
$fields = array_map('trim',$fields);
foreach($fields as $field){
$tmpfields .= $field.',';
}
$tmpfields = substr($tmpfields, 0, -1);
//////////
// prepare $values
if( !is_array( $values ) ){
$values = explode(',',$values);
}
//
if( is_array( $values[0] ) ){
foreach($values as $key => $value){
$values[$key] = array_map('trim',$values[$key]);
$values[$key] = array_map('mysql_real_escape_string',$values[$key]);
}
}else{
$values = array_map('trim',$values);
$values = array_map('mysql_real_escape_string',$values);
}
//
foreach($values as $value){
if(is_array($value)){
$isArray = 1;
foreach($value as $innerValue){
$innerValue = mysql_real_escape_string($innerValue);
$tmpvalues .= '\''.$innerValue.'\',';
}
$tmpvalues = substr($tmpvalues, 0, -1);
$tmpvalues .= '),(';
}else{
$value = mysql_real_escape_string($value);
$tmpvalues .= '\''.$value.'\',';
}
}
if($isArray){
$tmpvalues = substr($tmpvalues, 0, -3);
}else{
$tmpvalues = substr($tmpvalues, 0, -1);
}
//////////
//check if entry exists
if($check_existence !== false){
//
$checkoutput = $check_existence;
$check_existence = explode(',',$check_existence);
$check_existence = array_map('trim',$check_existence);
//
foreach($check_existence as $checker){
//
if(!is_array($values[0])){
$key = array_keys($fields, $checker);
$check .= '`'.$fields[$key[0]].'`=\''.$values[$key[0]].'\' OR ';
//
}else{
foreach($values as $innerValues){
$key = array_keys($fields, $checker);
$check .= '`'.$fields[$key[0]].'`=\''.$innerValues[$key[0]].'\' OR ';
}
}
}
//
$existence = $this->query('SELECT COUNT(*) FROM '.$table.' WHERE '.substr($check,0,-3));
$existence = mysql_fetch_row($existence);
if($existence[0] < 1){
// entry does not exist
$query = 'INSERT INTO '.$table.' ('.$tmpfields.') VALUES('.$tmpvalues.')';
$this->query($query);
}else{
// entry exists -> error
return 'An entry with this <i>\''. $checkoutput .'\'</i> already exists in database.';
}
//////////
//if existence check is deactive
}else{
$query = 'INSERT INTO '.$table.' ('.$tmpfields.') VALUES('.$tmpvalues.')';
$this->query($query);
return true;
}
}
/**
* @param string $table
* @param string || array $fields
* @param string || array $values
* @param string $operator
*/
public function drop($table, $fields, $values, $operator){
$operator != "OR" ? $operator = 'AND' : '';
//////////
// prepare $fields
$fields = explode(',',$fields);
$fields = array_map('trim',$fields);
//////////
// prepare $values
if( !is_array( $values ) ){
$values = explode(',',$values);
}
//
if( is_array( $values[0] ) ){
foreach($values as $key => $value){
$values[$key] = array_map('trim',$values[$key]);
$values[$key] = array_map('mysql_real_escape_string',$values[$key]);
}
}else{
$values = array_map('trim',$values);
$values = array_map('mysql_real_escape_string',$values);
}
foreach($values as $key => $value){
if( is_array( $value ) ){
$is_Array = 1;
foreach( $value as $key => $innerValue ){
$compare .= '`'.$fields[$key].'`=\''.$innerValue.'\' '.$operator.' ';
}
$compare = substr($compare, 0, -(strlen($operator)+1));
$compare .= ') OR (';
}else{
$compare .= '`'.$fields[$key].'`=\''.$value.'\' '.$operator.' ';
}
}
if($is_Array){
$compare = substr($compare, 0, -6);
}else{
$compare = substr($compare, 0, -(strlen($operator)+1));
}
//
$query = 'DELETE FROM '.$table.' WHERE ('.$compare.')';
$this->query($query);
return true;
}
/**
* @param string $table
* @param string || array $fields
* @param string || array $values
* @param string || array $wherefields
* @param string || array $wherevalues
* @param string $operator
*/
public function update($table, $fields, $values, $wherefields, $wherevalues, $operator){
$operator != "OR" ? $operator = 'AND' : '';
//////////
// prepare $fields
$fields = explode(',',$fields);
$fields = array_map('trim',$fields);
//////////
// prepare $values
if( !is_array( $values ) ){
$values = explode(',',$values);
}
//
if( is_array( $values[0] ) ){
foreach($values as $key => $value){
$values[$key] = array_map('trim',$values[$key]);
$values[$key] = array_map('mysql_real_escape_string',$values[$key]);
}
}else{
$values = array_map('trim',$values);
$values = array_map('mysql_real_escape_string',$values);
}
foreach($values as $key => $value){
$setter .= '`'.$fields[$key].'`=\''.$value.'\', ';
}
$setter = substr($setter, 0, -2);
//////////
// prepare $wherefields
$wherefields = explode(',',$wherefields);
$wherefields = array_map('trim',$wherefields);
//////////
// prepare $wherevalues
if( !is_array( $wherevalues ) ){
$wherevalues = explode(',',$wherevalues);
}
//
if( is_array( $wherevalues[0] ) ){
foreach($wherevalues as $key => $value){
$wherevalues[$key] = array_map('trim',$wherevalues[$key]);
$wherevalues[$key] = array_map('mysql_real_escape_string',$wherevalues[$key]);
}
}else{
$wherevalues = array_map('trim',$wherevalues);
$wherevalues = array_map('mysql_real_escape_string',$wherevalues);
}
// prepare $compare
foreach($wherevalues as $key => $value){
if( is_array( $value ) ){
$is_Array = 1;
foreach( $value as $key => $innerValue ){
$compare .= '`'.$wherefields[$key].'`=\''.$innerValue.'\' '.$operator.' ';
}
$compare = substr($compare, 0, -(strlen($operator)+1));
$compare .= ') OR (';
}else{
$compare .= '`'.$wherefields[$key].'`=\''.$value.'\' '.$operator.' ';
}
}
if($is_Array){
$compare = substr($compare, 0, -6);
}else{
$compare = substr($compare, 0, -(strlen($operator)+1));
}
//
//
$query = 'UPDATE '.$table.' SET '.$setter.' WHERE ('.$compare.')';
echo $query."<br />";
// $this->query($query);
return true;
}
// end of class
}
?>
Refactorings
No refactoring yet !
Tim Cooper
September 3, 2009, September 03, 2009 14:08, permalink
No rating.
Login to rate!
It's called PDO or MySQLi. Use them.
Lukas Oppermann
September 3, 2009, September 03, 2009 14:31, permalink
No rating.
Login to rate!
Well, PDO is only available in PHP 5.1, isn't it? So what if I do something on an older server. I will still have a look at it. And isn't MySQLi basically like MySQL? There is this class I use for when MySQLi is available making all MySQL statements into MySQLi.
Thanks so far.
lukas
September 3, 2009, September 03, 2009 14:45, permalink
No rating.
Login to rate!
Damn, forgot to log in so I can't edit.
Anyway, I forgot to mention, I think I read a in some articles that PDO is quite a bit slower than MySQL/i. So the only real benefit of PDO would be, that it is kind of simple to use, but my class would be too. So I do not quite see where the downside is.
Anyway, if there is no huge downside I did not spot, maybe you or someone else has some ideas how I could improve this class.
Tim Cooper
September 4, 2009, September 04, 2009 10:49, permalink
No rating.
Login to rate!
There's no excuse for running an old version of PHP. PHP 5.1 has been out since 2005 and all servers should be upgraded to at least some version of PHP 5, if not the latest.
So really, why use your class, a slower, non-native extension, when you can use MySQLi?
lukas
September 4, 2009, September 04, 2009 14:10, permalink
No rating.
Login to rate!
Yeah, well if you are in the position to decide this, you are lucky, but often I am not, because the server used is not up to date and I am not in a position to tell the person owning the server to update it, because otherwise my script will not work.
Anyway, you are saying using a class to convert all mysql statements into mysqli is slow? I do not have any test, but I would believe you right away. Do you have any idea whether it is a big drain on the speed of a page?
Anyway, if it is a big difference I might just write a second class for mysqli, but using it i would still have to do all the escaping and stuff outside, right?
I appreciate you trying to get me on the right path and using the “new“ technology, I only do not want to “upgrade“ to mysqli and afterwards I doesn't work on most of my projects and on the ones it does work on, it's not really a gain speed-wise. (Hope the sentence can be understood.)
Thanks.
// Edit # I did look into PDO and it is far more convincing than last time I tried it. So I just want to say thanks for getting me check it out again.
This is a class to do all the database work in a fairly secure way without much code in the actual app/web page.
I feel that I probably did some stuff not as efficient as possible and also that there are some points to improve this class.
I hope you can help me, make this class better.
Thanks,
Lukas