Hi!
Just thought I'd create a thread for the SQL class.
This is what I have so far. Please come with suggestions or submit updated code if you feel that you wish to help out.
php Code:
<?php class DBmysql
{ private $host;
private $user;
private $pass;
private $data;
private $con;
private $query_sql;
private $query_result;
private $query_fetch =
array();
/**
* Basic description:
* Sets the connection details
*
* Conditions:
* -
*
* Detailed description:
* -
*
* @param array $array
* @return $this
*/ public function setHandler
($host,
$user,
$pass,
$data) { $this->
host =
$host;
$this->
user =
$user;
$this->
pass =
$pass;
$this->
data =
$data;
return $this;
} /**
* Basic description:
* Connects to the database using the connection details set by setHandler function
*
* Conditions:
* You have to set the connection-values with setHandler before using this function
*
* Detailed description:
* -
*
* @return $this
*/ public function connect
() { $this->
con =
mysql_connect($this->
host,
$this->
user,
$this->
pass);
if($this->
con) return $this;
else return false;
} /**
* Basic description:
* Selects the database using the connection details set by setHandler function
*
* Conditions:
* The mysql has to be connected
*
* Detailed description:
* -
*
* @return $this
*/ public function select
() { if(mysql_select_db($this->
data,
$this->
con)) return $this;
else return false;
} /**
* Basic description:
* Disconnects the current connection
*
* Conditions:
* The mysql has to be connected
*
* Detailed description:
* -
*
* @return true or false
*/ public function disconnect
() { if(mysql_close($this->
con)) return true;
else return false;
} /**
* Basic description:
* Loads a query, specified in the parameter
*
* Condition:
* -
*
* Detailed description:
* -
*
* @param string $query
* @return $this
*/ public function loadQuery
($query) { $this->
query_sql =
$query;
return $this;
} /**
* Basic description:
* Executes a query
*
* Conditions:
* You have to load a query before using this function
*
* Detailed description:
* If you haven't loaded a query, you can set the first parameter to the sql statement and it will load the query automaticly
*
* @param string(optional) $sql
* @return $this
*/ public function exeQuery
($sql =
NULL) { if($sql ==
NULL) { if(isset($this->
query_sql)) { $this->
query_result =
mysql_query($this->
query_sql,
$this->
con);
if($this->
query_result) return $this;
else return false;
} else return false;
} elseif($sql !=
NULL) { $this->
loadQuery($sql)->
exeQuery();
} else return false;
} /**
* Basic description:
* Gets the results from the query
*
* Conditions:
* You have to execute a query before you can use this function
*
* Detailed description:
* -
*
* @return $this->query_result
*/ public function getQueryResult
() { if(isset($this->
query_result)) return $this->
query_result;
else return false;
} /**
* Basic description:
* Loads and executes a fetch array
*
* Conditions:
* You have to execute a query before you can use this function
*
* Detailed description:
* If you want it to fetch the array as ASSOC, you need to specify that by setting the first parameter to true.
* If you haven't executed an query, you can set the second parameter to the sql statement and it will execute the query automaticly
*
* @param boolean(optional) $assoc
* @param string(optional) $sql
* @return $this
*/ function loadFetch
($assoc =
false,
$sql =
NULL) { unset($this->
query_fetch);
empty($this->
query_fetch);
$sql =
(is_null($sql) &&
isset($this->
query_result)) ?
$this->
query_result :
$this->
exeQuery($sql)->
getQueryResult();
$fetchType =
($assoc ==
true) ? MYSQL_ASSOC : MYSQL_NUM;
if(!
empty($sql)) { while($fetch =
mysql_fetch_array($sql,
$fetchType)) { $this->
query_fetch[] =
$fetch;
} if(is_array($this->
query_fetch)) return $this;
else return false;
} else return false;
} /**
* Basic description:
* Gets the results from the loaded fetch
*
* Conditions:
* You have to load the fetch by using loadFetch before using this function
*
* Detailed description:
* If you haven't loaded the fetch, you can autoload it by setting the first parameter to true.
* If autoload is set to true, you can specify if you want to fetch the array as assoc by setting the second parameter
* to true or false.
*
* If autoload is set to true, you can specify the sql statement if you haven't already executed the query like this (example):
* $fetch = $db->exeQuery($sql)->getFetch(true, true);
*
* You can, as said, skip the exeQuery function by specifying the sql parameter in this function like this (example):
* $fetch = $db->getFetch(true, true, $sql);
*
* @return $this->query_fetch
*/ public function getFetch
($autoload =
false,
$assoc =
false,
$sql =
NULL) { if($autoload ==
false) { if(is_array($this->
query_fetch) &&
isset($this->
query_fetch)) return $this->
query_fetch;
else return false;
} else { $this->
loadFetch($assoc,
$sql)->
getFetch();
} } /**
* Basic description:
* Gets the number of rows
*
* Conditions:
* A query must be specified, either in the parameter, or a previously executed query
*
* Detailed description:
* You can get the number of rows the query returned in 2 ways, either by executing a query before using this function, like this (example):
* $numrows = $db->exeQuery($sql)->getRows();
*
* Or by setting the parameter to the sql statement, like this (example):
* $numrows = $db->getRows($sql);
*
* @param string(optional) $query
* @return number of rows based on the query
*/ public function getRows
($query =
NULL) { if(is_null($query)) { if(isset($this->
query_result))return mysql_num_rows($this->
query_result);
else return false;
} else { $this->
loadQuery($query)->
exeQuery()->
getRows();
} } /**
* Basic description:
* Secures a string against possible mysql injections
*
* Conditions:
* The parameter must be set
*
* Detailed description:
* If the parameter is set to an int, it will return just the integer without cleaning it.
* If the parameter is set to a string, it will clean the string from mysql injection before returning it
*
* @param string $string
* @return $secured
*/ public function secure
($string) { if(is_string($string)) { get_magic_quotes_gpc() ?
$secured =
stripslashes($string) :
$secured =
addslashes($string);
function_exists('mysql_real_escape_string') ?
$secured =
mysql_real_escape_string($secured) :
$secured =
mysql_escape_string($secured);
} elseif(is_numeric($string)) $secured =
$string;
else return false;
return $secured;
} }?>
Howto use:
PHP Code:
$db = new DBmysql();
Set connection, connect and select db:
PHP Code:
$db->setHandler('localhost', 'root', '', 'testdb')->connect()->select();
//or if you don't want to use singleton
$db->setHandler('localhost', 'root', '', 'testdb');
$db->connect();
$db->select();
For future reference, I will use singleton all the way. You can however choose not to use it.
Execute query:
PHP Code:
$sql = "DELETE * FROM `table`";
//There's alot of ways you can do this, here's one way:
$db->loadQuery($sql)->exeQuery();
//Here's another way:
$db->exeQuery($sql);
Retrieve query result:
PHP Code:
$sql = "SELECT * FROM `table`";
//1:
$result = $db->loadQuery($sql)->exeQuery()->getQueryResult();
//2:
$result = $db->exeQuery($sql)->getQueryResult();
Fetch something as an array:
PHP Code:
$sql = "SELECT * FROM `table`";
//1:
$fetch = $db->loadQuery($sql)->exeQuery()->loadFetch()->getFetch();
//2:
$fetch = $db->exeQuery($sql)->loadFetch()->getFetch();
//3, if you remove the loadFetch function, you must set the first parameter in getFetch to true:
$fetch = $db->exeQuery($sql)->getFetch(true);
//4, if you skip the exeQuery function and the loadFetch function, you must set the first parameter in getFetch to true, and the third parameter to the SQL statement:
$fetch = $db->getFetch(true, , $sql);
//You can also decide if you want to use MYSQL_ASSOC:
$fetch = $db->getFetch(true, true, $sql);
//or:
$fetch = $db->exeQuery($sql)->loadFetch(true)->getFetch();
Check num rows:
PHP Code:
$sql = "SELECT * FROM `table`";
//1:
$rows = $db->exeQuery($sql)->getRows();
//2:
$rows = $db->getRows($sql);
Using secure method:
PHP Code:
$string = "' OR 1 = 1'";
$int = 2;
//Will clean the string from the mysql-injection
$secure_string = $db->secure($string);
//Will return int's as they are
$secure_int = $db->secure($int);
Join the friendly bunch on IRC...
The SQL Class
Threaded Mode
