Sometimes my script receives a $_POST["is_searchfield"] request from a form. Then I want to use this variable as a part of a "Where" clause of a MySQL query. Otherwise this part of the "Where" clause shouldn't afflict the rest of the query.
For the query I use a prepared statement.
If the $_POST["is_searchfield"] has a value the query works as expected, but if no variable is sent the whole query doesn't give me any results.
How can I achieve that an empty variable doesn't destroy the whole query and only this certain part of the "where" clause will be ignored. Also the prepared statement should continue to work.
I tried to check if the variable is set with !empty but it doesn't change this behavoiur.
$conn = new mysqli("localhost", "xxxx", "xxxx", "xxxx");
$conn->set_charset("utf8");
// Variables to bind
$classid = "4";
if(!empty($_POST["is_searchfield"]))
{
$searchfield = $_POST["is_searchfield"];
}
else {
$searchfield= "";
}
$fileid = "10";
$sqlitemsparse = "SELECT * FROM itemSparse INNER JOIN item ON item.id = itemSparse.id";
$sqlitemsparse .= " WHERE item.ClassID = ?";
$sqlitemsparse .= " AND itemSparse.Display_lang = ?";
$sqlitemsparse .= " AND itemSparse.fileid = ?";
$sqlitemsparse2 = " LIMIT 0, 10";
$stmt = $conn->prepare($sqlitemsparse . $sqlitemsparse2);
$stmt->bind_param('sss', $classid, $searchfield, $fileid);
$stmt->execute();
$resultitemsparse = $stmt->get_result();
$rowsitemsparse = $resultitemsparse->fetch_all(MYSQLI_ASSOC);
The idea is to change the query to match the data provided
$conn = new mysqli("localhost", "xxxx", "xxxx", "xxxx");
$conn->set_charset("utf8");
// Variables to bind
$classid = "4";
$fileid = "10";
if(!empty($_POST["is_searchfield"]))
{
$sqlitemsparse = "SELECT *
FROM itemSparse
INNER JOIN item ON item.id = itemSparse.id
WHERE item.ClassID = ?
AND itemSparse.Display_lang = ?
AND itemSparse.fileid = ?
LIMIT 0, 10";
$stmt = $conn->prepare($sqlitemsparse);
$stmt->bind_param('sss', $classid, $_POST["is_searchfield"], $fileid);
} else {
$sqlitemsparse = "SELECT *
FROM itemSparse
INNER JOIN item ON item.id = itemSparse.id
WHERE item.ClassID = ?
AND itemSparse.fileid = ?
LIMIT 0, 10";
$stmt = $conn->prepare($sqlitemsparse);
$stmt->bind_param('ss', $classid, $fileid);
}
$stmt->execute();
$resultitemsparse = $stmt->get_result();
$rowsitemsparse = $resultitemsparse->fetch_all(MYSQLI_ASSOC);