How to: Input of multiple selections from the same table meet WHERE

[lordofthemorning]

I will try to explain what I’m doing. I have a few user input select boxes. 3 of those can be used to select a “skill”.

My intention is that each of those narrows down the array of “PET_ID”.
But currently they do not.
At first there was only 1 selectbox for the “skill” which produces “$selected_val2”. I created 2 more: “$selected_val7” and “$selected_val8”.

What happens at the moment is that a user can use either 1 of the select boxes and the query works. But if a user uses 2 or 3 select boxes, the query produces: “no result”.

So obviously I am not telling my query to do what I want
And that is where I am stuck: What function, order, can I use to make my query find the PET_ID that meet the input that meets “$selected_val2” AND “$selected_val7” AND “$selected_val8” ? from the same table skills.skillname? Any of the 3 can be empty at all times. If all 3 are empty, then all skills are right ($sql = $sql . “elements.elementname != ?”;).

Further challenge: at the end all variables are bound. So at all times i’d like all values to be present, if that is possible at all.

$sql = "
SELECT
	DISTINCT pets.PET_ID
FROM pets
	JOIN petskill ON pets.PET_ID=petskill.Pet_ID
	JOIN skills ON petskill.Skill_ID=skills.SKILL_ID
	JOIN petlocations ON petlocations.PET_ID=pets.PET_ID
	JOIN locations ON petlocations.LOC_ID=locations.LOC_ID
	JOIN elements ON pets.element_id=elements.element_id 
	JOIN elementgrowth ON elements.element_id=elementgrowth.element_id
WHERE ";

if (empty($selected_val1)) {
    $selected_val1 = '';
    $sql = $sql . "elements.elementname != ?";
} else {
    $sql = $sql . "elements.elementname = ?";
}

$sql = $sql . " AND ";

if (empty($selected_val2)) {
    $selected_val2 = '';
    $sql = $sql . "skills.skillname != ?";
} else {
    $sql = $sql . "skills.skillname = ?";
}

$sql = $sql . " AND ";

if (empty($selected_val7)) {
    $selected_val7 = '';
    $sql = $sql . "skills.skillname != ?";
} else {
    $sql = $sql . "skills.skillname = ?";
}
$sql = $sql . " AND ";

if (empty($selected_val8)) {
    $selected_val8 = '';
    $sql = $sql . "skills.skillname != ?";
} else {
    $sql = $sql . "skills.skillname = ?";
}

//etcetera removed the rest because it;s not relevant to the question//

$sql = $sql . " GROUP BY pets.PET_ID";

$stmt = mysqli_prepare($conn, $sql);

mysqli_stmt_bind_param($stmt, "ssssssss", $selected_val1, $selected_val2, $selected_val7, $selected_val8, $selected_val3, $selected_val4, $selected_val5, $boundSelected_val6);

[phdr]

The problem is because a column only holds a single value at a time, the WHERE clause ends up being false. For example, with two skills selected -

WHERE elements.elementname != ? AND skills.skillname = ? AND skills.skillname = ? AND skills.skillname != ?

If a stored skill id corresponds with ‘skill1’ and the inputs are ‘skill1’ and ‘skill2’, this becomes -
WHERE elements.elementname != ‘’ AND skills.skillname = ‘skill1’ AND skills.skillname = ‘skill2’ AND skills.skillname != ‘’

Which is TRUE AND FALSE AND TRUE, which is FALSE.

So, the question becomes, do you want to match data with ANY of the selected skills, in which case you would use OR between the terms (which can be simplified by using an IN() comparison), or do you want to match data that have ALL the selected skills, in which case you would still use OR betwen the terms in the WHERE clause, but you would also use HAVING COUNT(*) = x (x is the number of skills to match 1,2,3.)

You also need to simplify the logic that’s building the sql query statement, by adding each where term to an array, then simply implode() the array using the ’ AND ’ keyword and only add where terms that are being used (if you have used the != logic so that you always supply the same number of bound inputs, you won’t have this problem if you switch to the much simpler PDO extension, as was suggested by two different forum members in your previous thread.)