Simple Data Access Class


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

This is a simple class to encapsulate CURD functions. This class also includes some simple search methods.


Copy this code and paste it in your HTML
  1. <?php
  2. // Author: Emilio Cavazos
  3. // Date: 4/21/2007
  4. // Notes: Encapsulate mysql functionality (simple code for simple tasks)
  5. //
  6. // ==============
  7. // example usage
  8. // ==============
  9. //
  10. // // contact db parameters
  11. // $contact['first_name'] = $_POST['first_name'];
  12. // $contact['last_name'] = $_POST['last_name'];
  13. // $contact['office_phone'] = $_POST['office_phone'];
  14. // $contact['mobile_phone'] = $_POST['mobile_phone'];
  15. // $contact['email'] = $_POST['email'];
  16.  
  17. // // insert contact
  18. // $dal->updateById('contacts', $contact, 'id', $_GET['con_id']);
  19.  
  20. class DataAccessLayer
  21. {
  22. // connection variables
  23. private $_server = 'localhost';
  24. private $_username = 'root';
  25. private $_password = '';
  26. private $_database = 'db';
  27. public $debug = false;
  28.  
  29. // connection
  30. private $_conn;
  31.  
  32. function __construct() {
  33. $this->_conn = new mysqli($this->_server, $this->_username, $this->_password, $this->_database);
  34.  
  35. // check connection
  36. printf('Connect failed: %s\n', mysqli_connect_error());
  37. exit();
  38. }
  39.  
  40. // change character set to utf8
  41. if (!$this->_conn->set_charset('utf8')) {
  42. printf('Error loading character set utf8: %s\n', $this->_conn->error);
  43. }
  44. }
  45.  
  46. function __destruct() {
  47. $this->_conn->close();
  48. }
  49.  
  50. // print error
  51. public function error() {
  52. return $this->_conn->errno . ': ' . $this->_conn->error;
  53. }
  54.  
  55. // count all rows - return int
  56. public function totalRows($field, $table) {
  57. $sql = 'select ' . $field . ' from '
  58. . $table;
  59.  
  60. $result = $this->_conn->query($sql);
  61.  
  62. // execute query
  63. return $result->num_rows;
  64. }
  65.  
  66. public function query($sql) {
  67. // output sql sting for debugging
  68. // crude debugging
  69. if($this->debug) {
  70. echo '<h3>Query</h3>';
  71. echo '<div>';
  72. echo $sql;
  73. echo '</div>';
  74. }
  75.  
  76. // execute query
  77. return $this->_conn->query($sql);
  78. }
  79.  
  80. public function queryLimit($sql, $page, $pageCount) {
  81. $sql .= ' limit ' . $page . ', ' . $pageCount;
  82.  
  83. // execute query
  84. return $this->query($sql);
  85. }
  86.  
  87. public function nonQuery($sql) {
  88. // execute query
  89. $this->query($sql);
  90.  
  91. return $this->_conn->affected_rows;
  92. }
  93.  
  94. public function select($table) {
  95. $sql = 'select * from '
  96. . $table;
  97.  
  98. // execute query
  99. return $this->query($sql);
  100. }
  101.  
  102. public function selectFields($table, $parameters) {
  103. $sql = 'select ';
  104.  
  105. // build column names
  106. foreach ($parameters as $key => $value) {
  107. $sql .= $value;
  108.  
  109. if($key != end(array_keys($parameters))){
  110. $sql .= ', ';
  111. }
  112. }
  113.  
  114. $sql .= ' from ' . $table;
  115.  
  116. // execute query
  117. return $this->query($sql);
  118. }
  119.  
  120. public function selectById($table, $idName, $idValue) {
  121. $sql = 'select * from '
  122. . $table
  123. . ' where '
  124. . $idName
  125. . ' = '
  126. . $idValue;
  127.  
  128. // execute query
  129. return $this->query($sql);
  130. }
  131.  
  132. public function selectByIdOrder($table, $idName, $idValue, $order) {
  133. $sql = 'select * from '
  134. . $table
  135. . ' where '
  136. . $idName
  137. . ' = '
  138. . $idValue
  139. . ' order by ' . $order;
  140.  
  141. // execute query
  142. return $this->query($sql);
  143. }
  144.  
  145. public function selectWhere($table, $parameters, $where) {
  146. $sql = 'select ';
  147.  
  148. // build column names
  149. foreach ($parameters as $key => $value) {
  150. $sql .= $value;
  151.  
  152. if($key != end(array_keys($parameters))){
  153. $sql .= ', ';
  154. }
  155. }
  156.  
  157. $sql .= ' from ' . $table
  158. .= ' where ' . $where;
  159.  
  160. // execute query
  161. return $this->query($sql);
  162. }
  163.  
  164. public function selectWhereOrder($table, $where, $order) {
  165. $sql = 'select *'
  166. . ' from ' . $table
  167. . ' where ' . $where
  168. . ' order by ' . $order;
  169.  
  170. // execute query
  171. return $this->query($sql);
  172. }
  173.  
  174. public function selectFieldsWhereOrder($table, $parameters, $where, $order) {
  175. $sql = 'select ';
  176.  
  177. // build column names
  178. foreach ($parameters as $key => $value) {
  179. $sql .= $value;
  180.  
  181. if($key != end(array_keys($parameters))){
  182. $sql .= ', ';
  183. }
  184. }
  185.  
  186. $sql .= ' from ' . $table
  187. . ' where ' . $where
  188. . ' order by ' . $order;
  189.  
  190. // execute query
  191. return $this->query($sql);
  192. }
  193.  
  194. public function selectOrder($table, $order) {
  195. $sql = 'select * from '
  196. . $table
  197. . ' order by ' . $order;
  198.  
  199. // execute query
  200. return $this->query($sql);
  201. }
  202.  
  203. public function selectFieldsOrder($table, $parameters, $order) {
  204. $sql = 'select ';
  205.  
  206. // build column names
  207. foreach ($parameters as $key => $value) {
  208. $sql .= $value;
  209.  
  210. if($key != end(array_keys($parameters))){
  211. $sql .= ', ';
  212. }
  213. }
  214.  
  215. $sql .= ' from ' . $table
  216. .= ' order by ' . $order;
  217.  
  218. // execute query
  219. return $this->query($sql);
  220. }
  221.  
  222. //public function selectWhereOrder($table, $parameters, $where, $order)
  223.  
  224. // search query
  225. public function search($table, $fieldsToSearch, $search) {
  226. $searchWords = explode(' ', $search);
  227.  
  228. $sql = 'select *';
  229.  
  230. $sql .= ' from ' . $table . ' where ';
  231.  
  232. // search columns for a match
  233. foreach($searchWords as $wKey => $wValue) {
  234. $sql .= '(';
  235.  
  236. foreach ($fieldsToSearch as $key => $value) {
  237. $sql .= $value . ' like \'%' . $wValue . '%\'';
  238.  
  239. if($key != end(array_keys($fieldsToSearch))){
  240. $sql .= ' or ';
  241. }
  242. }
  243.  
  244. if($wKey != end(array_keys($searchWords))){
  245. $sql .= ') and ';
  246. } else {
  247. $sql .= ')';
  248. }
  249. }
  250.  
  251. // execute query
  252. return $this->query($sql);
  253. }
  254.  
  255. // search by fields query
  256. public function searchFields($table, $fields, $fieldsToSearch, $search) {
  257. $searchWords = explode(' ', $search);
  258.  
  259. $sql = 'select ';
  260.  
  261. // build column names
  262. foreach ($fields as $key => $value) {
  263. $sql .= $value;
  264.  
  265. if($key != end(array_keys($fields))){
  266. $sql .= ', ';
  267. }
  268. }
  269.  
  270. $sql .= ' from ' . $table . ' where ';
  271.  
  272. // search columns for a match
  273. foreach($searchWords as $wKey => $wValue) {
  274. $sql .= '(';
  275.  
  276. foreach ($fieldsToSearch as $key => $value) {
  277. $sql .= $value . ' like \'%' . $wValue . '%\'';
  278.  
  279. if($key != end(array_keys($fieldsToSearch))){
  280. $sql .= ' or ';
  281. }
  282. }
  283.  
  284. if($wKey != end(array_keys($searchWords))){
  285. $sql .= ') and ';
  286. } else {
  287. $sql .= ')';
  288. }
  289. }
  290.  
  291. // execute query
  292. return $this->query($sql);
  293. }
  294.  
  295. // search query
  296. public function searchKeyConstrain($table, $fieldsToSearch, $search, $keyId, $keyValue) {
  297. $searchWords = explode(' ', $search);
  298.  
  299. $sql = 'select *';
  300.  
  301. $sql .= ' from ' . $table . ' where ';
  302.  
  303. // search columns for a match
  304. foreach($searchWords as $wKey => $wValue) {
  305. $sql .= '(';
  306.  
  307. foreach ($fieldsToSearch as $key => $value) {
  308. $sql .= $value . ' like \'%' . $wValue . '%\'';
  309.  
  310. if($key != end(array_keys($fieldsToSearch))){
  311. $sql .= ' or ';
  312. }
  313. }
  314.  
  315. if($wKey != end(array_keys($searchWords))){
  316. $sql .= ') and ';
  317. } else {
  318. $sql .= ')';
  319. }
  320. }
  321.  
  322. $sql .= ' and ' . $keyId . ' = ' . $keyValue;
  323.  
  324. // execute query
  325. return $this->query($sql);
  326. }
  327.  
  328. // search custom query
  329. public function searchQuery($sql, $fieldsToSearch, $search) {
  330. $searchWords = explode(' ', $search);
  331.  
  332. $sql .= ' where ';
  333.  
  334. // search columns for a match
  335. foreach($searchWords as $wKey => $wValue) {
  336. $sql .= '(';
  337.  
  338. foreach ($fieldsToSearch as $key => $value) {
  339. $sql .= $value . ' like \'%' . $wValue . '%\'';
  340.  
  341. if($key != end(array_keys($fieldsToSearch))){
  342. $sql .= ' or ';
  343. }
  344. }
  345.  
  346. if($wKey != end(array_keys($searchWords))){
  347. $sql .= ') and ';
  348. } else {
  349. $sql .= ')';
  350. }
  351. }
  352.  
  353. // execute query
  354. return $this->query($sql);
  355. }
  356.  
  357. // search custom query
  358. public function searchQueryOrder($sql, $fieldsToSearch, $search, $order) {
  359. $searchWords = explode(' ', $search);
  360.  
  361. $sql .= ' where ';
  362.  
  363. // search columns for a match
  364. foreach($searchWords as $wKey => $wValue) {
  365. $sql .= '(';
  366.  
  367. foreach ($fieldsToSearch as $key => $value) {
  368. $sql .= $value . ' like \'%' . $wValue . '%\'';
  369.  
  370. if($key != end(array_keys($fieldsToSearch))){
  371. $sql .= ' or ';
  372. }
  373. }
  374.  
  375. if($wKey != end(array_keys($searchWords))){
  376. $sql .= ') and ';
  377. } else {
  378. $sql .= ')';
  379. }
  380. }
  381.  
  382. $sql .= $order;
  383.  
  384. // execute query
  385. return $this->query($sql);
  386. }
  387.  
  388. // todo: add trim function to values
  389. public function insert($table, $parameters) {
  390.  
  391. $sql = 'insert into '
  392. . $table
  393. . ' (';
  394.  
  395. // build column names
  396. foreach ($parameters as $key => $value) {
  397. $sql .= $key;
  398.  
  399. if($key != end(array_keys($parameters))){
  400. $sql .= ', ';
  401. }
  402. }
  403.  
  404. $sql .= ') values (';
  405.  
  406. // build values for columns
  407. foreach ($parameters as $key => $value) {
  408. $sql .= '\'' . $value . '\'';
  409.  
  410. if($key != end(array_keys($parameters))){
  411. $sql .= ', ';
  412. }
  413. }
  414.  
  415. $sql .= ') ';
  416.  
  417. // execute query
  418. $this->query($sql);
  419.  
  420. return $this->_conn->insert_id;
  421. }
  422.  
  423. public function insertQuery($sql) {
  424. // execute query
  425. $this->query($sql);
  426.  
  427. return $this->_conn->insert_id;
  428. }
  429.  
  430. //public function insertSafe($table, $parameters, $types)
  431.  
  432. // todo: add trim function to values
  433. public function updateById($table, $parameters, $idName, $idValue) {
  434. $sql = 'update '
  435. . $table
  436. . ' set ';
  437.  
  438. // build column value pairs
  439. foreach ($parameters as $key => $value) {
  440. $sql .= $key . ' = \'' . $value . '\'';
  441.  
  442. if($key != end(array_keys($parameters))){
  443. $sql .= ', ';
  444. }
  445. }
  446.  
  447. $sql .= ' where '
  448. . $idName . ' = ' . $idValue;
  449.  
  450. // execute query
  451. $this->query($sql);
  452.  
  453. return $this->_conn->affected_rows;
  454. }
  455.  
  456. public function updateWhere($table, $parameters, $what, $wValue) {
  457. $sql = 'update '
  458. . $table
  459. . ' set ';
  460.  
  461. // build column value pairs
  462. foreach ($parameters as $key => $value) {
  463. $sql .= $key . ' = \'' . $value . '\'';
  464.  
  465. if($key != end(array_keys($parameters))){
  466. $sql .= ', ';
  467. }
  468. }
  469.  
  470. $sql .= ' where '
  471. . $what . ' = \'' . $wValue . '\'';
  472.  
  473. // execute query
  474. $this->query($sql);
  475.  
  476. return $this->_conn->affected_rows;
  477. }
  478.  
  479. public function deleteById($table, $idName, $idValue) {
  480. $sql = 'delete from '
  481. . $table
  482. . ' where '
  483. . $idName . ' = ' . $idValue;
  484.  
  485. // execute query
  486. $this->query($sql);
  487.  
  488. return $this->_conn->affected_rows;
  489. }
  490. }
  491. ?>

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.