Advanced mySQL OOP Class


First off you may want to organize your main directory and sub directories so that you can find all your things easier.
My Directory Structure:
PHP Code
  1. --Main--
  2. --Inc
  3. ----Classes
  4. --Lib
  5. --Images


If you have a classes folder make this file within that directory: mysql.class.php

In this file you will want something similar to:
PHP Code
  1. <?php
  2. class mySQL{ //Define the class of mySQL
  3. var $db = array(); //your database information
  4. var $persistant_connection = false; //persistant connection
  5. var $queryCounter = 0; //no queries :O
  6. var $TotalTime = 0; //no time yet =D
  7. var $link = ""; //link used for closing
  8. function __Connect(){ //cvonnect to mySQL
  9. $startTime = $this->getMicroTime(); //script start time
  10. $this->TotalTime += $this->getMicroTime() - $startTime; //Add it to the total time
  11. if($this->persistant_connection == 'True'){ //if persistant connection is being used
  12.  
  13. $this->link = mysql_pconnect($this->db["hostname"], $this->db["username"], $this->db["password"]); /connect to mySQL and set the link variable
  14. }else{ //or maybe not..
  15. $this->link = mysql_connect($this->db["hostname"], $this->db["username"], $this->db["password"]); //still connect but with no continuious connection
  16. } //end pers. test
  17. mysql_select_db($this->db["database"], $this->link); //get database with selected Link variable
  18. } //End function
  19. function __Disconnect(){ //Define disconnect function
  20. $startTime = $this->getMicroTime(); //out start time
  21. mysql_close($this->link); //end the link =D
  22. $this->TotalTime += $this->GetMicroTime - $startTime; //Add up the time
  23. } //End the function
  24. function sqlQuery($query){ //function used for mySQL Queries
  25. $startTime = $this->getMicroTime(); //start time
  26. $exec = mysql_query($query); //execute the query
  27. ++$this->queryCounter; //add one to query counter
  28. $this->TotalTime += $this->getMicroTime() - $startTime; //add up more time to the timer
  29. return $exec; //return the results
  30. } //end function
  31. function numRows($sql){ //mysql_num_rows
  32. $startTime = $this->getMicroTime(); //start time =D
  33. $this->TotalTime += $this->getMicroTime() - $startTime; //add to total time
  34. ++$this->queryCounter; //add one to query counter
  35. $exec = mysql_num_rows($sql); //execute the query
  36. return $exec; //Return the results
  37. } //ENd function
  38. function fetchArray($sql){ //mysql_fetch_array
  39. $startTime = $this->getMicroTime(); //Our start time
  40. $this->TotalTime += $this->getMicroTime() - $startTime; //Add to total time
  41. ++$this->queryCounter; //add one to query counter
  42. $exec = mysql_fetch_array($sql); //execute the query
  43. return $exec; //return the results
  44. } //end functions
  45. function getMicroTime(){ //Gert our complete microtime
  46. list($usec, $sec) = explode(" ", microtime()); //simple thing to get the time started
  47. return ((float)$usec + (float)$sec); //give the start time and whatnot
  48. } //end function
  49. function getTotalTime(){ //get our total time
  50. $string = round($this->TotalTime, 6); //round to six decimal places
  51. return $string; //return the time
  52. } //end
  53. function sqlCount(){ //get total sql uqeries
  54. return $this->queryCounter; //return the amount of queries
  55. } //end function
  56. } //end class
  57. ?>


To use this you will want to open your configuration main file and type something a bit like:
PHP Code
  1. <?php
  2. require("mysql.class.php"); //get our class file
  3. $dbinfo = array(
  4. "hostname" => "localhost",
  5. "username" => "root",
  6. "password" => "",
  7. "database" => "sqltest",
  8. ); //all your database information
  9. $MySql = new mySQL; //create a new mySQL Clss for the var: $MySql
  10. $MySql->db = $dbinfo; //set database info
  11. $MySql->__Connect(); //and finally Connect :D
  12. ?>


Real Quick lets run a query through our phpMyAdmin:
PHP Code
  1. CREATE TABLE `test` (
  2. `id` INT( 11 ) NOT NULL auto_increment,
  3. `name` VARCHAR( 255 ) NOT NULL,
  4. `data` VARCHAR( 255 ) NOT NULL,
  5. PRIMARY KEY(`id`)
  6. );
  7.  
  8. INSERT INTO `test` (`name` ,`data`) VALUES ('Bob', 'Old');


In a new file maybe named test.php you could have:
PHP Code
  1. <?php
  2. require("config.php");
  3. $get_data = $MySql->sqlQuery("SELECT * FROM `test` ORDER BY `id` ASC"); //get all data ordered ascending by id
  4. while($objects = $MySql->fetchArray($get_data)){ //lets loop the data
  5. echo "$objects[name] is $objects[data]"; //Couldn't think of anything else but yeah echo data
  6. } //end the loop
  7. echo "<b>Total Queries</b>: ".$MySql->sqlCount()."&nbsp;<b>Execution Time</b>: ".$MySql->getTotalTime()."\n";
  8. $MySql->__Disconnect(); //hehe close connection x]
  9. ?>


Please alert me of any mistakes you may find and i shall have them fixed.
ShadowMage's Avatar
Author:
Views:
2,511
Rating:
Posted on Tuesday 27th January 2009 at 09:22 PM
ShadowMage
ShadowMage's Avatar
Works fine for me...
Posted on Tuesday 27th January 2009 at 01:20 PM
VicVance
VicVance's Avatar
Hey this script does not work on come browser why is that like X10hosting