3
* Little Software Stats
5
* An open source program that allows developers to keep track of how their software is being used
7
* @package Little Software Stats
9
* @copyright Copyright (c) 2011, Little Apps
10
* @license http://www.gnu.org/licenses/gpl.html GNU General Public License v3
11
* @link http://little-apps.org
18
* Manage connection to MySQL database
20
* @package Little Software Stats
22
* @link http://github.com/a1phanumeric/PHP-MySQL-Class
26
public $sLastError; // Holds the last error
27
public $sLastQuery; // Holds the last query
28
public $aResult; // Holds the MySQL query result
29
public $iRecords; // Holds the total number of records returned
30
public $iAffected; // Holds the total number of records affected
31
public $aRawResults; // Holds raw 'arrayed' results
32
public $aArrayedResult; // Holds a single 'arrayed' result
33
public $aArrayedResults; // Holds multiple 'arrayed' results (usually with a set key)
35
private $sHostname = MYSQL_HOST; // MySQL Hostname
36
private $sUsername = MYSQL_USER; // MySQL Username
37
private $sPassword = MYSQL_PASS; // MySQL Password
38
private $sDatabase = MYSQL_DB; // MySQL Database
39
private $sPrefix = MYSQL_PREFIX; // MySQL Prefix
41
private $sDBLink; // Database Connection Link
45
* Assigning values to variables
47
function __construct() {
52
* Connects class to database
54
* @param bool $bPersistant Use persistant connection?
55
* @return bool Returns true if connection has been made
57
private function Connect($bPersistant = false){
59
mysql_close($this->sDBLink);
63
$this->sDBLink = mysql_pconnect($this->sHostname, $this->sUsername, $this->sPassword);
65
$this->sDBLink = mysql_connect($this->sHostname, $this->sUsername, $this->sPassword);
69
$this->sLastError = 'Could not connect to server: ' . mysql_error($this->sDBLink);
74
$this->sLastError = 'Could not connect to database: ' . mysql_error($this->sDBLink);
81
* Select database to use
83
* @return bool Returns true if database was selected
85
private function UseDB(){
86
if (!mysql_select_db($this->sDatabase, $this->sDBLink)) {
87
$this->sLastError ='Cannot select database: ' . mysql_error($this->sDBLink);
95
* Executes MySQL query
97
* @param string $sSQLQuery Query to execute
98
* @return bool Returns true if execution was successful
100
public function ExecuteSQL($sSQLQuery){
101
$this->sLastQuery = $sSQLQuery;
102
if($this->aResult = mysql_query($sSQLQuery, $this->sDBLink)){
103
$this->iRecords = @mysql_num_rows($this->aResult);
104
$this->iAffected = @mysql_affected_rows($this->sDBLink);
107
$this->sLastError = mysql_error($this->sDBLink);
113
* Adds a record to the database based on the array key names
115
* @param array $aVars Variables to insert
116
* @param type $sTable Table to insert variables into
117
* @param array $aExclude Column(s) to exclude
118
* @return bool Returns true if insert was successful
120
public function Insert($aVars, $sTable, $aExclude = ''){
126
array_push($aExclude, 'MAX_FILE_SIZE');
129
$aVars = $this->SecureData($aVars);
131
$sSQLQuery = 'INSERT INTO `' . $sPrefix . $sTable . '` SET ';
132
foreach($aVars as $iKey=>$sValue){
133
if(in_array($iKey, $aExclude)){
136
$sSQLQuery .= '`' . $iKey . '` = "' . $sValue . '", ';
139
$sSQLQuery = substr($sSQLQuery, 0, -2);
141
if($this->ExecuteSQL($sSQLQuery)){
149
* Deletes a record from the database
151
* @param string $sTable Table to delete from
152
* @param array $aWhere Column(s) to search for
153
* @param string $sLimit Limit of how many records
154
* @param bool $bLike Use like to search
155
* @return bool Returns true if delete was successful
157
public function Delete($sTable, $aWhere='', $sLimit='', $bLike=false){
158
$sSQLQuery = 'DELETE FROM `' . $sPrefix . $sTable . '` WHERE ';
159
if(is_array($aWhere) && $aWhere != ''){
161
$aWhere = $this->SecureData($aWhere);
163
foreach($aWhere as $iKey=>$sValue){
165
$sSQLQuery .= '`' . $iKey . '` LIKE "%' . $sValue . '%" AND ';
167
$sSQLQuery .= '`' . $iKey . '` = "' . $sValue . '" AND ';
171
$sSQLQuery = substr($sSQLQuery, 0, -5);
175
$sSQLQuery .= ' LIMIT ' .$sLimit;
178
if($this->ExecuteSQL($sSQLQuery)){
186
* Selects row(s) from table
188
* @param string $sFrom Table to select from
189
* @param array $aWhere Column(s) to search for
190
* @param string $sOrderBy Column to order by
191
* @param string $sLimit Number of rows that can be returned
192
* @param bool $bLike Use 'LIKE' instead of '='
193
* @param string $sOperand Operand to use ('AND' or 'OR')
194
* @return bool Returns true if select was successful
196
public function Select($sFrom, $aWhere='', $sOrderBy='', $sLimit='', $bLike=false, $sOperand='AND'){
198
if(trim($sFrom) == ''){
202
$sSQLQuery = 'SELECT * FROM `' . $sPrefix . $sFrom . '` WHERE ';
204
if(is_array($aWhere) && $aWhere != ''){
206
$aWhere = $this->SecureData($aWhere);
208
foreach($aWhere as $iKey=>$sValue){
210
$sSQLQuery .= '`' . $iKey . '` LIKE "%' . $sValue . '%" ' . $sOperand . ' ';
212
$sSQLQuery .= '`' . $iKey . '` = "' . $sValue . '" ' . $sOperand . ' ';
216
$sSQLQuery = substr($sSQLQuery, 0, -5);
219
$sSQLQuery = substr($sSQLQuery, 0, -7);
223
$sSQLQuery .= ' ORDER BY ' .$sOrderBy;
227
$sSQLQuery .= ' LIMIT ' .$sLimit;
230
if($this->ExecuteSQL($sSQLQuery)){
231
if($this->iRecords == 1)
232
$this->ArrayResult();
233
else if($this->iRecords > 1)
234
$this->ArrayResults();
244
* Updates a record in the database based on WHERE
246
* @param string $sTable Table to update
247
* @param array $aSet Variables to update
248
* @param array $aWhere Column(s) to search for
249
* @param array $aExclude Column(s) to exclude
250
* @return bool Returns true if update was successful
252
public function Update($sTable, $aSet, $aWhere, $aExclude = ''){
254
if(trim($sTable) == '' || !is_array($aSet) || !is_array($aWhere)){
261
array_push($aExclude, 'MAX_FILE_SIZE');
263
$aSet = $this->SecureData($aSet);
264
$aWhere = $this->SecureData($aWhere);
268
$sSQLQuery = 'UPDATE `' . $sPrefix . $sTable . '` SET ';
270
foreach($aSet as $iKey=>$sValue){
271
if(in_array($iKey, $aExclude)){
274
$sSQLQuery .= '`' . $iKey . '` = "' . $sValue . '", ';
277
$sSQLQuery = substr($sSQLQuery, 0, -2);
281
$sSQLQuery .= ' WHERE ';
283
foreach($aWhere as $iKey=>$sValue){
284
$sSQLQuery .= '`' . $iKey . '` = "' . $sValue . '" AND ';
287
$sSQLQuery = substr($sSQLQuery, 0, -5);
289
if($this->ExecuteSQL($sSQLQuery)){
297
*'Arrays' a single result
299
* @return array Returns a single row
301
public function ArrayResult(){
302
$this->aArrayedResult = mysql_fetch_assoc($this->aResult) or die (mysql_error($this->sDBLink));
303
return $this->aArrayedResult;
307
* 'Arrays' multiple result
309
* @return array Returns multiple rows
311
public function ArrayResults(){
312
$this->aArrayedResults = array();
313
while ($aData = mysql_fetch_assoc($this->aResult)){
314
$this->aArrayedResults[] = $aData;
316
return $this->aArrayedResults;
320
* Performs a 'mysql_real_escape_string' on the entire array/string
322
* @param array|string $aData Data to prevent from SQL injection
323
* @return array|string Returns SQL injection stripped data
325
public function SecureData($aData){
326
if(is_array($aData)){
327
foreach($aData as $iKey=>$sVal){
328
if(!is_array($aData[$iKey])){
329
$aData[$iKey] = mysql_real_escape_string($aData[$iKey], $this->sDBLink);
333
$aData = mysql_real_escape_string($aData, $this->sDBLink);