php - Search Form with One or More (Multiple) Parameters
Get the solution ↓↓↓
Solution:
This is easiest to do when using PDO, not mysqli, as your database API.
Build theWHERE clause dynamically. My recommended approach is to push each condition onto an array, and then useimplode() to concatenate all the conditions, connecting them withAND orOR as is your preference.
$wheres = array();
$params = array();
if (!empty($_GET['id'])) {
$wheres[] = 'a.uid = :uid';
$params[':uid'] = $_GET['id'];
}
if (!empty($_GET['major'])) {
$wheres[] = 'a.major = :major';
$params[':major'] = $_GET['major'];
}
if (!empty($_GET['name'])) {
$wheres[] = 'b.name LIKE :name';
$params[':name'] = '%'.$_GET['name'].'%';
}
// And so on for all parameters
$sql = "SELECT *
FROM user_details AS a
JOIN user AS b ON a.uid = b.id";
if (!empty($wheres)) {
$sql .= " WHERE " . implode(' AND ', $wheres);
}
$stmt = $db->prepare($sql);
$stmt->execute($params);
Then display the results as in your original code.
while ($student = $stmt->fetch()) {
...
}
Answer
Solution:
If you aren't going to change anything in the database - you are just selecting - go ahead and use GET instead of POST. The advantage of this is that it is going to allow you to save the URL as your search string. You can also refresh the search without getting the resubmit post alert. You just want to make sure that you parameterize your values before you send them to the database. I would normally send those values through sanitize functions, such as a regex that makes sure you only have letters if you expect letters, or a numbers if you expected numbers.
On the same page (all search): (I am just going to outline this for you.)
<form action="<?= $_SERVER["REQUEST_URI"]; ?>" method="GET">
<input name="major" value="<?= $_GET["major"]; ?>" />
<select name="college">
<option value="1" <?PHP if( $_GET["college"] == 1 ) echo 'selected="true"'; ?>>Business</option>
</select>
</form>
<?PHP
if( ! empty( $_GET ) ){
if (isset($_GET['major'])) {
$wheres[] = 'a.major = :major';
$params[':major'] = $_GET['major'];
}
if (isset($_GET['name'])) {
$wheres[] = 'b.name LIKE :name';
$params[':name'] = '%'.$_GET['name'].'%';
}
// And so on for all parameters
$sql = "SELECT *
FROM user_details AS a
JOIN user AS b ON a.uid = b.id";
if (!empty($wheres)) {
$sql .= " WHERE " . implode(' AND ', $wheres);
}
$stmt = $db->prepare($sql);
$stmt->execute($params);
}
?>
Now you can display your data.
edit: I wrote the other half of the answer, and then he wrote the 2nd half, so I just incorporated it...
Also, the next level of sophistication in this would be to take the PHP out of the search file and to put it into another file. When you press the search button in your form, you'd use AJAX to call the PHP elements. Then the PHP file would return the results via Ajax. You could return either the HTML preformatted, or JSON and let something like JQuery display it for you.
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: uncaught mysqli_sql_exception
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.