MySQLi prepared Conditional Statements

Question

I have four possible variables, only one of them is required. My question is, how do I prepare a statement / construct the sql query if there parts of the query that may or not show up.

Something like this, I guess:

sql = "SELECT * FROM dogs WHERE name = ?"
if(isset($dogid)) {
sql .= "AND WHERE id = ?";
}

}
if(isset($dogcolour)) {
sql .= "AND WHERE colour = ?";
}

My brain is totally broken, and I figure there has to be a better way to prepare it than using something like $dogID = "true"; in each if, and then binding it at the end with a bunch of if statements.

Answer 1

Well I would define some empty value, and then check everything in database.

$sql = "SELECT * FROM dogs WHERE (IF(? = 0, 1 = 1, name = ?)) AND (IF(? = 0, 1 = 1, colour = ?))"

this solution has one drawback, you need to pass all parameters twice:

$statement->bind_param('ssss', $name, $name, $colour, $colour);

if name or $colour variable is 0 then 1 = 1 is executed in where statement (witch is always true). There should be posibility to pass NULL value to mysqli, however for me it doesn't work, if it work then could be possible to simplify query.

$sql = "SELECT * FROM dogs WHERE name = (IFNULL(?, name)) AND colour = (IFNULL(?, colour))"