donnykurnia

mysqli query builder

Aug 27th, 2016
381
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. <?php
  2.  
  3. //helper function
  4. function prepare_mysqli_where_helper($param_type, $conditions) {
  5. $get_condition_values = function($key) use ($conditions) {
  6. return $conditions[$key];
  7. };
  8.  
  9. $keys = array_keys($param_type);
  10. $condition_query = array();
  11. foreach ($keys as $column_name) {
  12. $condition_query[] = "`{$column_name}` = ?";
  13. }
  14.  
  15. $where_condition = implode(' AND ', $condition_query);
  16. $types = implode('', array_values($param_type));
  17. $values = array_map($get_condition_values, $keys);
  18. $types_and_values = array_merge([$types], $values);
  19.  
  20. return compact("where_condition", "types", "values", "types_and_values");
  21. }
  22.  
  23. function refValues($arr) {
  24. if (strnatcmp(phpversion(),'5.3') >= 0) //Reference is required for PHP 5.3+
  25. {
  26. $refs = array();
  27. foreach($arr as $key => $value)
  28. $refs[$key] = &$arr[$key];
  29. return $refs;
  30. }
  31. return $arr;
  32. }
  33.  
  34. function filter_table($kota,$usia,$profesi,$jeniskelamin) {
  35. $main_query = "SELECT tbl_polling.image AS Gambar, tbl_polling.nama AS Nama , COUNT(*) AS Jumlah FROM tbl_choice INNER JOIN tbl_polling ON tbl_choice.pilihan=tbl_polling.id";
  36. $group_order = "GROUP BY tbl_choice.pilihan ORDER BY Jumlah DESC";
  37. $conditions = array();
  38. $param_type = array();
  39.  
  40. if ($kota != 'semuakota') {
  41. $conditions["kota"] = $kota;
  42. $param_type["kota"] = 's';
  43. }
  44. if ($usia != 'semuausia') {
  45. $conditions["usia"] = $usia;
  46. $param_type["usia"] = 's';
  47. }
  48. if ($jeniskelamin != 'semuajeniskelamin') {
  49. $conditions["jk"] = $jeniskelamin;
  50. $param_type["jk"] = 's';
  51. }
  52. if ($profesi != 'semuaprofesi') {
  53. $conditions["profesi"] = $profesi;
  54. $param_type["profesi"] = 's';
  55. }
  56. //call helper
  57. $helper = prepare_mysqli_where_helper($param_type, $conditions);
  58. //build query
  59. $query = $main_query;
  60. if (count($conditions) > 0) {
  61. $query .= ' WHERE ' . $helper['where_condition'];
  62. }
  63. $query .= $group_order;
  64. //mysqli statement prepare and bind params
  65. $stmt = $mysqli->prepare($query);
  66. call_user_func_array(array($stmt, "bind_param"), refValues($helper['types_and_values']));
  67. //execute and return
  68. $result = $stmt->execute();
  69. return $result;
  70. }
Advertisement
Add Comment
Please, Sign In to add comment