Posted By

MMDeveloper on 01/21/11


Tagged

mysql php object oop oriented


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

NaetoH


OOP MySQLi Object Oriented style


 / Published in: PHP
 

URL: http://skyward.nefec.org

MySQLi can be written in both OOP style and procedural style. My other MySQLi class was procedural style, this is the OOP style. Nearly identical in functionality.

Common Usage Examples

$database = new database_mysqli("localhost", "username", "password", "databasename");

//show full results - single query
$sql = "SHOW TABLES";
$tables = $database->query($sql);

//execute multiple queries
$sql = array ("SELECT * FROM blogs WHERE userId = 2", "SELECT * FROM comments WHERE userId = 2");
$results = $database->query($sql, "", true, true);

//execute query, dont care about results
$sql = "DELETE FROM blogs WHERE userID = 2";
$database->justquery($sql);

//show first row only
$sql = "SHOW TABLES";
$firstTable = $database->loadFirstRow($sql);

//show first element of first row only (useful for SELECT COUNT(*) statements)
$sql = "SELECT COUNT(*) FROM users AS U";
$total = $database->loadResult($sql);

$database->destroyConnection();
  1. <?php
  2.  
  3. /**
  4.  * This is the MySQLi OOP database interface class
  5.  * @package Skyward_Landing_Page
  6.  * @subpackage Database Interface
  7.  * @filesource
  8.  * @author Matt Ford
  9.  * @version 2.0
  10.  */
  11.  
  12. class database_mysqli {
  13.  
  14. /**
  15. * database server hostname/IP
  16. * @var string
  17. */
  18. private $host = NULL;
  19.  
  20. /**
  21. * SQL Server login username, encrypted for privacy
  22. * @var string
  23. */
  24. private $username = NULL;
  25.  
  26. /**
  27. * SQL Server login password, encrypted for privacy
  28. * @var string
  29. */
  30. private $password = NULL;
  31.  
  32. /**
  33. * name of database
  34. * @var string
  35. */
  36. public $databaseName = NULL;
  37.  
  38. /**
  39. * SQL Server connection resource
  40. * @var object
  41. */
  42. public $link = NULL;
  43.  
  44. /**
  45. * array of queries run against this object
  46. * @var array
  47. */
  48. public $queries = NULL;
  49.  
  50. /**
  51. * any errors resulting from queries
  52. * @var array
  53. */
  54. public $errors = NULL;
  55.  
  56. public function __construct($host, $username, $password, $database) {
  57.  
  58. $this->host = $host;
  59. $this->username = sha1($username);
  60. $this->password = sha1($password);
  61. $this->databaseName = $database;
  62. $this->link = "";
  63. $this->queries = array ();
  64. $this->errors = array ();
  65.  
  66. $this->sqls = array ();
  67.  
  68. $this->link = @new mysqli($this->host, $username, $password);
  69. if ($this->link->connect_error != null) {
  70. die("Connect Error: " . $this->link->connect_error);
  71. }
  72. else {
  73. if ($this->link->select_db($this->databaseName) === false) {
  74. die("Cannot Select Table: " . $this->link->error);
  75. } else {}
  76. }
  77. }
  78.  
  79. /**
  80. * This method kills the MySQLi connection
  81. * @access public
  82. * @author Matt Ford
  83. */
  84. public function destroyConnection() {
  85. $this->link->kill($this->link->thread_id);
  86. $this->link->close();
  87. }
  88.  
  89. /**
  90. * This method executes a query and returns the raw result resource
  91. * @access public
  92. * @author Matt Ford
  93. * @param string $sql string query
  94. * @return object raw SQL result resource
  95. */
  96. public function justquery($sql) {
  97. $this->queries[] = $sql;
  98. return $this->link->query($sql);
  99. }
  100.  
  101. /**
  102. * This method loads the first value of the first column of the first row of results
  103. * @access public
  104. * @author Matt Ford
  105. * @param string $sql string query
  106. * @return string result from first column of first row of query results
  107. */
  108. public function loadResult($sql) {
  109. if (!($cur = $this->justquery($sql))) {
  110. return null;
  111. }
  112. $ret = null;
  113. if ($row = $cur->fetch_row()) {
  114. $ret = $row[0];
  115. }
  116. $cur->free();
  117. return $ret;
  118. }
  119.  
  120. /**
  121. * This method returns the first row of results
  122. * @access public
  123. * @author Matt Ford
  124. * @param string $sql string query
  125. * @return object first row of results
  126. */
  127. public function loadFirstRow($sql) {
  128. if (!($cur = $this->justquery($sql))) {
  129. return null;
  130. }
  131. $ret = null;
  132. if ($row = $cur->fetch_object()) {
  133. $ret = $row;
  134. }
  135. $cur->free();
  136. return $ret;
  137. }
  138.  
  139. /**
  140. * This method returns the auto-increment value from the last query run
  141. * @access public
  142. * @author Matt Ford
  143. * @return int auto-incremeted (primary key) value of last query
  144. */
  145. public function insertid() {
  146. return $this->link->insert_id;
  147. }
  148.  
  149. /**
  150. * This method returns the number of affected rows in the last insert/update/replace/delete query
  151. * @access public
  152. * @author Matt Ford
  153. * @return int number of affected rows
  154. */
  155. public function numAffectedRows() {
  156. return $this->link->affected_rows;
  157. }
  158.  
  159. /**
  160. * This method queries the database, logs data, and returns results
  161. * @access public
  162. * @author Matt Ford
  163. * @param string|array $sql depending on $batch flag, could be a single string query or an array of queries to run
  164. * @param string $key if supplied, each group of results will be indexed with its respective $key's column value as its object index/position
  165. * @param bool $returns determins if any results will be returned or not, merely for I/O
  166. * @param bool $batch flag denoting whether $sql is a string query or an array of queries to loop over
  167. * @return unset|object depending on $returns, could be nothing, or an object of query results
  168. */
  169. public function query($sql, $key = "", $returns = true, $batch = false) {
  170. $sqls = $result = array ();
  171.  
  172. switch ($batch) {
  173. default:
  174. case true:
  175. foreach ($sql as $index => $query) {
  176. $this->queries[] = $query;
  177. $answer = $this->link->query($query);
  178.  
  179. if (!$answer) {
  180. $this->errors[] = $this->link->error;
  181. }
  182. else {
  183. if ($returns != false) {
  184. if ($answer->num_rows > 0){
  185. while ($row = $answer->fetch_object()) {
  186. if ($key != ""){
  187. $result[$index][$row->$key] = $row;
  188. }
  189. else {
  190. $result[$index][] = $row;
  191. }
  192. }
  193. $answer->free();
  194. } else {}
  195. } else {}
  196. }
  197. }
  198. break;
  199.  
  200. case false:
  201. $this->queries[] = $sql;
  202. $answer = $this->link->query($sql);
  203.  
  204. if (!$answer) {
  205. $this->errors[] = $this->link->error;
  206. $result = false;
  207. }
  208. else {
  209. if ($returns != false) {
  210. if ($answer->num_rows > 0){
  211. while ($row = $answer->fetch_object()) {
  212. if ($key != ""){
  213. $result[$row->$key] = $row;
  214. }
  215. else {
  216. $result[] = $row;
  217. }
  218. }
  219. $answer->free();
  220. } else {}
  221. }
  222. else {
  223. $result = true;
  224. }
  225. }
  226. break;
  227. }
  228.  
  229. return $result;
  230. }
  231.  
  232. /**
  233. * This method simply uses the database library's string escape utility
  234. * @access public
  235. * @author Matt Ford
  236. * @param string $string string needing escaping
  237. * @return string escaped string
  238. */
  239. public function escapeString($string) {
  240. return $this->link->real_escape_string($string);
  241. }
  242.  
  243. /**
  244. * This method builds INSERT/UPDATE queries to allow easy query generation/maintenance for long queries.
  245. * @access public
  246. * @author Matt Ford
  247. * @param array $params key/value pair array of parameters for query
  248. * @return string resulting Query string for MySQLi
  249. */
  250. public function buildSQL($params) {
  251. /*
  252. Usage
  253.  
  254. #INSERT Statements
  255.  
  256. $params = array (
  257. "type" => "insert",
  258. "table" => "eventCal_events",
  259. "doNotQuote" => array(),
  260. "data" => array (
  261. "eventName" => $data->request["eventName"],
  262. "eventText" => $data->request["eventText"],
  263. "eventLocation" => $data->request["eventLocation"],
  264. "eventStartDate_month" => $start["month"],
  265. "eventStartDate_day" => $start["day"],
  266. "eventStartDate_year" => $start["year"],
  267. "eventStartDate_time" => $start["time"],
  268. "eventStartDate_timestamp" => $timestampStart,
  269. "eventEndDate_month" => $end["month"],
  270. "eventEndDate_day" => $end["day"],
  271. "eventEndDate_year" => $end["year"],
  272. "eventEndDate_time" => $end["time"],
  273. "eventEndDate_timestamp" => $timestampEnd,
  274. "occursMonthly" => $occursMonthly,
  275. "occursYearly" => $occursYearly,
  276. "dynamicEvent" => $dynamicEvent,
  277. "dynNthDay" => $data->request["dynOccurrence_freq"],
  278. "dynDayName" => $data->request["dynOccurrence_day"],
  279. "dynMonth" => $data->request["dynOccurrence_month"]
  280. )
  281. );
  282. $sql = $database->buildSQL($params);
  283.  
  284.  
  285.  
  286. #UPDATE Statements
  287.  
  288. $params = array (
  289. "type" => "update",
  290. "table" => "eventCal_events",
  291. "doNotQuote" => array(),
  292. "data" => array (
  293. "eventName" => $data->request["eventName"],
  294. "eventText" => $data->request["eventText"],
  295. "eventLocation" => $data->request["eventLocation"],
  296. "eventStartDate_month" => $start["month"],
  297. "eventStartDate_day" => $start["day"],
  298. "eventStartDate_year" => $start["year"],
  299. "eventStartDate_time" => $start["time"],
  300. "eventStartDate_timestamp" => $timestampStart,
  301. "eventEndDate_month" => $end["month"],
  302. "eventEndDate_day" => $end["day"],
  303. "eventEndDate_year" => $end["year"],
  304. "eventEndDate_time" => $end["time"],
  305. "eventEndDate_timestamp" => $timestampEnd,
  306. "occursMonthly" => $occursMonthly,
  307. "occursYearly" => $occursYearly,
  308. "dynamicEvent" => $dynamicEvent,
  309. "dynNthDay" => $data->request["dynOccurrence_freq"],
  310. "dynDayName" => $data->request["dynOccurrence_day"],
  311. "dynMonth" => $data->request["dynOccurrence_month"]
  312. ),
  313. "where" => array (
  314. "eventID" => $data->request["eventID"],
  315. "eventCreator" => $my->userID
  316. )
  317. );
  318. $sql = $database->buildSQL($params);
  319. */
  320. $sql = "";
  321. $fieldQuantifier = "`";
  322. $valueQuantifier = '"';
  323.  
  324. $params["type"] = strtolower($params["type"]);
  325. $params["doNotQuote"] = (is_array($params["doNotQuote"]) === true) ? $params["doNotQuote"] : array();
  326.  
  327. foreach ($params["data"] as $k => $v) {
  328. $value = stripslashes($v);
  329. $params["data"][$k] = $this->escapeString($value);
  330. }
  331.  
  332. switch ($params["type"]) {
  333. case "insert":
  334. $sql .= "INSERT INTO " . $fieldQuantifier . $params["table"] . $fieldQuantifier . " ";
  335. $sql .= "(" . $fieldQuantifier . implode($fieldQuantifier . ", " . $fieldQuantifier, array_keys($params["data"])) . $fieldQuantifier . ") ";
  336. $sql .= "VALUES(";
  337.  
  338. $vars = array();
  339. foreach ($params["data"] as $k => $v) {
  340. $v = (in_array($k, $params["doNotQuote"])) ? $v : $valueQuantifier . $v . $valueQuantifier;
  341. $vars[] = $v;
  342. }
  343.  
  344. $sql .= implode(", ", $vars);
  345. $sql .= ");";
  346. break;
  347.  
  348. case "update":
  349. $sql .= "UPDATE " . $fieldQuantifier . $params["table"] . $fieldQuantifier . " SET ";
  350.  
  351. $vars = array();
  352. foreach ($params["data"] as $k => $v) {
  353. $v = (in_array($k, $params["doNotQuote"])) ? $v : $valueQuantifier . $v . $valueQuantifier;
  354. $vars[] = $fieldQuantifier . $k . $fieldQuantifier . " = " . $v;
  355. }
  356.  
  357. $sql .= implode(", ", $vars);
  358. $vars = array();
  359. if ($params["where"]) {
  360. $sql .= " WHERE ";
  361. foreach ($params["where"] as $k => $v) {
  362. $vars[] = $fieldQuantifier . $k . $fieldQuantifier . " = " . $valueQuantifier . $v . $valueQuantifier;
  363. }
  364. $sql .= implode(" AND ", $vars);
  365. } else {}
  366. $sql .= ";";
  367. break;
  368. }
  369.  
  370. return $sql;
  371. }
  372. }
  373. ?>

Report this snippet  

You need to login to post a comment.