Posted By

ecavazos on 01/21/08


Tagged

mysql php data query dynamic access layer dal


Versions (?)

Who likes this?

2 people have marked this snippet as a favorite

mbcdg
Juntalis


Simple Data Access Class


 / Published in: PHP
 

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

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

Report this snippet  

You need to login to post a comment.