Home PHP mysql query issue when filtering from array and variables
Reply: 1

PHP mysql query issue when filtering from array and variables

terry25110
1#
terry25110 Published in 2017-12-07 19:12:29Z

On my webpage (html, php), I'm trying to get it such that users may select multiple checkboxes, each checkbox effectively filters what results the users see. Info is pulled from the database (MySQL) based on the values in different columns. As shown below, one column is Joint_1, another column is Position.

Effective code that WORKS for filtering (very static, not practical to use obviously) is this:

$sql = "SELECT * FROM `Table_Name` WHERE (Joint_1=\'region1\'  OR  
                                          Joint_1=\'region2\'  OR  
                                          Joint_1=\'region3\' OR
                                          Joint_1=\'region4\') AND
                                         (Position=\'position1\' OR
                                          Position=\'position2\' OR
                                          Position=\'position3\')";
$result = $conn->query($sql);

if($result->num_rows > 0) {
   while($row = $result->fetch_assoc()) {
      echo $row["Common_Name1"] . "<br>";
   }
} else {
  echo "0 results";
  }

Below code is attempts at above code, but using arrays, which does NOT work.

$regions = 
array('region1', 'region2', 'region3', 'region4');
$position = array('position1', 'position2', 'position3');

$sql = "SELECT * FROM `Table_Name` WHERE (Joint_1=\'. $regions[0] .\'  OR  
                                          Joint_1=\'. $regions[1] .\'  OR  
                                          Joint_1=\'. $regions[2] .\' OR
                                          Joint_1=\'. $regions[3] .\') AND
                                         (Position=\'. $position[0] .\' OR
                          Position=\'. $position[0] .\' OR
                      Position=\'. $position[0] .\')"; 

Above code provides results of '0 results.'

I've attempted to perform this numerous times, with additional NON-FUNCTIONAL CODE also below (below attempting to filter based on only 1 column as I have obviously not mastered the code to approach filtering based on 2 columns).

$sqlregion = array();
foreach ($_POST['region'] as $reg) {
   $sqlreg[] = "'" . mysql_real_escape_string($reg) . "'";   
}
$sql = "SELECT * FROM 'Exercises' WHERE Joint_1 IN (" . implode(",", 
$sqlreg) . ");";

$result=$conn->query($sql);

if($result->num_rows > 0) {
   while($row = $result->fetch_assoc()) {
     echo $row["Common_Name1"] . "<br>";
   } 
}

Any help is appreciated! Thanks.

miknik
2#
miknik Reply to 2017-12-07 20:57:41Z

You can construct this query from arrays, you can use the IN clause in mysql to specify multiple OR values

$regions = array('region1', 'region2', 'region3', 'region4');
$position = array('position1', 'position2', 'position3');
$regions = implode(",", $regions); // Converts array to string with comma as a separator
$position = implode(",", $position);
$sql = "SELECT * FROM `Table_Name` WHERE Joint_1 IN ($regions) AND Position IN ($position)";
echo $sql;

This gives you a query like this

SELECT * FROM Table_Name WHERE Joint_1 IN (region1,region2,region3,region4) AND Position IN (position1,position2,position3)

add in your own LIMIT GROUP BY or ORDER BY parameters to suit your needs

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.314303 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO