I have a form where people can search the database for four values: Location, Period, Day and Service. I always do not get the results that I want.
If I use AND, people need to fill in everything. If I use OR I get the complete database. I want to be able to search the database for those one to 4 things. Is there a way how I can do this?
Is there maybe a way to check which fields are filled in, and that the query is automatically changed with the filled in fields?
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<title>Zoeken</title>
</head>
<body>
<p><a href="new.php"><img src="add.png" width="20px" height="20px"/></a> | <a href="search.php"><img src="search.png" width="20px" height="20px"/></a> | <a href="search_lijnen.php"><img src="number.png" width="20px" height="20px"/></a> </p>
<form action="" method="post">
<div>
<table>
<tr><td><strong>Locatie: </strong></td><td><input type="text" name="Locatie" value="" /></td> </tr>
<tr><td><strong>Periode: </strong></td><td><input type="text" name="Periode" value="" /></td> </tr>
<tr><td><strong>Dag: </strong></td><td><input type="text" name="Dag" value="" /></td> </tr>
<tr><td><strong>Dienst: </strong></td> <td><input type="text" name="Dienst" value="" /></td></tr>
<tr><td></td><td><input type="submit" name="zoeken" value="Zoeken"></td></tr>
</table>
</div>
</form>
<p><a href="new.php"><img src="add.png" width="20px" height="20px"/></a> | <a href="search.php"><img src="search.png" width="20px" height="20px"/></a> | <a href="search_lijnen.php"><img src="number.png" width="20px" height="20px"/></a> </p>
</body>
</html>
<?php
if (isset($_POST['zoeken']))
{
include('connect-db.php');
$Locatie = $_POST['Locatie'];
$Periode = $_POST['Periode'];
$Dag = $_POST['Dag'];
$Dienst = $_POST['Dienst'];
// get results from database
$result = mysql_query("SELECT * FROM WMC_DeLijn WHERE Locatie='$Locatie' ANY Periode='$Periode' ANY Dag='$Dag'ANY Dienst='$Dienst' ")
or die(mysql_error());
// display data in table
echo "<h2>Resultaten:</h2><p>";
echo "<table border='1' cellpadding='10'>";
echo "<table><tr><th>ID</th><th>Locatie</th><th>Periode</th><th>Dag</th><th>Dienst</th><th>Delen</th><th>Geleed</th><th>Start 1</th><th>Eind 1</th><th>Start 2</th><th>Eind 2</th><th>Lijnen</th></tr>";
// loop through results of database query, displaying them in the table
while($row = mysql_fetch_array( $result )) {
// echo out the contents of each row into a table
echo "<tr>";
echo '<td align="center">' . $row['id'] . '</td>';
echo '<td align="center">' . $row['Locatie'] . '</td>';
echo '<td align="center">' . $row['Periode'] . '</td>';
echo '<td align="center">' . $row['Dag'] . '</td>';
echo '<td align="center">' . $row['Dienst'] . '</td>';
echo '<td align="center">' . $row['Delen'] . '</td>';
echo '<td align="center">' . $row['Geleed'] . '</td>';
echo '<td align="center">' . $row['Start1'] . '</td>';
echo '<td align="center">' . $row['Eind1'] . '</td>';
echo '<td align="center">' . $row['Start2'] . '</td>';
echo '<td align="center">' . $row['Eind2'] . '</td>';
echo '<td align="center">' . $row['Lijnen'] . '</td>';
//Link to edit record
echo '<td align="center"><a href="edit.php?id=' . $row['id'] . '"><img src="edit.png" width="20px" height="20px"/></a></td>';
// Link to delete record
echo '<td align="center"><a href="delete.php?id=' . $row['id'] . '"><img src="delete.png" width="20px" height="20px"/></a></td>';
//Link to Add Event to Google Calendar
echo '<td align="center"><a href="Add_Event.php?id=' . $row['id'] . '"><img src="proceed.png" width="20px" height="20px"/></a></td>';
echo "</tr>";
}}
// close table>
echo "</table>";
?>
You can either build the query string dynamically, only adding WHERE clause statements if the parameter is not falsy, or add conditions in the SQL itself like so: WHERE (col = ? OR '' = ?) AND (col2 = ? OR '' = ?).
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments