MYSQL Database Class


 / Published in: PHP
 

URL: http://kampp-productions.dk/functions/examples/database.php

This class operates a range of mysql functions base on arrays.

Example:

Name: Description:

  1. <?php
  2.  
  3. /**
  4.  * Database class
  5.  *
  6.  * @version: 2.2
  7.  * @author: Emil T. Kampp <[email protected]>
  8.  * @revised: 27 may 2007
  9.  *
  10.  **/
  11.  
  12. class Database {
  13. var $host;
  14. var $name;
  15. var $user;
  16. var $pass;
  17. var $prefix;
  18. var $linkId;
  19.  
  20. function Database($mysql) {
  21. foreach($mysql as $k => $v){
  22. $this->$k = $v;
  23. }
  24. if(strlen($this->prefix)>0 && substr($this->prefix, -1) !== "_") $prefix .= "_";
  25. $this->prefix = $prefix;
  26. }
  27.  
  28. function getLastID() {
  29. $id = mysql_fetch_row(mysql_query("SELECT LAST_INSERT_ID()", $this->linkId));
  30. return $id[0];
  31. }
  32.  
  33. function getPossibleValues($tableA, $whereA) {
  34. if(gettype($tableA) == "array") {
  35. $table = "";
  36. foreach($tableA as $t) {
  37. $table .= $this->prefix.$t.", ";
  38. }
  39. $table = substr($table, 0, -2);
  40. } else $table = $this->prefix.$tableA;
  41. if(gettype($whereA) != "array") {
  42. $whereA = array($whereA);
  43. }
  44. $return = array();
  45. foreach($whereA as $where) {
  46. $sql = mysql_query("SHOW COLUMNS FROM ".$table." LIKE '%".$where."%'");
  47. while($arr = mysql_fetch_array($sql)) {
  48. if(strpos($arr['Type'], 'enum')===0) {
  49. $vals = substr($arr['Type'], 5, -1);
  50. } else {
  51. $vals = substr($arr['Type'], 4, -1);
  52. }
  53. $vals = str_replace("'","",$vals);
  54. $vals = explode(",",$vals);
  55. $i = 1;
  56. foreach($vals as $val) {
  57. $return[$arr['Field']][$i++] = $val;
  58. }
  59. $return[$arr['Field']]['default'] = $arr['Default'];
  60. if($arr['Null'] != "NO") $return[$arr['Field']][0] = NULL;
  61. }
  62. }
  63. return $return;
  64. }
  65.  
  66. function connect() {
  67. $this->linkId = mysql_connect($this->host, $this->user, $this->pass);
  68. if(!$this->linkId) {
  69. return false;
  70. }
  71. if(mysql_select_db($this->name, $this->linkId)) return true;
  72. mysql_close($this->linkId);
  73. return false;
  74. }
  75.  
  76. function runSelect($tables, $where = "1", $fieldsA = "*", $order = false, $limit = false, $offset = false, $group = false) {
  77. if(gettype($tables) == "array") {
  78. $table = "";
  79. foreach($tables as $t) {
  80. $table .= $this->prefix.$t.", ";
  81. }
  82. $table = substr($table, 0, -2);
  83. } else $table = $this->prefix.$tables;
  84. if(gettype($fieldsA) == "array") {
  85. $fields = "";
  86. $keys = array_keys($fieldsA);
  87.  
  88. if($keys[0] != '0') {
  89. foreach($keys as $key) {
  90. $fields .= $key.' AS '.$fieldsA[$key].', ';
  91. }
  92. } else {
  93. foreach($fieldsA as $field) {
  94. $fields .= $field.', ';
  95. }
  96. }
  97. $fields = substr($fields, 0, -2);
  98.  
  99. } else $fields = $fieldsA;
  100. $query = "SELECT ".$fields." FROM ".$table." WHERE ".$where.
  101. ($order!== false?" ORDER BY ".$order:($group!==false ? " GROUP BY ".$group : "")).
  102. ($limit !== false?" LIMIT ".$limit:"").
  103. ($offset !== false?" OFFSET ".$offset:"");
  104.  
  105. return mysql_query($query, $this->linkId);
  106. }
  107.  
  108. function runUpdate($table, $valuesA, $where = "1") {
  109. if(gettype($valuesA) == "array") {
  110. $fields = "";
  111. $values = "";
  112. $keys = array_keys($valuesA);
  113. foreach($keys as $key) {
  114. if($valuesA[$key] !== NULL)
  115. $values .= "`".$key."`='".str_replace("'",'\'', $valuesA[$key])."',";
  116. else
  117. $values .= $key."=NULL,";
  118. }
  119. $fields = substr($fields, 0, -1);
  120. $values = substr($values, 0, -1);
  121. } else $values = $valuesA;
  122. $query = "UPDATE ".$this->prefix.$table." SET ".$values." WHERE ".$where;
  123.  
  124. if(mysql_query($query,
  125. $this->linkId))
  126. return mysql_affected_rows($this->linkId);
  127. return false;
  128. }
  129.  
  130. function runDelete($table, $where = "1") {
  131. if(mysql_query("DELETE FROM ".$this->prefix.$table." WHERE ".$where, $this->linkId))
  132. return mysql_affected_rows($this->linkId);
  133. return false;
  134. }
  135.  
  136. function runInsert($table, $valuesA, $onDuplicate = NULL) {
  137. if(gettype($valuesA) == "array") {
  138. $fields = "";
  139. $values = "";
  140. $keys = array_keys($valuesA);
  141. foreach($keys as $key) {
  142. $fields .= "`".$key."`, ";
  143. $values .= ($valuesA[$key]===NULL?"NULL, ":"'".str_replace("'", '\'', $valuesA[$key])."', ");
  144. }
  145. $fields = substr($fields, 0, -2);
  146. $values = substr($values, 0, -2);
  147. }
  148.  
  149. $onDup = "";
  150. if($onDuplicate != NULL) {
  151. $onDup = " ON DUPLICATE KEY UPDATE ";
  152. if(gettype($onDuplicate) == "array") {
  153. $keys = array_keys($onDuplicate);
  154. foreach($keys as $key) {
  155. $onDup .= '`'.$key.'`='.($onDuplicate[$key]===NULL?"NULL,":"'".str_replace("'", '\'', $onDuplicate[$key])."', ");
  156. }
  157. $onDup = substr($onDup, 0, -2);
  158. } else $onDup .= $onDuplicate;
  159. }
  160. $query = "INSERT INTO ".$this->prefix.$table.($fields!==NULL?"(".$fields.")":"").
  161. " VALUES (".$values.")".$onDup;
  162.  
  163. if(mysql_query($query, $this->linkId))
  164. return mysql_affected_rows($this->linkId);
  165. return false;
  166. }
  167.  
  168. function getCells($table){
  169. $query = "SHOW COLUMNS FROM `".$table."`";
  170. $fields = mysql_query($query, $this->linkId) or die('hej');
  171. return $fields;
  172. }
  173.  
  174. function translateCellName($cellName){
  175. $sql = $this->runSelect("mysql_cell_translation","mysql_name = '".$cellName."'");
  176. $row = mysql_fetch_assoc($sql);
  177. return $row['human_name']?$row['human_name']:'<span class="faded">['.$cellName.']</span>';
  178. }
  179.  
  180. function getError() {
  181. return mysql_error($this->linkId);
  182. }
  183.  
  184. function close()
  185. {
  186. mysql_close($this->linkId);
  187. }
  188. }
  189. ?>

Report this snippet  

Comments

RSS Icon Subscribe to comments
Posted By: Nix on May 7, 2008

Uh-oh! This is awesome! Thanks for sharing.

Posted By: abhisek on August 9, 2008

thanks for sharing...may use it sometimes...i used to use a customised version of joomla's database class.

Posted By: Allard on April 17, 2011

Okey, thanks. But I have a question. How can I use the class? I have tried but he doesn't work. Does anyone have an example?

You need to login to post a comment.