Advertisement
Faguss

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

Jun 16th, 2017
930
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PHP 2.10 KB | None | 0 0
  1. #DB.php     https://pastebin.com/Jp2EbDTw
  2. #Forum      https://userspice.com/forums/topic/db-php-patch-error-handling-flexible-where-clauses-fix-bug/
  3. #example 1  https://pastebin.com/XmJR0Jk5
  4.  
  5. #I've used examples from https://www.w3schools.com/sql/sql_between.asp as a benchmark
  6. #SQL        https://pastebin.com/RKe1qHQT
  7.  
  8. //SELECT * FROM Customers WHERE CustomerName LIKE 'a%'
  9. $result = $db -> get("Customers", ["CustomerName","LIKE","a%"]);
  10.  
  11.  
  12.  
  13. //SELECT * FROM Persons WHERE Address IS NULL
  14. $result = $db -> get("Persons", ["Address","IS NULL"]);
  15.  
  16. //SELECT * FROM Persons WHERE Address IS NOT NULL
  17. $result = $db -> get("Persons", ["Address","IS NOT NULL"]);
  18.  
  19.  
  20.  
  21. //SELECT * FROM Products WHERE Price BETWEEN 10 AND 20
  22. $result = $db -> get("Products", ["Price","BETWEEN",10,20]);
  23.  
  24. //SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK');
  25. $result = $db -> get("Customers", ["Country","IN",["Germany","France","UK"]]);
  26.  
  27.  
  28.  
  29. //SELECT * FROM Customers WHERE Country IN (SELECT Country FROM Suppliers)
  30. $result = $db -> get("Customers", ["Country", "IN SELECT", "Suppliers", "Country"]);
  31.  
  32. //SELECT * FROM Products WHERE (Price BETWEEN 10 AND 20) AND NOT CategoryID IN (2,3,4)
  33. $result = $db -> get("Products", [ "AND",     ["Price","BETWEEN",10,20], ["NOT CategoryID","IN",[2,3,4]] ]);
  34. $result = $db -> get("Products", [ "AND NOT", ["Price","BETWEEN",10,20], [    "CategoryID","IN",[2,3,4]] ]);
  35.  
  36.  
  37.  
  38. //SELECT * FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity >= 10)
  39. $result = $db -> get("Products", [ "ProductID", "=", "ANY", "OrderDetails", "ProductID", ["Quantity",">=","10"] ]);
  40.  
  41. //SELECT * FROM Products WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10)
  42. $result = $db -> get("Products", [ "ProductID", "=", "ALL", "OrderDetails", "ProductID", ["Quantity", "=","10"] ]);
  43.  
  44.  
  45.  
  46. //SELECT * FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE SupplierId = Suppliers.supplierId AND Price < 20)
  47. $result = $db -> get("Suppliers", [ "EXISTS", "Products", "ProductName", ["SupplierId=Suppliers.supplierId AND Price","<",20] ]);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement