API tools faq
paste
Guest User

better_mysqli.php

a guest
Jun 20th, 2013
216
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 13.20 KB | None | 0 0
raw download clone embed print report
  1. <?php
  2. class better_mysqli extends mysqli {
  3.  
  4. // version :: date :: comment
  5. // 1.1 :: 05/11/2010 :: Added 'delete' method
  6.  
  7. private $prepared_statements = array();
  8. private $errstr = '';
  9.  
  10.  
  11. function errstr(){
  12. return $this->errstr;
  13. }
  14.  
  15. function bind_placeholder_vars(&$stmt,$params,$debug=0) {
  16. // Credit to: Dave Morgan
  17. // Code ripped from: http://www.devmorgan.com/blog/2009/03/27/dydl-part-3-dynamic-binding-with-mysqli-php/
  18. if ($params != null) {
  19. $types = ''; //initial sting with types
  20. foreach($params as $param) { //for each element, determine type and add
  21. if(is_int($param)) {
  22. $types .= 'i'; //integer
  23. } elseif (is_float($param)) {
  24. $types .= 'd'; //double
  25. } elseif (is_string($param)) {
  26. $types .= 's'; //string
  27. } else {
  28. $types .= 'b'; //blob and unknown
  29. }
  30. }
  31.  
  32. $bind_names = array();
  33. $bind_names[] = $types; //first param needed is the type string
  34. // eg: 'issss'
  35.  
  36. for ($i=0; $i<count($params);$i++) {//go through incoming params and added em to array
  37. $bind_name = 'bind' . $i; //give them an arbitrary name
  38. $$bind_name = $params[$i]; //add the parameter to the variable variable
  39. $bind_names[] = &$$bind_name; //now associate the variable as an element in an array
  40. }
  41.  
  42. if($debug){
  43. echo "\$bind_names:<br />\n";
  44. var_dump($bind_names);
  45. echo "<br />\n";
  46. }
  47. //error_log("better_mysqli has params ".print_r($bind_names, 1));
  48. //call the function bind_param with dynamic params
  49. call_user_func_array(array($stmt,'bind_param'),$bind_names);
  50. return true;
  51. }else{
  52. return false;
  53. }
  54. }
  55.  
  56.  
  57.  
  58.  
  59. function bind_result_array($stmt, &$row){
  60. // Credit to: Dave Morgan
  61. // Code ripped from: http://www.devmorgan.com/blog/2009/03/27/dydl-part-3-dynamic-binding-with-mysqli-php/
  62. $meta = $stmt->result_metadata();
  63. while ($field = $meta->fetch_field()){
  64. $params[] = &$row[$field->name];
  65. }
  66. call_user_func_array(array($stmt, 'bind_result'), $params);
  67. return true;
  68. }
  69.  
  70.  
  71.  
  72.  
  73. function shut($statement){
  74. $stmt_key = md5($statement);
  75. if(array_key_exists($stmt_key,$this->prepared_statements)){
  76. $stmt = $this->prepared_statements[$stmt_key]['stmt'];
  77. $stmt->close();
  78. unset($this->prepared_statements[$stmt_key]);
  79. }
  80. return $stmt;
  81. }
  82.  
  83.  
  84.  
  85.  
  86.  
  87. function insert($statement, $params='', $debug=0, &$dm='', &$id_of_new_record=''){
  88. // note: $params = array of values to use for any placeholders used in statement
  89. // $id_of_new_record = Will be assigned the ID of the record just inserted. Obtained via $mysqli->insert_id
  90.  
  91. $this->clear_sth();
  92.  
  93. // Prepare the statement, if we haven't already
  94. $stmt = '';
  95. $stmt_key = md5($statement);
  96.  
  97. if($debug){
  98. $dm .= "\$stmt_key: $stmt_key<br />\nStatement:<br />\n<blockquote>$statement</blockquote>\n";
  99. $dm .= "\$this->prepared_statements:<br />\n<pre>\n";
  100. $dm .= print_r($this->prepared_statements, true);
  101. $dm .= "</pre><br />\n";
  102. }
  103.  
  104. if($statement==''){
  105. if($debug){$dm .= "\$statement argument is blank!<br />\n";}
  106. return false;
  107. }
  108.  
  109. if(array_key_exists($stmt_key,$this->prepared_statements)){
  110. if($debug){$dm .= "Using prepared statement<br />\n";}
  111. $stmt = $this->prepared_statements[$stmt_key]['stmt'];
  112. $stmt->reset();
  113. }else{
  114. if($debug){$dm .= "Preparing insert statement <blockquote>$statement</blockquote> <br />\n";}
  115. $stmt=$this->stmt_init();
  116. if ($stmt->prepare($statement)) {
  117. if($debug){$dm .= "Statement prepared OK<br />\n";}
  118. $this->prepared_statements[$stmt_key]=array();
  119. $this->prepared_statements[$stmt_key]['stmt'] = $stmt;
  120. $this->prepared_statements[$stmt_key]['params_required'] = $stmt->param_count;
  121. $this->prepared_statements[$stmt_key]['statement'] = $statement;
  122. }else{
  123. if($debug){$dm .= "ERROR preparing statement: ". $stmt->error .", ".$this->error."<br />\n";}
  124. return false;
  125. }
  126.  
  127. }
  128. // we now have a prepared '$stmt' object
  129.  
  130. // bind any placeholders, if required
  131. if($this->prepared_statements[$stmt_key]['params_required']){
  132. if($params==''){
  133. // TO-DO: figure out how to set mysqli error and error number
  134. if($debug){$dm .= "Statement requires 'params' array of values but none were given<br />\n";}
  135. return false;
  136. }
  137. if(!$this->bind_placeholder_vars($stmt,$params,$debug)){
  138. if($debug){$dm .= "Unable to 'bind_placeholder_vars'<br />\n";}
  139. return false;
  140. }
  141. }
  142. // our values have been bound to the placeholders
  143.  
  144.  
  145. // execute the statement
  146. if(!$stmt->execute()){
  147. if($debug){$dm .= "ERROR executing statement: ". $stmt->error .", ".$this->error."<br />\n";}
  148. return false;
  149. }
  150.  
  151. $id_of_new_record = $this->insert_id;
  152.  
  153. if($debug){$dm .= "All done in here, affected rows were: ". $stmt->affected_rows .", new record id is: ". $id_of_new_record ."<br />\n";}
  154. return $stmt->affected_rows;
  155.  
  156. }
  157.  
  158.  
  159.  
  160. function clear_sth(){
  161. // clears any remaining results when using multi_query
  162. while($this->more_results()){
  163. if($this->next_result()){
  164. $result = $this->use_result();
  165. $result->free_result();
  166. }
  167. }
  168. // reset all exisitng prepared statements
  169. foreach($this->prepared_statements as $stmt_key => $a) {
  170. $stmt = $this->prepared_statements[$stmt_key]['stmt'];
  171. $stmt->reset();
  172. }
  173. return true;
  174. }
  175.  
  176.  
  177.  
  178.  
  179. function select($statement, &$row, $params='', $debug=0, &$dm=''){
  180.  
  181. $row = array();
  182.  
  183. $this->errstr = '';
  184.  
  185. // Prepare the statement, if we haven't already
  186. $stmt = '';
  187. $stmt_key = md5($statement);
  188.  
  189. if($debug > 1){
  190. $dm .= "\$stmt_key: $stmt_key<br />\nStatement:<br />\n<blockquote>$statement</blockquote>\n";
  191. $dm .= "\$this->prepared_statements:<br />\n<pre>\n";
  192. $dm .= print_r($this->prepared_statements, true);
  193. $dm .= "</pre><br />\n";
  194. }
  195.  
  196. if($statement==''){
  197. if($debug){$dm .= "\$statement argument is blank!<br />\n";}
  198. $this->errstr = "\$statement argument is blank!";
  199. return false;
  200. }
  201.  
  202. //cleanup any previous result sets that are still hanging out
  203. $this->clear_sth();
  204.  
  205. if(array_key_exists($stmt_key, $this->prepared_statements)){
  206. if($debug){$dm .= "Using prepared statement<br />\n";}
  207. $stmt = $this->prepared_statements[$stmt_key]['stmt'];
  208. }else{
  209. if($debug){$dm .= "Preparing select statement <blockquote>$statement</blockquote> <br />\n";}
  210. $stmt=$this->stmt_init();
  211. if ($stmt->prepare($statement)) {
  212. if($debug){$dm .= "Statement prepared OK<br />\n";}
  213. $this->prepared_statements[$stmt_key]=array();
  214. $this->prepared_statements[$stmt_key]['stmt'] = $stmt;
  215. $this->prepared_statements[$stmt_key]['statement'] = $statement;
  216. $this->prepared_statements[$stmt_key]['params_required'] = $stmt->param_count;
  217. }else{
  218. if($debug){$dm .= "ERROR preparing statement: ". $stmt->error .", ".$this->error."<br />\n";}
  219. $this->errstr = "ERROR preparing statement: ". $stmt->error .", ".$this->error;
  220. return false;
  221. }
  222. }
  223. // bind any placeholders, if required
  224. if($this->prepared_statements[$stmt_key]['params_required']){
  225. if($params==''){
  226. // TO-DO: figure out how to set mysqli error and error number
  227. if($debug){$dm .= "Statement requires 'params' array of values but none were given<br />\n";}
  228. $this->errstr = "Statement requires 'params' array of values but none were given";
  229. return false;
  230. }
  231. if(!$this->bind_placeholder_vars($stmt,$params)){
  232. if($debug){$dm .= "Unable to 'bind_placeholder_vars'<br />\n";}
  233. $this->errstr = "Unable to 'bind_placeholder_vars'";
  234. return false;
  235. }
  236. }
  237. // execute the statement
  238. if(!$stmt->execute()){
  239. if($debug){$dm .= "ERROR executing statement: ". $stmt->error .", ".$this->error."<br />\n";}
  240. $this->errstr = "ERROR executing statement: ". $stmt->error .", ".$this->error;
  241. return false;
  242. }
  243. // bind the results
  244. if(!$this->bind_result_array($stmt, $row)){
  245. return false;
  246. }
  247. //return the stmt handle
  248. if($debug){$dm .= "All done in here, affected rows were: ". $stmt->affected_rows ."<br />\n";}
  249. return $stmt;
  250. }
  251.  
  252.  
  253. function update($statement, $params='', $debug=0, &$dm=''){
  254. // note: $params = array of values to use for any placeholders used in statement
  255.  
  256. // Prepare the statement, if we haven't already
  257. $stmt = '';
  258. $stmt_key = md5($statement);
  259.  
  260. if($debug){
  261. $dm .= "\$stmt_key: $stmt_key<br />\nStatement:<br />\n<blockquote>$statement</blockquote>\n";
  262. $dm .= "\$this->prepared_statements:<br />\n<pre>\n";
  263. $dm .= print_r($this->prepared_statements, true);
  264. $dm .= "</pre><br />\n";
  265. }
  266.  
  267. if($statement==''){
  268. if($debug){$dm .= "\$statement argument is blank!<br />\n";}
  269. return false;
  270. }
  271.  
  272. //cleanup any previous result sets that are still hanging out
  273. $this->clear_sth();
  274.  
  275. if(array_key_exists($stmt_key,$this->prepared_statements)){
  276. if($debug){$dm .= "Using prepared statement<br />\n";}
  277. $stmt = $this->prepared_statements[$stmt_key]['stmt'];
  278. $stmt->reset();
  279. }else{
  280. if($debug){$dm .= "Preparing update statement <blockquote>$statement</blockquote> <br />\n";}
  281. $stmt=$this->stmt_init();
  282. if ($stmt->prepare($statement)) {
  283. if($debug){$dm .= "Statement prepared OK<br />\n";}
  284. $this->prepared_statements[$stmt_key]=array();
  285. $this->prepared_statements[$stmt_key]['stmt'] = $stmt;
  286. $this->prepared_statements[$stmt_key]['params_required'] = $stmt->param_count;
  287. $this->prepared_statements[$stmt_key]['statement'] = $statement;
  288. }else{
  289. if($debug){$dm .= "ERROR preparing statement: ". $stmt->error .", ".$this->error."<br />\n";}
  290. return false;
  291. }
  292.  
  293. }
  294. // we now have a prepared '$stmt' object
  295.  
  296. // bind any placeholders, if required
  297. if($this->prepared_statements[$stmt_key]['params_required']){
  298. if($params==''){
  299. // TO-DO: figure out how to set mysqli error and error number
  300. if($debug){$dm .= "Statement requires 'params' array of values but none were given<br />\n";}
  301. return false;
  302. }
  303. if(!$this->bind_placeholder_vars($stmt,$params,$debug)){
  304. if($debug){$dm .= "Unable to 'bind_placeholder_vars'<br />\n";}
  305. return false;
  306. }
  307. }
  308. // our values have been bound to the placeholders
  309.  
  310.  
  311. // execute the statement
  312. if(!$stmt->execute()){
  313. if($debug){$dm .= "ERROR executing statement: ". $stmt->error .", ".$this->error."<br />\n";}
  314. return false;
  315. }
  316.  
  317. if($debug){$dm .= "All done in here, affected rows were: ". $stmt->affected_rows ."<br />\n";}
  318. return $stmt->affected_rows>0?$stmt->affected_rows:true;
  319.  
  320. }
  321.  
  322.  
  323. function delete($statement, $params='', $debug=0, &$dm=''){
  324.  
  325. // Prepare the statement, if we haven't already
  326. $stmt = '';
  327. $stmt_key = md5($statement);
  328.  
  329. if($debug){
  330. $dm .= "\$stmt_key: $stmt_key<br />\nStatement:<br />\n<blockquote>$statement</blockquote>\n";
  331. $dm .= "\$this->prepared_statements:<br />\n<pre>\n";
  332. $dm .= print_r($this->prepared_statements, true);
  333. $dm .= "</pre><br />\n";
  334. }
  335.  
  336. //cleanup any previous result sets that are still hanging out
  337. $this->clear_sth();
  338.  
  339. if(array_key_exists($stmt_key, $this->prepared_statements)){
  340. if($debug){$dm .= "Using prepared statement<br />\n";}
  341. $stmt = $this->prepared_statements[$stmt_key]['stmt'];
  342. $stmt->reset();
  343. }else{
  344. if($debug){$dm .= "Preparing update statement <blockquote>$statement</blockquote> <br />\n";}
  345. //cleanup any previous result sets that are still hanging out
  346. $this->clear_sth();
  347. $stmt=$this->stmt_init();
  348. if ($stmt->prepare($statement)) {
  349. $this->prepared_statements[$stmt_key]=array();
  350. $this->prepared_statements[$stmt_key]['stmt'] = $stmt;
  351. $this->prepared_statements[$stmt_key]['statement'] = $statement;
  352. $this->prepared_statements[$stmt_key]['params_required'] = $stmt->param_count;
  353. }else{
  354. # delete
  355. if($debug){$dm .= "ERROR preparing statement: ". $stmt->error .", ".$this->error."<br />\n";}
  356. return false;
  357. }
  358. }
  359. // bind any placeholders, if required
  360. if($this->prepared_statements[$stmt_key]['params_required']){
  361. if($params==''){
  362. // TO-DO: figure out how to set mysqli error and error number
  363. if($debug){$dm .= "Statement requires 'params' array of values but none were given<br />\n";}
  364. return false;
  365. }
  366. if(!$this->bind_placeholder_vars($stmt,$params)){
  367. if($debug){$dm .= "Unable to 'bind_placeholder_vars'<br />\n";}
  368. return false;
  369. }
  370. }
  371. // execute the statement
  372. if(!$stmt->execute()){
  373. if($debug){$dm .= "ERROR executing statement: ". $stmt->error .", ".$this->error."<br />\n";}
  374. return false;
  375. }
  376. if($debug){$dm .= "All done in here, affected rows were: ". $stmt->affected_rows ."<br />\n";}
  377. return true;
  378. }
  379.  
  380.  
  381.  
  382.  
  383. private function requires_placeholder_params($statement){
  384. if(preg_match('/\?/',$statement)){
  385. return true;
  386. }else{
  387. return false;
  388. }
  389. }
  390.  
  391.  
  392.  
  393. } // end class
  394. ?>
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!