Change SQL query based on array elements-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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

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

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

CodePudding user response：

You can use the array_key_exists function to check if an array-key exists in your current array. (https://www.php.net/manual/en/function.array-key-exists.php)

Also, you can use a switch instead of all that if-checks (performance-wise better) (https://www.php.net/manual/en/control-structures.switch.php)