Posted By

Cory on 10/16/10


Tagged

mysql database php library


Versions (?)

Who likes this?

6 people have marked this snippet as a favorite

titosemi
cigraphics
peckham
careca
tux-world
gecube


MySQL Database Class


 / Published in: PHP
 

This is my fairly easy to use MySQL database class, I use this on my Experiment framework BlahFramework, anyways I thought someone else may find this useful or just study the code to get an awesome idea of there own. Enjoy.

  1. <?php
  2. /**
  3. * Copyright (c) 2010 Cory Borrow
  4. *
  5. * Permission is hereby granted, free of charge, to any person obtaining a copy
  6. * of this software and associated documentation files (the "Software"), to deal
  7. * in the Software without restriction, including without limitation the rights
  8. * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
  9. * copies of the Software, and to permit persons to whom the Software is
  10. * furnished to do so, subject to the following conditions:
  11. *
  12. * The above copyright notice and this permission notice shall be included in
  13. * all copies or substantial portions of the Software.
  14. *
  15. * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
  16. * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
  17. * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
  18. * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
  19. * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
  20. * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
  21. * THE SOFTWARE.
  22. *
  23. * Database.php
  24. *
  25. * This file add's a simple and effective way of accessing/querying a MySQL database
  26. *
  27. * @author Cory Borrow
  28. * @copyright Copyright 2010 Cory Borrow
  29. * @link http://snipplr.com/view/42405/mysql-database-class/
  30. * @package Database
  31. * @license MIT License (http://www.opensource.org/licenses/mit-license.php)
  32. */
  33.  
  34. /*
  35. * A quick example
  36. *
  37. * include "Database.php";
  38. *
  39. * $db = new Database("host", "user", "pass", "dbname");
  40. * $db->where("date", time(), WhereOperator::$LessThan)
  41. * ->where("date", time() - 1000, WhereOperator::$MoreThan)
  42. * ->select("posts");
  43. *
  44. * $results = $db->fetchAll();
  45. */
  46.  
  47. class Database
  48. {
  49. /**
  50.   * MySQL database host name
  51.   * @access public
  52.   * @var string
  53.   */
  54. public $Host;
  55.  
  56. /**
  57.   * MySQL datbase username
  58.   * @access public
  59.   * @var string
  60.   */
  61. public $User;
  62.  
  63. /**
  64.   * MySQL database password
  65.   * @access public
  66.   * @var string
  67.   */
  68. public $Pass;
  69.  
  70. /**
  71.   * MySQL database name
  72.   * @access public
  73.   * @var string
  74.   */
  75. public $Name;
  76.  
  77. /**
  78.   * Last preformed SQL query string
  79.   * @access public
  80.   * @var string
  81.   */
  82. public $LastQuery;
  83.  
  84. /**
  85.   * Last MySQL error
  86.   * $access public
  87.   * @var string
  88.   */
  89. public $LastError;
  90.  
  91. /**
  92.   * Where and or-where statements
  93.   * @access protected
  94.   * @var array
  95.   */
  96. protected $where;
  97.  
  98. /**
  99.   * Order by statement
  100.   * @access protected
  101.   * @var string
  102.   */
  103. protected $orderBy;
  104.  
  105. /**
  106.   * Group by statement
  107.   * @access protected
  108.   * @var string
  109.   */
  110. protected $groupBy;
  111.  
  112. /**
  113.   * Limit statement
  114.   * @access protected
  115.   * @var string
  116.   */
  117. protected $limit;
  118.  
  119. /**
  120.   * Database connection handle
  121.   * @access protected
  122.   * @var resource
  123.   */
  124. protected $conn;
  125.  
  126. /**
  127.   * MySQL result handle
  128.   * @access protected
  129.   * @var resource
  130.   */
  131. protected $result;
  132.  
  133. /**
  134.   * Constructor, set's host, username, password, and db name
  135.   */
  136. public function __construct($host, $user, $pass, $name)
  137. {
  138. if(!empty($host))
  139. $this->Host = $host;
  140. else
  141. $this->Host = "localhost";
  142.  
  143. if(!empty($user) && !is_null($pass) && !empty($name))
  144. {
  145. $this->User = $user;
  146. $this->Pass = $pass;
  147. $this->Name = $name;
  148. }
  149. }
  150.  
  151. /**
  152.   * Connect's to mysql database
  153.   * @access public
  154.   */
  155. public function connect()
  156. {
  157. if(!$this->conn)
  158. {
  159. $this->conn = mysql_connect($this->Host, $this->User, $this->Pass);
  160.  
  161. if($this->conn)
  162. mysql_select_db($this->Name, $this->conn);
  163. }
  164. }
  165.  
  166. /**
  167.   * Fetch's first row from MySQL result
  168.   * @return resource|null MySQL Result or null on faliure
  169.   * @access public
  170.   */
  171. public function fetch()
  172. {
  173. if($this->conn && $this->result)
  174. {
  175. return mysql_fetch_object($this->result);
  176. }
  177. return null;
  178. }
  179.  
  180. /**
  181.   * Fetch's all MySQL row's from MySQL result
  182.   * @return array|null MySQL rows or null on faliure
  183.   * @access public
  184.   */
  185. public function fetchAll()
  186. {
  187. if($this->conn && $this->result)
  188. {
  189. $rows = array();
  190.  
  191. while($row = $this->fetch())
  192. $rows[] = $row;
  193.  
  194. return $rows;
  195. }
  196. return null;
  197. }
  198.  
  199. /**
  200.   * Gets the number of rows affected by the last query
  201.   * @return interger Number of rows affected
  202.   * @access public
  203.   */
  204. public function affectedRows()
  205. {
  206. if($this->conn)
  207. {
  208. return mysql_affected_rows($this->conn);
  209. }
  210. }
  211.  
  212. /**
  213.   * Gets the number of rows returned by the last query
  214.   * @return integer Number of rows returned
  215.   * @access public
  216.   */
  217. public function returnedRows()
  218. {
  219. if($this->conn && $this->result)
  220. {
  221. return mysql_num_rows($this->result);
  222. }
  223. }
  224.  
  225. /**
  226.   * Add's a where statement to the sql query
  227.   * @param string $key The field key to match against
  228.   * @param mixed $value The value to match against the database field
  229.   * @param interger $op A WhereOperator value of the type of match
  230.   * @return Instance of Database class
  231.   * @access public
  232.   */
  233. public function where($key, $value, $op)
  234. {
  235. if($this->conn)
  236. {
  237. $value = mysql_real_escape_string($value, $this->conn);
  238. }
  239.  
  240. switch($op)
  241. {
  242. case WhereOperator::$Equal:
  243. $this->where[] = "{$key} = '{$value}'";
  244. break;
  245. case WhereOperator::$NotEqual:
  246. $this->where[] = "{$key} != '{$value}'";
  247. break;
  248. case WhereOperator::$LessThan:
  249. $this->where[] = "{$key} < '{$value}'";
  250. break;
  251. case WhereOperator::$MoreThan:
  252. $this->where[] = "{$key} > '{$value}'";
  253. break;
  254. case WhereOperator::$LessThanOrEqual:
  255. $this->where[] = "{$key} <= '{$value}'";
  256. break;
  257. case WhereOperator::$MoreThanOrEqual:
  258. $this->where[] = "{$key} >= '{$value}'";
  259. break;
  260. case WhereOperator::$Like:
  261. $this->where[] = "{$key} LIKE '{$value}'";
  262. break;
  263. case WhereOperator::$NotLike:
  264. $this->where[] = "{$key} NOT LIKE {$value}";
  265. break;
  266. case WhereOperator::$In:
  267. $this->where[] = "{$key} IN {$value}";
  268. break;
  269. case WhereOperator::$NotIn:
  270. $this->where[] = "{$key} NOT IN {$value}";
  271. break;
  272. case WhereOperator::$IsNull:
  273. $this->where[] = "{$key} IS NULL {$value}";
  274. break;
  275. case WhereOperator::$IsNotNull:
  276. $this->where[] = "{$key} IS NOT NULL {$value}";
  277. break;
  278. case WhereOperator::$Contains:
  279. $this->where[] = "{$key} CONTAINS {$value}";
  280. break;
  281. case WhereOperator::$NotContains:
  282. $this->where[] = "{$key} NOT CONTAINS {$value}";
  283. break;
  284. case WhereOperator::$Between:
  285. $this->where[] = "{$key} BETWEEN {$value}";
  286. break;
  287. case WhereOperator::$NotBetween:
  288. $this->where[] = "{$key} NOT BETWEEN {$value}";
  289. break;
  290. case WhereOperator::$BeginsWith:
  291. $this->where[] = "{$key} BEGINS WITH {$value}";
  292. break;
  293. case WhereOperator::$NotBeginsWith:
  294. $this->where[] = "{$key} NOT BEGINS WITH {$value}";
  295. break;
  296. }
  297. return $this;
  298. }
  299.  
  300. /**
  301.   * Add's an or-where statement to the sql query
  302.   * @param string $key The field key to match against
  303.   * @param mixed $value The value to match against the database field
  304.   * @param integer $op A WhereOperator value of the type of match
  305.   * @return Instance of Database class
  306.   * @access public
  307.   */
  308. public function orWhere($key, $value, $op = 0)
  309. {
  310. return $this->where("OR {$key}", $value, $op);
  311. }
  312.  
  313. /**
  314.   * Add's an order by statement to the sql query
  315.   * @param string $key The mysql field key to order by
  316.   * @param boolean $desc A boolean value to order the results in descending order
  317.   * @return Instance of Database class
  318.   * @access public
  319.   */
  320. public function orderBy($key, $desc)
  321. {
  322. $this->orderBy = "ORDER BY {$key}";
  323. $this->orderBy .= ($desc) ? " DESC " : " ASC ";
  324. return $this;
  325. }
  326.  
  327. /**
  328.   * Add's a group by statement to the sql query
  329.   * @param string $key The mysql field key to group by
  330.   * @return Instance of Database class
  331.   * @access public
  332.   */
  333. public function groupBy($key)
  334. {
  335. $this->groupBy = "GROUP BY {$key} ";
  336. return $this;
  337. }
  338.  
  339. /**
  340.   * Add's a limit statement to the sql query
  341.   * @param integer $start The number of rows to limit or the row to start from
  342.   * @param integer $length The number of rows to return [optional]
  343.   * @return Instance of Database class
  344.   * @access public
  345.   */
  346. public function limit($start, $length = 0)
  347. {
  348. $this->limit = "LIMIT {$start}";
  349. $this->limit .= ($length == 0) ? ", {$length} " : " ";
  350. return $this;
  351. }
  352.  
  353. /**
  354.   * Builds a SQL select statement and preforms query
  355.   * @param string $table The table to preform a query against
  356.   * @param array $keys The field keys to return [optional]
  357.   * @access public
  358.   */
  359. public function select($table, $keys = array())
  360. {
  361. if($this->conn)
  362. {
  363. if(is_array($keys) && count($keys) > 0)
  364. $keys = implode(", ", $keys);
  365. else
  366. $keys = "*";
  367.  
  368. $sql = "";
  369. $sql .= "SELECT {$keys} FROM {$table} ";
  370. $sql = $this->appendWhere($sql);
  371.  
  372. $sql .= $this->groupBy;
  373. $sql .= $this->orderBy;
  374. $sql .= $this->limit;
  375.  
  376. $this->query($sql);
  377. }
  378. }
  379.  
  380. /**
  381.   * Builds a SQL update statement and preforms query
  382.   * @param string $table The table to preform a query against
  383.   * @param array $values The field key's and there $values to update
  384.   * @access public
  385.   */
  386. public function update($table, array $values)
  387. {
  388. if($this->conn)
  389. {
  390. $sql = "";
  391. $sql .= "UPDATE {$table} SET ";
  392. $counter = 0;
  393. $values = $this->cleanArray($values);
  394.  
  395. foreach($values as $key => $value)
  396. {
  397. if($counter < count($values) - 1)
  398. $sql .= "{$key} = '{$value}', ";
  399. else
  400. $sql .= "{$key} = '{$value}' ";
  401. $counter++;
  402. }
  403.  
  404. $sql = $this->appendWhere($sql);
  405. $this->query($sql);
  406. }
  407. }
  408.  
  409. /**
  410.   * Builds a SQL insert statement and preforms query
  411.   * @param string $table The table to preform a query against
  412.   * @param array $values An array of key's and value's to insert into the table.
  413.   * @access public
  414.   */
  415. public function insert($table, array $values)
  416. {
  417. if($this->conn)
  418. {
  419. $keys = array_keys($values);
  420. $keysStr = implode(", ", $keys);
  421. $valuesStr = implode("', '", $this->cleanArray($values));
  422.  
  423. $sql = "";
  424. $sql .= "INSERT INTO {$table} ";
  425. $sql .= "({$keysStr}) VALUES ('{$valuesStr}')";
  426. $this->query($sql);
  427. }
  428. }
  429.  
  430. /**
  431.   * Builds a SQL delete statement and preforms query
  432.   * @param string $table The table to preform a query against
  433.   * @access public
  434.   */
  435. public function delete($table)
  436. {
  437. if($this->conn)
  438. {
  439. $sql = "";
  440. $sql .= "DELETE FROM {$table} ";
  441. $sql = $this->appendWhere($sql);
  442. $this->query($sql);
  443. }
  444. }
  445.  
  446. /**
  447.   * Gets the last id inserted
  448.   * @return interger
  449.   * @access public
  450.   */
  451. public function lastInsertId()
  452. {
  453. if($this->conn)
  454. {
  455. return mysql_insert_id($this->conn);
  456. }
  457. }
  458.  
  459. /**
  460.   * Preforms a SQL query
  461.   * @param string $sql A sql string to query against a database with
  462.   * @access public
  463.   */
  464. public function query($sql)
  465. {
  466. if($this->conn)
  467. {
  468. $this->LastQuery = $sql;
  469. $this->result = mysql_query($sql, $this->conn);
  470. $this->LastError = mysql_error($this->conn);
  471. $this->where = array();
  472. $this->orderBy = "";
  473. $this->groupBy = "";
  474. $this->limit = "";
  475. }
  476. }
  477.  
  478. /**
  479.   * Builds and appends the where statements to a sql query
  480.   * @param string $sql A sql query string
  481.   * @return string The complete query string
  482.   * @access private
  483.   */
  484. private function appendWhere($sql)
  485. {
  486. for($i = 0; $i < count($this->where); $i++)
  487. {
  488. if($i == 0)
  489. {
  490. $sql .= "WHERE {$this->where[$i]} ";
  491. }
  492. else
  493. {
  494. if(substr($this->where[$i], 0, 2) == "OR")
  495. $sql .= "{$this->where[$i]} ";
  496. else
  497. $sql .= "AND {$this->where[$i]} ";
  498. }
  499. }
  500. return $sql;
  501. }
  502.  
  503. /**
  504.   * Preforms mysql_real_escape_string on all strings in an array
  505.   * @param array $arr An array to clean
  506.   * @return array The cleaned array.
  507.   * @access private
  508.   */
  509. private function cleanArray(array $arr)
  510. {
  511. foreach($arr as $item)
  512. {
  513. if(is_string($item))
  514. $item = mysql_real_escape_string($item);
  515. }
  516. return $arr;
  517. }
  518. }
  519.  
  520. class WhereOperator
  521. {
  522. public static $Equal = 0;
  523. public static $Like = 1;
  524. public static $LessThan = 2;
  525. public static $MoreThan = 4;
  526. public static $LessThanOrEqual = 8;
  527. public static $MoreThanOrEqual = 10;
  528. public static $NotEqual = 20;
  529. public static $In = 40;
  530. public static $NotIn = 80;
  531. public static $Contains = 100;
  532. public static $NotContains = 200;
  533. public static $Between = 400;
  534. public static $NotBetween = 800;
  535. public static $BeginsWith = 1000;
  536. public static $NotBeginsWith = 2000;
  537. public static $NotLike = 4000;
  538. public static $IsNull = 8000;
  539. public static $IsNotNull = 10000;
  540. }
  541. ?>

Report this snippet  

You need to login to post a comment.