Advertisement
brilliantmojo

SQLDataMangement-notPrepared

Jun 23rd, 2020
217
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PHP 2.57 KB | None | 0 0
  1. <?php
  2.     // Inputted filters' values
  3.     $ItemNumber = $_POST["ItemSearch"];
  4.  
  5.     $MinWT = $_POST["minimum_wt"];
  6.     $MaxWT = $_POST["maximum_wt"];
  7.  
  8.     $Shape = $_POST["shape"];
  9.  
  10.     $Color = $_POST["color"];
  11.  
  12.     $Enhancement = $_POST["enhancement"];
  13.  
  14.     $Matching = $_POST["matching"];
  15.  
  16.     /* SELECT ALL (with the alias of 'inv' [to not mix up the following request])
  17.     FROM the 'stones' table WHERE 'wt' is greater than or equal to 2.5,
  18.     then COUNT ALL and use the window function; OVER() to partition the COUNT AS a column
  19.     called 'totalrows' */
  20.     $SQL = "
  21.        
  22.        SELECT inv.*, COUNT(*) OVER() AS totalrows FROM stones inv WHERE wt >= 2.5
  23.  
  24.    ";
  25.  
  26.     // Filters
  27.     if(isset($ItemNumber)) {
  28.  
  29.         $SQL .= "
  30.        
  31.            AND number LIKE '".$ItemNumber."%'
  32.        
  33.        ";
  34.        
  35.     }
  36.  
  37.     if(isset($MinWT, $MaxWT) && !empty($MinWT) && !empty($MaxWT)) {
  38.            
  39.         $SQL .= "
  40.        
  41.            AND wt BETWEEN '".$MinWT."' AND '".$MaxWT."'
  42.        
  43.        ";
  44.        
  45.     }
  46.    
  47.     if(isset($Shape)) {
  48.        
  49.         $ShapeFilter = implode("','", $Shape);
  50.        
  51.         $SQL .= "
  52.        
  53.            AND stoneshape IN('".$ShapeFilter."')
  54.            
  55.        ";
  56.     }
  57.    
  58.     if(isset($Color)) {
  59.        
  60.         $ColorFilter = implode("','", $Color);
  61.        
  62.         $SQL .= "
  63.        
  64.            AND stonecolor IN('".$ColorFilter."')
  65.            
  66.        ";
  67.                
  68.     }
  69.    
  70.     if(isset($Enhancement)) {
  71.        
  72.         $EnhancementFilter = implode("','", $Enhancement);
  73.        
  74.         $SQL .= "
  75.        
  76.            AND enhcode IN('".$EnhancementFilter."')
  77.            
  78.        ";
  79.                    
  80.     }
  81.    
  82.     if(isset($Matching)) {
  83.        
  84.         $MatchingFilter = implode("','", $Matching);
  85.        
  86.         $SQL .= "
  87.        
  88.            AND pair IN('".$MatchingFilter."')
  89.            
  90.         "; 
  91.                    
  92.     }
  93.    
  94.     // Pagination
  95.     $PageNo = $_REQUEST['PageNo']; // Get page number
  96.  
  97.     $Limit = 25; // Maximum iterations per page
  98.     $Offset = ($PageNo - 1) * $Limit; // Current page times the amount per page
  99.    
  100.     // $Offset and $Limit are declared in their respective variables
  101.     $SQL .= "
  102.  
  103.        ORDER BY wt ASC LIMIT $Offset, $Limit;
  104.        
  105.    ";
  106.  
  107. ?>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement