Change SQL query based on array elements

Question

I can't get any solution for my script. I'm trying to change my SQL query based on what is in the array. I have an jQuery AJAX call that calls a PHP script retrieving information from the database based on passed data through the AJAX call. I have 5 checkboxes with different values. On each checkbox click it stores a number from 1 to 5 into the array. When uncheck the box the number disappear from the array. This array is passing through the AJAX call on each checkbox click. The problem comes here - The array is successfully passing to the PHP script file, but I need based on what is inside the array to change my SQL query. When there are for example ('1', '5') in the array i want the SQL query to retrieve information with a condition "WHERE column = "1" AND column = '5'", but I can't create it logically. That's what I've made so far:

$sql = "SELECT * 
        FROM `reviews` 
        WHERE `review_website_id`='" . $getQfetch['website_id'] . "' ";

if(in_array("1", $_GET['stars'])) {
    $sql .= " AND `review_stars` = '1'";
}
if(in_array("2", $_GET['stars'])) {
    $sql .= " AND `review_stars` = '2'";
}
if(in_array("3", $_GET['stars'])) {
    $sql .= " AND `review_stars` = '3'";
}
if(in_array("4", $_GET['stars'])) {
    $sql .= " AND `review_stars` = '4'";
}
if(in_array("5", $_GET['stars'])) {
    $sql .= " AND `review_stars` = '5'";
}

$sql .= " ORDER BY CAST(review_id AS SIGNED) DESC";

The $_GET['stars'] parameter is the array containing the values from 1-5. I need the SQL query to retrieve information either the array contain values 1, 5.. or 1, 2, 3, 4 but when there are multiple checkbox selections the query goes wrong.

Your script is open to [SQL Injection Attack](http://stackoverflow.com/questions/60174). Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187) You should alway use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's instead of concatenating user provided values into the query. Never trust ANY user input! — RiggsFolly, Sep 21 '21 at 14:39

score 1 · Answer 1 · answered Sep 21 '21 at 14:37

Your query builds this:

... AND review_stars = 1 AND review_stars = 2 AND review_stars = 3 ...

Obviously, review_stars can't be both 1 AND 2 at the same time.

You want this:

... AND (review_stars = 1 OR review_stars = 2 OR review_stars = 3) ...

Or, equivalently:

... AND review_stars IN (1, 2, 3) ...

Also note, your code is likely vulnerable to SQL injection attacks. Instead of building queries with string concatenation, always use prepared statements with bound parameters. See this page and this post for some good examples.

score 0 · Answer 2 · answered Sep 21 '21 at 14:38

0

You can try changing your array to a comma delimited string.

$values = implode(', ', $_GET['stars']);

$sql .= " AND review_stars in ($values)";

answered Sep 21 '21 at 14:38

Shane Rimmer

31
2

Change SQL query based on array elements

2 Answers2