When working on different projects, you have to use time-consuming mysql commands every now and then. Same was the case with me and i had really got sick of all those commands, so i decided to create my own mysql wrapper with ability to perform the similar tasks far more quickly and easily than their mysql counterparts. I have used it in many projects i have developed and it has saved me about 50% of the time as compared to using mysql default functions.
You can download the file whole class with example here.
So here is the code for the sample file, name it quickdb_example.php
// Online Class Location: // http://www.phpclasses.org/browse/package/5318.html /* In order to use the example here, you need to create a table, just execute the following SQL lines using your MySQL client: ------------------------------------------------------------- ################################ CREATE TABLE `quickdbtest` ( `catid` int(11) NOT NULL auto_increment, `catname` varchar(255) default NULL, PRIMARY KEY (`catid`) ); insert into `quickdbtest`(`catid`,`catname`) values (1,'Big'),(2,'Small'),(3,'Average'),(4,'Light'),(5,'Huge'); ################################ */ // This file uses most of the functions of QuickDB MySQL wrapper. // It is very easy to use these funtions (methods), see below examples. // Once you know how to use this, i bet you are gonna use it in future as well 🙂 require_once "quickdb.class.php"; // Argumentd are: host, user, password, database, persistent connection, show errors on screen $db = new QuickDB("localhost", "root", "", "test", false, true); ## execute Method/Function // it can run any query whether select, insert, update or delete like mysql_query function // returns: // 1) resource identifier for "Select query" that can later be used with mysql_fetch_array or mysql_fetch_object // 2) number of rows affected for "insert, update or delete" queries // Usage Of: // -------------------------- $result = $db->execute("select * from quickdbtest order by catname"); // $db->execute // Or // $result = $db->select("quickdbtest"); // $db->select while ($row = mysql_fetch_array($result)) { print $row["catname"] . " "; } print " Number of rows selected from previous query : " . $db->count_select(); // $db->count_select() $affected = $db->execute("insert into quickdbtest set catname = 'New Category'"); // Or // $affected = $db->insert("quickdbtest", "catname = 'New Category'"); // $db->insert print " Number of rows affected are: $affected"; // Or print " Number of rows affected using class method: " . $db->count_affected(); // $db->count_affected() $db->success_msg("Record was added successfully !!"); $affected = $db->update("quickdbtest", "catname = 'New Category 2'", "catname = 'New Category'"); // $db->update print " Number of rows affected are: $affected"; $db->delete("quickdbtest", "catid = " . $db->last_insert_id()); // $db->last_insert_id(), $db->delete print " Number of rows affected using class method: " . $db->count_affected(); print " Total records in table are: " . $db->count_all("quickdbtest"); // $db->count_all print " Counting records using 'count_rows' : " . $db->count_rows("select * from quickdbtest"); // $db->count_rows // $db->insert_update will update if row exists, or insert data if it doesn't with catid=5 $affected = $db->insert_update("quickdbtest", "catname='Test Category'", "catid=5"); // $db->insert_update print " Number of rows affected are: $affected "; $result = $db->select_limited("quickdbtest", 4, 1); // $db->select_limited while ($row = mysql_fetch_array($result)) { print $row["catname"] . " "; } if ($db->has_rows("quickdbtest")) // $db->has_rows { print "This table is not empty, it has rows in it !!"; } else { print "Oops, the table is empty !!!"; } if ($db->row_exists("select * from quickdbtest where catid = 5")) // $db->row_exists { print " Yes, row exists"; } else { print " No, row does not exist"; } // fetch a single row from db $row = $db->fetch_row("select * from quickdbtest where catid = 1"); // $db->fetch_row print " The fetched values are: " . $row->catid . " " . $row->catname ; // fetch a single row from db $catname = $db->fetch_value("quickdbtest", "catname", "catid = 3"); // $db->fetch_value print " The fetched value is: " . $catname; print " The date today is: " . $db->get_date(); // $db->get_date() print " The current time is: " . $db->get_time(); // $db->get_time() // $db->last_query() gives the last run query, may be useful for debugging queries print " The last run query was: <strong>" . $db->last_query() . "</strong> "; // using very useful $db->load_data() $db->select("quickdbtest"); // $db->select $data = $db->load_array(); // $db->load_array() print_r($data); ///////////////////////// // using another very useful function that convert db table to a html table $db->get_html("select * from quickdbtest order by catname" , true, 'width = 50%, align="center"'); // $db->get_html print " <strong>Database Tables</strong> "; $db->list_tables(); // $db->list_tables() print " <strong>Table Information</strong> "; $db->table_info("quickdbtest"); // $db->table_info $db->success_msg("Wow, this is cool class !!"); // $db->success_msg // just a wrong table to get that error $db->count_rows("select * from NO_TABLE"); $db->display_errors(); // $db->display_errros() $db->failure_msg("Oops, i must have received the error !!"); // $db->failure_msg $db->alert_msg("This is general alert message !!"); // $db->alert_msg print " " . $db->success_msg("<STRONG>REQUEST:</STRONG> IF YOU LIKE THIS THEN PLEASE GO AHEAD AND <a href='http://www.phpclasses.org/browse/package/5318.html'>RATE</a> THIS AT PHPCLASSES.ORG, THANKS :)") . " ";
and here is the code for the class itself, quickdb.class.php
######################################################### # QuickDB->MySQL Wrapper Class # #-------------------------------------------------------# # Author: SARFRAZ AHMED CHANDIO # # Web Developer # # Brains Technology # # http://www.brainstech.com # # https://sarfraznawaz.wordpress.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 ' <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 . ' <span style="color:#ff0000;">MySQL Error Message</span> : ' . $msg . '</div> '; } } ################################################# # 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 html table from the specified db table public function get_html($command, $display_field_headers = true, $table_attribs = 'border="0" cellpadding="5" cellspacing="0" 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 ' <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; padding:5px;'><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> "; } } 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, useful for debugging queries 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 . " "; } } } // 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 <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 ' <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 . ' <span style="color:#ff0000;">MySQL Error Message</span> : ' . $this->emsg . '</div> '; } else { print ' <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 ' <div align="center" style="background:#EEFDD7; padding:5px; font-size:13px; font-family:tahoma, verdana; border:1px solid #8DD607; margin:5px 0px 5px 0px;"> <strong>' . $msg . ' </strong></div> '; } // to display failure message public function failure_msg($msg) { print ' <div align="center" style="background:#FFF2F2; padding:5px; font-size:13px; font-family:tahoma, verdana; border:1px solid #FF8080; margin:5px 0px 5px 0px;"> <strong>' . $msg . ' </strong></div> '; } // to display general alert message public function alert_msg($msg) { print ' <div align="center" style="background:#FFFFCC; padding:5px; font-size:13px; font-family:tahoma, verdana; border:1px solid #CCCC33; margin:5px 0px 5px 0px;"> <strong>' . $msg . ' </strong></div> '; } //////////////////////////////////////////////////////// }
Enjoy and reply with your feedbacks 🙂