PHP Classes

File: quickdb.class.php

Recommend this page to a friend!
  Classes of Sarfraz Ahmed   QuickDB   quickdb.class.php   Download  
File: quickdb.class.php
Role: Class source
Content type: text/plain
Description: main class file
Class: QuickDB
Query MySQL using values passed pragmatically
Author: By
Last change: made some necessary changes to execute method.
Date: 14 years ago
Size: 20,595 bytes
 

Contents

Class file image Download
<?php ######################################################### # QuickDB->MySQL Wrapper Class # #-------------------------------------------------------# # Author: SARFRAZ AHMED CHANDIO # # Web Developer # # The Brains # # http://www.brainstech.com # # http://sarfraz-ahmed.blogspot.com/ # # # # Date Created: 12 April 2009 # ######################################################### #-------------------------------------------------------# # +++ Future Additions +++ # #-------------------------------------------------------# # Paging # # Multi-Language Support # #-------------------------------------------------------# class QuickDB { private $con = null; // for db connection private $result = null; // for mysql result resource id private $row = null; // for fetched row private $rows = null; // for number of rows fetched private $affected = null; // for number of rows affected private $insert_id = null; // for last inserted id private $query = null; // for the last run query private $show_errors = null; // for knowing whether to display errors private $emsg = null; // for mysql error description private $eno = null; // for mysql error number // Intialize the class with connection to db public function __construct($host, $user, $password, $db, $persistent = false, $show_errors = false) { if ($show_errors == true) { $this->show_errors = true; } if ($persistent == true) { $this->con = @mysql_pconnect($host, $user, $password); } else { $this->con = @mysql_connect($host, $user, $password); } if ($this->con) { $result = mysql_select_db($db, $this->con) or die("Could Not Select The Database !!"); return $result; } else { die("Could Not Establish The Connection !!"); } } // Close the connection to database public function __destruct() { $this->close(); } // Close the connection to database public function close() { $result = @mysql_close($this->con); return $result; } // stores mysql errors private function setError($msg, $no) { $this->emsg = $msg; $this->eno = $no; if ($this->show_errors == true) { print ' <br /><br /><div style="background:#f6f6f6; padding:5px; font-size:13px; font-family:verdana; border:1px solid #cccccc;"> <span style="color:#ff0000;">MySQL Error Number</span> : ' . $no . '<br /> <span style="color:#ff0000;">MySQL Error Message</span> : ' . $msg . '</div><br />'; } } ################################################# # General Functions # ################################################# // Runs the SQL query (general execute query function) public function execute($command) { # Params: # $command = query command if (!$command) { exit("No Query Command Specified !!"); } $this->query = $command; // For Operational query if ( (stripos($command, "insert ") !== false) || (stripos($command, "update ") !== false) || (stripos($command, "delete ") !== false) || (stripos($command, "replace ") !== false) ) { $this->result = mysql_query($command) or $this->setError(mysql_error(), mysql_errno()); if (stripos($command, "insert ") !== false) { if ($this->result) { $this->insert_id = intval(mysql_insert_id()); } } if ($this->result) { $this->affected = intval(mysql_affected_rows()); // return the number of rows affected return $this->affected; } } else { // For Selection query $this->result = mysql_query($command) or $this->setError(mysql_error(), mysql_errno()); if ($this->result) { $this->rows = intval(mysql_num_rows($this->result)); // return the query resource for later processing return $this->result; } } } // Gets records from table public function select($table, $rows = "*", $condition = null, $order = null) { # Params: # $table = the name of the table # $rows = rows to be selected # $condition = example: where id = 99 # $order = ordering field name if (!$table) { exit("No Table Specified !!"); } $sql = "select $rows from $table"; if($condition) { $sql .= ' where ' . $condition; } else if($order) { $sql .= ' order by ' . $order; } $this->query = $sql; $this->result = mysql_query($sql) or $this->setError(mysql_error(), mysql_errno()); if ($this->result) { $this->rows = intval(mysql_num_rows($this->result)); // return the query resource for later processing return $this->result; } } // Inserts records public function insert($table, $data) { # Params: # $table = the name of the table # $data = field/value pairs to be inserted if ($table) { if ($data) { $this->result = mysql_query("insert into $table set $data") or $this->setError(mysql_error(), mysql_errno()); $this->query = "insert into $table set $data"; if ($this->result) { $this->affected = intval(mysql_affected_rows()); $this->insert_id = intval(mysql_insert_id()); // return the number of rows affected return $this->affected; } } else { print "No Data Specified !!"; } } else { print "No Table Specified !!"; } } // Updates records public function update($table, $data, $condition) { # Params: # $table = the name of the table # $data = field/value pairs to be updated # $condition = example: where id = 99 if ($table) { if ($data) { if ($condition) { $this->result = mysql_query("update $table set $data where $condition") or $this->setError(mysql_error(), mysql_errno()); $this->query = "update $table set $data where $condition"; if ($this->result) { $this->affected = intval(mysql_affected_rows()); // return the number of rows affected return $this->affected; } } else { print "No Condition Specified !!"; } } else { print "No Data Specified !!"; } } else { print "No Table Specified !!"; } } // Deletes records public function delete($table, $condition) { # Params: # $table = the name of the table # $condition = example: where id = 99 if ($table) { if ($condition) { $this->result = mysql_query("delete from $table where $condition") or $this->setError(mysql_error(), mysql_errno()); $this->query = "delete from $table where $condition"; if ($this->result) { $this->affected = intval(mysql_affected_rows()); // return the number of rows affected return $this->affected; } } else { print "No Condition Specified !!"; } } else { print "No Table Specified !!"; } } // returns table data in array public function load_array() { $arr = array(); while ($row = mysql_fetch_object($this->result)) { $arr[] = $row; } return $arr; } // print a complete table from the specified table public function get_html($command, $display_field_headers = true, $table_attribs = 'border="0" cellpadding="3" cellspacing="2" style="padding-bottom:5px; border:1px solid #cccccc; font-size:13px; font-family:verdana;"') { if (!$command) { exit("No Query Command Specified !!"); } $this->query = $command; $this->result = mysql_query($command) or $this->setError(mysql_error(), mysql_errno()); if ($this->result) { $this->rows = intval(mysql_num_rows($this->result)); $num_fields = mysql_num_fields($this->result); print '<br /><br /><div> <table ' . $table_attribs . '>' . "\n" . '<tr>'; if ($display_field_headers == true) { // printing table headers for($i = 0; $i < $num_fields; $i++) { $field = mysql_fetch_field($this->result); print "<td bgcolor='#f6f6f6' style=' border:1px solid #cccccc;'><strong style='color:#666666;'>" . ucwords($field->name) . "</strong></td>\n"; } print "</tr>\n"; } // printing table rows while($row = mysql_fetch_row($this->result)) { print "<tr>"; foreach($row as $td) { print "<td bgcolor='#f6f6f6'>$td</td>\n"; } print "</tr>\n"; } print "</table></div><br /><br />"; } } public function last_insert_id() { if ($this->insert_id) { return $this->insert_id; } } // Counts all records from a table public function count_all($table) { if (!$table) { exit("No Table Specified !!"); } $this->result = mysql_query("select count(*) as total from $table") or $this->setError(mysql_error(), mysql_errno()); $this->query = "select count(*) as total from $table"; if ($this->result) { $this->row = mysql_fetch_array($this->result); return intval($this->row["total"]); } } // Counts records based on specified criteria public function count_rows($command) { # Params: # $command = query command if (!$command) { exit("No Query Command Specified !!"); } $this->query = $command; $this->result = mysql_query($command) or $this->setError(mysql_error(), mysql_errno()); if ($this->result) { return intval(mysql_num_rows($this->result)); } } // Updates a row if it exists or adds if it doesn't already exist. public function insert_update($table, $data, $condition) { # Params: # $table = the name of the table # $data = field/value pairs to be added/updated # $condition = example: where id = 99 if ($table) { if ($data) { if ($condition) { if ($this->row_exists("select * from $table where $condition")) { $this->result = mysql_query("update $table set $data where $condition") or $this->setError(mysql_error(), mysql_errno()); $this->query = "update $table set $data where $condition"; if ($this->result) { $this->affected = intval(mysql_affected_rows()); // return the number of rows affected return $this->affected; } } else { $this->result = mysql_query("insert into $table set $data") or $this->setError(mysql_error(), mysql_errno()); $this->query = "insert into $table set $data"; if ($this->result) { $this->insert_id = intval(mysql_insert_id()); $this->affected = intval(mysql_affected_rows()); // return the number of rows affected return $this->affected; } } } else { print "No Condition Specified !!"; } } else { print "No Data Specified !!"; } } else { print "No Table Specified !!"; } } // Runs the sql query with claus "limit x, x" public function select_limited($table, $start, $return_count, $condition = null, $order = null) { # Params: # $start = starting row for limit clause # $return_count = number of records to fetch # $condition = example: where id = 99 # $order = ordering field name if ($table && $start >= 0 && $return_count) { if ($condition) { if ($order) { $this->result = mysql_query("select * from $table where $condition order by $order limit $start, $return_count") or $this->setError(mysql_error(), mysql_errno()); $this->query = "select * from $table where $condition order by $order limit $start, $return_count"; } else { $this->result = mysql_query("select * from $table where $condition limit $start, $return_count") or $this->setError(mysql_error(), mysql_errno()); $this->query = "select * from $table where $condition limit $start, $return_count"; } } else { if ($order) { $this->result = mysql_query("select * from $table order by $order limit $start, $return_count") or $this->setError(mysql_error(), mysql_errno()); $this->query = "select * from $table order by $order limit $start, $return_count"; } else { $this->result = mysql_query("select * from $table limit $start, $return_count") or $this->setError(mysql_error(), mysql_errno()); $this->query = "select * from $table limit $start, $return_count"; } } if ($this->result) { $this->rows = intval(mysql_num_rows($this->result)); // return the query resource for later processing return $this->result; } } else { print "Parameter Missing !!"; } } ################################################# # Utility Functions # ################################################# // Counts rows from last Select query public function count_select() { if ($this->rows) { return $this->rows; } } // Gets the number of affected rows after Operational query has executed public function count_affected() { if ($this->affected) { return $this->affected; } } // Checks whether a table has records public function has_rows($table) { $rows = $this->count_all($table); if ($rows) { return true; } else { return false; } } // Checks whether or not a row exists with specified criteria public function row_exists($command) { # Params: # $command = query command if (!$command) { exit("No Query Command Specified !!"); } $this->query = $command; $this->result = mysql_query($command) or $this->setError(mysql_error(), mysql_errno()); if ($this->result) { if (mysql_num_rows($this->result)) { return true; } else { return false; } } } // Returns single fetched row public function fetch_row($command) { if (!$command) { exit("No Query Command Specified !!"); } $this->query = $command; $this->result = mysql_query($command) or $this->setError(mysql_error(), mysql_errno()); if ($this->result) { $this->rows = intval(mysql_num_rows($this->result)); $this->row = mysql_fetch_object($this->result); return $this->row; } } // Returns single field value public function fetch_value($table, $field, $condition = null) { if (!$table || !$field) { exit("Arguments Missing !!"); } $query = "select $field from $table"; if ($condition != null) { $query = "select $field from $table where $condition"; } $this->query = $query; $this->result = mysql_query($query) or $this->setError(mysql_error(), mysql_errno()); if ($this->result) { $this->rows = intval(mysql_num_rows($this->result)); $this->row = mysql_fetch_object($this->result); return $this->row->$field; } } // Returns the last run query public function last_query() { if ($this->query) { return $this->alert_msg($this->query); } } // Gets today's date public function get_date($format = null) { # Params: # $format = date format like Y-m-d if ($format) { $today = date($format); } else { $today = date("Y-m-d"); } return $today; } // Gets currents time public function get_time($format = null) { # Params: # $format = date format like H:m:s if ($format) { $time = date($format); } else { $time = date("H:m:s"); } return $time; } // Adds slash to the string irrespective of the setting of getmagicquotesgpc public function smartslashes($value) { if (get_magic_quotes_gpc()) { $value = stripslashes($value); } if (!is_numeric($value)) { $value = mysql_real_escape_string($value); } return $value; } // This function can be used to discard any characters that can be used to manipulate the SQL queries or SQL injection /* EXAMPLE USE: if (is_valid($_REQUEST["username"]) === true && is_valid($_REQUEST["pass"]) === true) { //login now } */ public function is_valid($input) { $input = strtolower($input); if (str_word_count($input) > 1) { $loop = "true"; $input = explode(" ",$input); } $bad_strings = array("'","--","select","union","insert","update","like","delete","1=1","or"); if ($loop) { foreach($input as $value) { if (in_array($value, $bad_strings)) { return false; } else { return true; } } } else { if (in_array($input, $bad_strings)) { return false; } else { return true; } } } // lists tables of database public function list_tables() { $this->result = mysql_query("show tables"); $this->query = "show tables"; if ($this->result) { $tables = array(); while($row = mysql_fetch_array($this->result)) { $tables[] = $row[0]; } foreach ($tables as $table) { print $table . "<br />"; } } } // provides info about given table public function table_info($table) { if ($table) { $this->result = mysql_query("select * from $table"); $this->query = "select * from $table"; $fields = mysql_num_fields($this->result); $rows = mysql_num_rows($this->result); $table = mysql_field_table($this->result, 0); print " The '<strong>" . $table . "</strong>' table has <strong>" . $fields . "</strong> fields and <strong>" . $rows . "</strong> record(s) with following fields.\n<br /><ul>"; for ($i=0; $i < $fields; $i++) { $type = mysql_field_type($this->result, $i); $name = mysql_field_name($this->result, $i); $len = mysql_field_len($this->result, $i); $flags = mysql_field_flags($this->result, $i); print "<strong><li>" . $type . " " . $name . " " . $len . " " . $flags . "</strong></li>\n"; } print "</ul>"; } else { print "The table not specified !!"; } } // displays any mysql errors generated public function display_errors() { if ($this->show_errors == false) { if ($this->emsg) { print ' <br /><br /><div style="background:#f6f6f6; padding:5px; font-size:13px; font-family:verdana; border:1px solid #cccccc;"> <span style="color:#ff0000;">MySQL Error Number</span> : ' . $this->eno . '<br /> <span style="color:#ff0000;">MySQL Error Message</span> : ' . $this->emsg . '</div>'; } else { print ' <br /><br /><div style="background:#f6f6f6; padding:5px; font-size:13px; font-family:verdana; border:1px solid #cccccc;"> <strong>No Erros Found !!</strong> </div>'; } } } // to display success message public function success_msg($msg) { print ' <br /><br /><div align="center" style="background:#EEFDD7; padding:5px; font-size:13px; font-family:verdana; border:1px solid #8DD607;"> ' . $msg . ' </div><br />'; } // to display failure message public function failure_msg($msg) { print ' <br /><br /><div align="center" style="background:#FFF2F2; padding:5px; font-size:13px; font-family:verdana; border:1px solid #FF8080;"> ' . $msg . ' </div><br />'; } // to display general alert message public function alert_msg($msg) { print ' <br /><br /><div align="center" style="background:#FFFFCC; padding:5px; font-size:13px; font-family:verdana; border:1px solid #CCCC33;"> ' . $msg . ' </div><br />'; } //////////////////////////////////////////////////////// } ?>