daily pastebin goal
56%
SHARE
TWEET

Userspice DB.php (with patch by plb) - example 1

Faguss Jun 16th, 2017 (edited) 101 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2. Let's find multiple users sharing the same characteristic
  3.  
  4. On the beginning we could write SQL like this:
  5. SELECT * FROM test_table WHERE (Data=1 AND UserID=1) OR (Data=1 AND UserID=2)
  6.  
  7. Argument for get() will need to look like this:
  8. [
  9.     "or",
  10.     [
  11.         "and",
  12.         ["Data",   "=", 1],
  13.         ["UserID", "=", 1]
  14.     ],
  15.     [
  16.         "and",
  17.         ["Data",   "=", 1],
  18.         ["UserID", "=", 2]
  19.     ]
  20. ]
  21.  
  22. Here's PHP code to do this query:
  23. */
  24.  
  25. $query_array = ["or"];
  26. $user_list   = [["id"=>1], ["id"=>2]];
  27.  
  28. foreach ($user_list as $user_info)
  29.     $query_array[] = [ "and", ["Data","=",1], ["UserID","=",$user_info["id"]] ];
  30.  
  31. $result = $db -> get("test_table", $query_array);
  32.  
  33.  
  34.  
  35.  
  36. /*
  37. Now let's optimize this query:
  38. SELECT * FROM test_table WHERE Data=1 AND (UserID=1 OR UserID=2)
  39.  
  40. Argument for get() will need to look like this:
  41. [
  42.     "and",
  43.     ["Data", "=", 1],
  44.     [
  45.         "or",
  46.         ["UserID", "=", 1],
  47.         ["UserID", "=", 2]
  48.     ]
  49. ]
  50.  
  51. Here's PHP code to do this query:
  52. */
  53.  
  54. $query_array = ["and", ["Data","=",1], ["or"]];
  55. $user_list   = [["id"=>1], ["id"=>2]];
  56.  
  57. foreach ($user_list as $user_info)
  58.     $query_array[2][] = ["UserID","=",$user_info["id"]];
  59.  
  60. $result = $db -> get("test_table", $query_array);
  61.  
  62.  
  63.  
  64.  
  65. /*
  66. With new DB.php you can use IN instead of OR:
  67. SELECT * FROM test_table WHERE Data=1 AND UserID IN (1,2)
  68.  
  69. Argument for get() will need to look like this:
  70. [
  71.     "and",
  72.     [ "Data",   "=",  1     ],
  73.     [ "UserID", "IN", [1,2] ]
  74. ]
  75.  
  76. Here's PHP code to do this query:
  77. */
  78.  
  79. $query_array = ["and", ["Data","=",1], ["UserID","IN",[]]];
  80. $user_list   = [["id"=>1], ["id"=>2]];
  81.  
  82. foreach ($user_list as $user_info)
  83.     $query_array[2][2][] = $user_info["id"];
  84.  
  85. $result = $db -> get("test_table", $query_array);
  86.  
  87.  
  88.  
  89. // Alternative version
  90.  
  91. $user_list = [["id"=>1], ["id"=>2]];
  92. $id_list   = [];
  93.  
  94. foreach ($user_list as $user_info)
  95.     $id_list[] = $user_info["id"];
  96.    
  97. $result = $db -> get("test_table", [ "and", ["Data","=",1], ["UserID","IN",$id_list] ]);
RAW Paste Data
Top