PHP - Lightweight Database and Rowset class, ActiveRecord style functions


/ Published in: PHP
Save to your folder(s)

Been wanting to made one of these for a while and finally I got round to it.

For some examples please visit the post:
http://www.dom111.co.uk/blog/coding/db/8

Any comments suggestions appreciated.
CreativeCommons ShareAlike


Copy this code and paste it in your HTML
  1. <?php
  2. // DB - simple database wrapper
  3. // The idea behind this database wrapper is to have a simple to use database wrapper that includes some useful
  4. // commands to do some every day tasks.
  5. //
  6.  
  7. class DB {
  8. // options
  9. public $enableMoreSearch = true; // disable this if any of your field names end with
  10. // _like, _before, _less_than, _after or _greater_than as this may cause problems
  11. // enables __call()s like find_by_name_like('dom') to match Dom, dom or DOM
  12.  
  13. // variables
  14. public $c = null;
  15. public $db = null;
  16. public $table = null;
  17. public $id = 'id';
  18.  
  19. private $engine = "mysql";
  20. private $host = "localhost";
  21. private $user = "root";
  22. private $pass = "";
  23. private $q = "";
  24. // private $db = "test";
  25. private $fields = array();
  26.  
  27. // __construct
  28. // sets up the connection to the mysql server and selects the database
  29. public function __construct($a = null) {
  30. if (is_array($a)) {
  31. $this->host = (isset($a['host'])) ? $a['host'] : $this->host;
  32. $this->user = (isset($a['user'])) ? $a['user'] : $this->host;
  33. $this->pass = (isset($a['pass']) && is_string($a['pass'])) ? $a['pass'] : $this->pass;
  34. $this->database = (isset($a['database'])) ? $a['database'] : $this->database;
  35. $this->table = (isset($a['table'])) ? $a['table'] : $this->table;
  36.  
  37. } elseif (is_string($a)) {
  38. if (preg_match("/^(mysql):\/\/([^:]+):(.*)@([a-z0-9\-\.]+):?(\d+)?\/([a-z0-9_\-]+)(\/([a-z0-9_\-]+))?\/?$/i", $a, $m)) {
  39. $this->engine = $m[1];
  40. $this->host = $m[4];
  41. $this->user = $m[2];
  42. $this->pass = $m[3];
  43. $this->db = $m[6];
  44. $this->table = isset($m[8]) ? $m[8] : null;
  45.  
  46. } else {
  47. throw new Exception("DB Error! (Invalid connection details)");
  48. }
  49. }
  50.  
  51. if (!is_resource($this->c)) {
  52. if ($this->c = $this->_connect()) {
  53. if ($this->_select_db()) {
  54. return $this->c;
  55.  
  56. } else {
  57. throw new Exception("DB Error! (Could not select database)");
  58. }
  59.  
  60. } else {
  61. throw new Exception("DB Error! (Cound not connect to server)");
  62. }
  63. }
  64. }
  65.  
  66. // __destruct
  67. // closes the connection
  68. public function __destruct() {
  69. return @$this->_close();
  70. // the @ is used because PHP shares mysql connections and if more than one
  71. // 'DB' instance is finished with at the same time, the second, third, etc
  72. // will produce errors, as the connection is already closed, the @ hides
  73. // any errors
  74. }
  75.  
  76. // __call
  77. // magic method to map unbound functions
  78. // used for db->find_all_by_username("dom") etc
  79. public function __call($f, $a) {
  80. if (preg_match("/^find_(all_by|by)_([_a-zA-Z]\w*)$/", $f, $m)) {
  81. if ($m[1] == "by") {
  82. $array = false;
  83.  
  84. } elseif ($m[1] == "all_by") {
  85. $array = true;
  86. }
  87.  
  88. if (isset($a[1]) && is_array($a[1]) && isset($a[1]['table'])) {
  89. $t = $a[1]['table'];
  90. } else {
  91. $t = null;
  92. }
  93.  
  94. $fields = explode("_or_", $m[2]);
  95.  
  96. if (count($fields) == 1) {
  97. $fields = explode("_and_", $m[2]);
  98. $mode = "AND";
  99. } else {
  100. $mode = "OR";
  101. }
  102.  
  103. $find = array();
  104.  
  105. if (count($fields) == count($a[0]) || (!is_array($a[0]) && count($fields) == 1)) {
  106. foreach ($fields as $i => $key) {
  107. if (is_array($a[0])) {
  108. $value = $a[0][$i];
  109.  
  110. } else {
  111. $value = $a[0];
  112. }
  113.  
  114. if ($this->enableMoreSearch) {
  115. if (substr(strtolower($key), -5) == '_like') {
  116. $key = substr($key, 0, -5);
  117. $find[$key] = array('mode' => 'LIKE', 'value' => $value);
  118. } elseif (substr(strtolower($key), -13) == '_greater_than') {
  119. $key = substr($key, 0, -13);
  120. $find[$key] = array('mode' => '>', 'value' => $value);
  121. } elseif (substr(strtolower($key), -6) == '_after') {
  122. $key = substr($key, 0, -6);
  123. $find[$key] = array('mode' => '>', 'value' => $value);
  124. } elseif (substr(strtolower($key), -10) == '_less_than') {
  125. $key = substr($key, 0, -10);
  126. $find[$key] = array('mode' => '<', 'value' => $value);
  127. } elseif (substr(strtolower($key), -7) == '_before') {
  128. $key = substr($key, 0, -7);
  129. $find[$key] = array('mode' => '<', 'value' => $value);
  130. } else {
  131. $find[$key] = $value;
  132. }
  133. } else {
  134. $find[$key] = $value;
  135. }
  136. }
  137. } elseif (count($a) == count($fields)) {
  138. foreach ($fields as $i => $key) {
  139. $value = $a[$i];
  140. if ($this->enableMoreSearch) {
  141. if (substr(strtolower($key), -5) == '_like') {
  142. $key = substr($key, 0, -5);
  143. $find[$key] = array('mode' => 'LIKE', 'value' => $value);
  144. } elseif (substr(strtolower($key), -13) == '_greater_than') {
  145. $key = substr($key, 0, -13);
  146. $find[$key] = array('mode' => '>', 'value' => $value);
  147. } elseif (substr(strtolower($key), -6) == '_after') {
  148. $key = substr($key, 0, -6);
  149. $find[$key] = array('mode' => '>', 'value' => $value);
  150. } elseif (substr(strtolower($key), -10) == '_less_than') {
  151. $key = substr($key, 0, -10);
  152. $find[$key] = array('mode' => '<', 'value' => $value);
  153. } elseif (substr(strtolower($key), -7) == '_before') {
  154. $key = substr($key, 0, -7);
  155. $find[$key] = array('mode' => '<', 'value' => $value);
  156. } else {
  157. $find[$key] = $value;
  158. }
  159. } else {
  160. $find[$key] = $value;
  161. }
  162. }
  163. } else {
  164. throw new Exception('Number of fields and parameters don\'t match');
  165. }
  166.  
  167. return $this->find($find, array('table' => $t, 'mode' => $mode, 'array' => $array));
  168.  
  169. } elseif (preg_match("/^find_or_(initialize|create)_by_([_a-zA-Z]\w*)$/", $f, $m)) {
  170.  
  171. $fields = explode("_and_", $m[2]);
  172.  
  173. $find = array();
  174.  
  175. if (count($fields) > 1 && is_array($a[0]) && count($fields) == count($a[0])) {
  176. foreach ($fields as $i => $key) {
  177. $value = $a[0][$i];
  178. $find[$key] = $value;
  179. }
  180.  
  181. } else {
  182. $find[$m[2]] = $a[0];
  183. }
  184.  
  185. $check = $this->find($find, array('mode' => 'AND'));
  186.  
  187. if (!empty($check)) {
  188. return $check;
  189.  
  190. } else {
  191. if ($m[1] == "create") {
  192. $this->create($find);
  193.  
  194. return $this->find($this->lastId());
  195.  
  196. } elseif ($m[1] == "initialize") {
  197. $row = $this->_new();
  198.  
  199. foreach ($find as $key => $value) {
  200. $row->$key = $value;
  201. }
  202.  
  203. return $row;
  204. }
  205. }
  206. }
  207.  
  208. return false;
  209. }
  210.  
  211. // _connect
  212. // simple function, maybe enable use of other database engines?
  213. private function _connect() {
  214. if ($this->engine == 'mysql') {
  215. return mysql_connect($this->host, $this->user, $this->pass);
  216. }
  217. }
  218.  
  219. // _query
  220. // pretty simple again
  221. private function _query($q) {
  222. $this->q = $q; // store the last query
  223. if ($this->engine == 'mysql') {
  224. return mysql_query($q, $this->c);
  225. }
  226. }
  227.  
  228. // _num_rows
  229. // pretty simple again
  230. private function _num_rows($r) {
  231. if ($this->engine == 'mysql') {
  232. return mysql_num_rows($r);
  233. }
  234. }
  235.  
  236. // _select_db
  237. // pretty simple again
  238. private function _select_db() {
  239. if ($this->engine == 'mysql') {
  240. return mysql_select_db($this->db, $this->c);
  241. }
  242. }
  243.  
  244. // _close
  245. // pretty simple again
  246. private function _close() {
  247. if ($this->engine == 'mysql') {
  248. return mysql_close($this->c);
  249. }
  250. }
  251.  
  252. // _close
  253. // pretty simple again
  254. private function _auto_increment_id() {
  255. if ($this->engine == 'mysql') {
  256. return mysql_insert_id($this->c);
  257. }
  258. }
  259.  
  260. // _get
  261. // accepts a query string, or mysql resource and returns either and array
  262. // or an array of arrays depending on how many rows there are or if the
  263. // $array variable is true
  264. // private function _get($q, $array = false) {
  265. private function _get($q, $options = array()) {
  266. if (is_string($q)) {
  267. $q = $this->_query($q);
  268. }
  269.  
  270. if (!is_resource($q)) {
  271. return false;
  272. }
  273.  
  274. $r = $this->_num_rows($q);
  275.  
  276. $options['array'] = !empty($options['array']) ? $options['array'] : false;
  277. $options['raw'] = isset($options['raw']) ? $options['raw'] : false;
  278.  
  279. if ($r) {
  280. // if we have a result...
  281. if ($r === 1 && !$options['array']) {
  282. // if there is only 1 row and array is not forced
  283. if ($this->engine == 'mysql') {
  284. // if we're using mysql...
  285. if ($options['raw']) {
  286. // if $options row == true, return the raw array, not a DB record object
  287. return mysql_fetch_assoc($q);
  288.  
  289. } else {
  290. return new DB_record($this, mysql_fetch_assoc($q));
  291. }
  292. }
  293. } else {
  294. // $options array == true or we have more than one row
  295. $a = array();
  296.  
  297. if ($this->engine == 'mysql') {
  298. // if we're using mysql
  299. while (($row = mysql_fetch_assoc($q)) != false) {
  300. // create a $row variable with the contetnts of the row
  301. if ($options['raw']) {
  302. $a[] = $row;
  303.  
  304. } else {
  305. $a[] = new DB_record($this, $row);
  306. }
  307. }
  308. }
  309.  
  310. return $a;
  311. }
  312.  
  313. } else {
  314. return false;
  315. }
  316. }
  317.  
  318. public function query($q) {
  319. return $this->_query($q);
  320. }
  321.  
  322. // find
  323. // builds a query to pass to get based on the variables passed
  324. // if $id is null it will use the current data->id, if $id is an array it
  325. // will match based on the array settings (correctly matching nulls), or
  326. // $id can just be a specified id
  327. // to match all users with a name like bob you could do:
  328. // db->find(array('name' => array('mode' => 'LIKE', 'value' => '%bob%')), array('table' => 'users'));
  329. // to retrieve all rows from the current table you could do:
  330. // db->find();
  331. // to retrieve a specific id you could do:
  332. // db->find(123)
  333. public function find($id = null, $options = array()) {
  334. if (empty($options['table'])) {
  335. if (!empty($this->table)) {
  336. $options['table'] = $this->table;
  337.  
  338. } else {
  339. return false;
  340. }
  341. }
  342.  
  343. if ($id === null) {
  344. $options['array'] = isset($options['array']) ? $options['array'] : true; // default behaviour to return an array
  345.  
  346. $q = "SELECT * FROM `{$options['table']}`";
  347.  
  348. if (!empty($options['order']) && is_array($options['order'])) {
  349. $q .= ' ORDER BY ';
  350. $order = array();
  351.  
  352. foreach ($options['order'] as $field => $direction) {
  353. $direction = strtoupper($direction);
  354.  
  355. if ($direction !== 'ASC' && $direction !== 'DESC') {
  356. if ($field === 'RAND()') {
  357. $order[] = 'RAND()';
  358.  
  359. } else {
  360. $order[] .= "`{$field}`";
  361. }
  362.  
  363. } else {
  364. $order[] .= "`{$field}` {$direction}";
  365. }
  366. }
  367. $q .= implode(", ", $order);
  368.  
  369. } elseif (!empty($options['order']) && is_string($options['order'])) {
  370. $q .= " ORDER BY {$options['order']}";
  371. }
  372.  
  373. if (empty($options['offset']) && !empty($options['limit'])) {
  374. $q .= " LIMIT {$options['limit']}";
  375.  
  376. } elseif (!empty($options['offset']) && !empty($options['limit'])) {
  377. $q .= " LIMIT {$options['offset']},{$options['limit']}";
  378. }
  379.  
  380. $q .= ";";
  381. return $this->_get($q, $options);
  382.  
  383. } else {
  384. $options['array'] = isset($options['array']) ? $options['array'] : null;
  385.  
  386. $where = array();
  387.  
  388. if (is_array($id)) {
  389. foreach ($id as $k => $v) {
  390. if (is_array($v)) {
  391. $where[] = "`{$k}` {$v['mode']} '{$v['value']}'";
  392.  
  393. } elseif ($v === null) {
  394. $where[] = "ISNULL(`{$k}`)";
  395.  
  396. } else {
  397. $where[] = "`{$k}` = '{$v}'";
  398. }
  399. }
  400. } else {
  401. $where[] = "`{$this->id}` = '{$id}'";
  402. }
  403.  
  404. if (isset($options['mode']) || ($options['mode'] != "OR" && $options['mode'] != "AND")) {
  405. $options['mode'] = 'OR';
  406. }
  407.  
  408. $q = "SELECT * FROM `{$options['table']}` WHERE ".implode(" {$options['mode']} ", $where);
  409.  
  410. if (isset($options['order']) && !empty($options['order']) && is_array($optinos['order'])) {
  411. $q .= ' ORDER BY ';
  412. $order = array();
  413.  
  414. foreach ($options['order'] as $field => $direction) {
  415. $direction = strtoupper($direction);
  416.  
  417. if ($direction !== 'ASC' || $direction !== 'DESC') {
  418. if ($field === 'RAND()') {
  419. $order[] = 'RAND()';
  420.  
  421. } else {
  422. $order[] .= "`{$field}`";
  423. }
  424.  
  425. } else {
  426. $order[] .= "`{$field}` {$direction}";
  427. }
  428. }
  429. $q .= implode(", ", $order);
  430. } elseif (isset($options['order']) && !empty($options['order']) && is_string($options['order'])) {
  431. $q .= " ORDER BY {$options['order']}";
  432. }
  433.  
  434. if (empty($options['offset']) && !empty($options['limit'])) {
  435. $q .= " LIMIT {$options['limit']}";
  436.  
  437. } elseif (!empty($options['offset']) && !empty($options['limit'])) {
  438. $q .= " LIMIT {$options['offset']},{$options['limit']}";
  439. }
  440.  
  441. $q .= ';';
  442.  
  443. return $this->_get($q, $options);
  444. }
  445. }
  446.  
  447. // create
  448. // builds an INSERT query based on the variables passed
  449. // $data must be an associative array with indexes matching the field names
  450. // $table can be omitted and the current table will be used
  451. // to insert a new row to the current table you could do:
  452. // db->create(array("name" => "bob", "password" => md5(md5("mytest")), "email" => "[email protected]"));
  453. public function create($data = null, $options = array()) {
  454. if (empty($options['table'])) {
  455. if (!empty($this->table)) {
  456. $options['table'] = $this->table;
  457.  
  458. } else {
  459. return false;
  460. }
  461. }
  462.  
  463. if ($data !== null && is_array($data)) {
  464. $set = array();
  465.  
  466. foreach ($data as $k => $v) {
  467. if ($v === null) {
  468. $set[] = "`{$k}` = NULL";
  469.  
  470. } else {
  471. $set[] = "`{$k}` = '{$v}'";
  472. }
  473. }
  474.  
  475. $q = "INSERT INTO `{$options['table']}` SET ".implode(", ", $set).";";
  476.  
  477. $r = $this->_query($q);
  478.  
  479. if ($r) {
  480. // if the query is successful return the create row
  481. return $this->find($this->lastId());
  482. } else {
  483. // else return false
  484. return false;
  485. }
  486.  
  487. } else {
  488. // data was null or not an array
  489. return false;
  490. }
  491. }
  492.  
  493. // update
  494. // builds an UPDATE query based on the variables passed
  495. // $data must be an associative array with indexes matching the field names
  496. // $table and $id can be omitted for the current id/table to be used
  497. // to update the current record you could use:
  498. // db->update(array("title" => "Cheese", "price" => 9.99));
  499. public function update($data, $id = null, $options = array()) {
  500. if (empty($options['table'])) {
  501. if (!empty($this->table)) {
  502. $options['table'] = $this->table;
  503.  
  504. } else {
  505. return false;
  506. }
  507. }
  508.  
  509. if (is_array($data) && !empty($id)) {
  510. $set = array();
  511.  
  512. foreach ($data as $k => $v) {
  513. if ($v === null) {
  514. $set[] = "`{$k}` = NULL";
  515.  
  516. } else {
  517. $set[] = "`{$k}` = '{$v}'";
  518. }
  519. }
  520.  
  521. $q = "UPDATE `{$options['table']}` SET ".implode(", ", $set)." WHERE `{$this->id}` = '{$id}';";
  522.  
  523. return $this->_query($q);
  524.  
  525. } else {
  526. return false;
  527. }
  528. }
  529.  
  530. // delete
  531. // builds a DELETE query based on the variables passed
  532. // all varibles are optional and will default to the current table/id
  533. // $id can be an associative array of fields/values to match before deleting
  534. // to delete all news posts by bob you could do:
  535. // db->delete(array("user" => "bob"), "news");
  536. // to delete all news posts by bob with car in the title you could do:
  537. // db->delete(array("user" => "bob", "title" => array("mode" => "LIKE", "value" => "%car%")), "news", "OR");
  538. public function delete($id = null, $options = array()) {
  539. if (empty($options['table'])) {
  540. if (!empty($this->table)) {
  541. $options['table'] = $this->table;
  542. } else {
  543. return false;
  544. }
  545. }
  546.  
  547. $where = array();
  548.  
  549. if (is_array($id)) {
  550. foreach ($id as $k => $v) {
  551. if (is_array($v) && isset($v['mode'], $v['value'])) {
  552. $where[] = "`{$k}` {$v['mode']} '{$v['value']}'";
  553.  
  554. } elseif ($v === null) {
  555. $where[] = "ISNULL(`{$k}`)";
  556.  
  557. } elseif (!is_array($v) && !empty($v)) {
  558. $where[] = "`{$k}` = '{$v}'";
  559. }
  560. }
  561. } elseif (is_string($id) || is_numeric($id)) {
  562. $where[] = "`{$this->id}` = {$id}";
  563. }
  564.  
  565. if ($mode !== "AND" || $mode !== "OR") {
  566. $mode = "AND";
  567. }
  568.  
  569. $q = "DELETE FROM `{$options['table']}` WHERE ".implode(" {$mode} ", $where);
  570.  
  571. if (isset($options['limit']) && !empty($options['limit'])) {
  572. $q .= " LIMIT {$options['limit']}";
  573. }
  574. $q .= ";";
  575.  
  576. return $this->_query($q);
  577. }
  578.  
  579. // random
  580. // returns $n, random rows from $table
  581. public function random($n = 1, $options = array()) {
  582. if (empty($options['table'])) {
  583. if (!empty($this->table)) {
  584. $options['table'] = $this->table;
  585.  
  586. } else {
  587. return false;
  588. }
  589. }
  590.  
  591. if (!is_numeric($n)) {
  592. $n = 1;
  593. }
  594.  
  595. return $this->_get("SELECT * FROM `{$options['table']}` ORDER BY RAND() LIMIT {$n};");
  596. }
  597.  
  598. // numRows
  599. // returns the number of rows in $table
  600. // $table can be omitted and the number of rows in the current table will be returned
  601. public function numRows($options = array()) {
  602. if (empty($options['table'])) {
  603. if (!empty($this->table)) {
  604. $options['table'] = $this->table;
  605.  
  606. } else {
  607. return false;
  608. }
  609. }
  610.  
  611. $r = $this->_query("SELECT `{$this->id}` FROM `{$options['table']}`;");
  612.  
  613. return $this->_num_rows($r);
  614. }
  615.  
  616. // lastId
  617. // return the id of the last inserted row
  618. public function lastId() {
  619. return $this->_auto_increment_id();
  620. }
  621.  
  622. // table
  623. // creates a clone of the current db object using $options as the default table
  624. // (or $options['table'] if $options is an array)
  625. public function table($options = null) {
  626. if ($options === null && !empty($this->table)) {
  627. return clone $this;
  628.  
  629. } elseif (is_string($options) && !empty($options)) {
  630. $r = clone $this;
  631. $r->table = $options;
  632.  
  633. return $r;
  634.  
  635. } elseif (is_array($options) && isset($options['table']) && !empty($options['table'])) {
  636. $r = clone $this;
  637. $r->table = $options['table'];
  638.  
  639. return $r;
  640.  
  641. } else {
  642. return false;
  643. }
  644. }
  645.  
  646. public function getFields($options = array()) {
  647. if (!empty($this->fields)) {
  648. return $this->fields;
  649. } else {
  650. if (empty($options['table'])) {
  651. $options['table'] = $this->table;
  652.  
  653. } else {
  654. return false;
  655. }
  656.  
  657. $q = "SHOW FIELDS FROM `{$options['table']}`;";
  658. $fields = $this->_get($q, array('raw' => true));
  659.  
  660. foreach ($fields as $field) {
  661. if (isset($field['Field'])) {
  662. $this->fields[] = $field['Field'];
  663. } elseif (isset($field['field'])) {
  664. $this->fields[] = $field['Field'];
  665. } elseif (isset($field[0])) {
  666. $this->fields[] = $field[0];
  667. }
  668. }
  669. }
  670. return $this->fields;
  671. }
  672.  
  673. public function _new() {
  674. return new DB_record($this);
  675. }
  676.  
  677. }
  678.  
  679. class DB_record {
  680. private $_data = array();
  681. private $_db = null;
  682. private $_new = false;
  683.  
  684. function __construct($db = null, $data = null) {
  685. if (!empty($db)) {
  686. $this->_db = $db;
  687. }
  688.  
  689. if (is_array($data)) {
  690. $this->_data = $data;
  691.  
  692. foreach ($this->_data as $key => $value) {
  693. if (is_array($value)) {
  694. $this->_data = null;
  695. break;
  696.  
  697. } else {
  698. $this->$key = $value;
  699. }
  700. }
  701. } else {
  702. $this->_new = true;
  703.  
  704. if (!empty($this->_db)) {
  705. foreach ($this->_db->getFields() as $field) {
  706. if ($field != $this->_db->id) {
  707. $this->$field = null;
  708. }
  709. }
  710. } else {
  711. return false;
  712. }
  713. }
  714.  
  715. if (empty($this->_data) || empty($this->_db)) {
  716. return false;
  717.  
  718. } else {
  719. return $this;
  720. }
  721. }
  722.  
  723. function save() {
  724. $save = array();
  725.  
  726. foreach ($this->_db->getFields() as $field) {
  727. if (isset($this->$field) && $field != $this->_db->id) {
  728. $this->_data[$field] = $this->$field;
  729. $save[$field] = $this->$field;
  730. }
  731. }
  732.  
  733. $id = $this->_db->id;
  734.  
  735. if ($this->_new) {
  736. $r = $this->_db->create($save);
  737. $this->$id = $this->_db->lastId();
  738. $this->_new = false;
  739. return $r;
  740.  
  741. } else {
  742. return $this->_db->update($save, $this->$id);
  743. }
  744. }
  745.  
  746. function isNew() {
  747. return $this->_new;
  748. }
  749.  
  750. }
  751. ?>

URL: http://www.dom111.co.uk/blog/coding/db/8

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.