Select all if text box is empty
I have five textboxes which are associated with its own field from the my sql database. What I want to do is fetch data from the mysql database depending on what the user has entered in the text box. The problem is that if a text box is empty, it outputs no record as that the textbox is trying to post '' as a piece of data. How can I implement it so that if a textbox is empty, it will look for all (or any data) in that field. E.g if SessionID t开发者_JAVA技巧extbox is empty, then select all sessionID's. Below is my current coding:
<body>
<form action="exam_interface.php" method="post" name="sessionform"> <!-- This will post the form to its own page"-->
<p>Session ID: <input type="text" name="sessionid" /></p> <!-- Enter Session Id here-->
<p>Module Number: <input type="text" name="moduleid" /></p> <!-- Enter Module Id here-->
<p>Teacher Username: <input type="text" name="teacherid" /></p> <!-- Enter Teacher here-->
<p>Student Username: <input type="text" name="studentid" /></p> <!-- Enter User Id here-->
<p>Grade: <input type="text" name="grade" /></p> <!-- Enter Grade here-->
<p><input type="submit" value="Submit" /></p>
</form>
<?php
$username="u0867587";
$password="31may90";
$database="mobile_app";
$sessionid = $_POST['sessionid'];
$moduleid = $_POST['moduleid'];
$teacherid = $_POST['teacherid'];
$studentid = $_POST['studentid'];
$grade = $_POST['grade'];
mysql_connect('localhost',$username,$password);
@mysql_select_db($database) or die("Unable to select database");
$result = mysql_query("SELECT * FROM Module m INNER JOIN Session s ON m.ModuleId = s.ModuleId JOIN Grade_Report gr ON s.SessionId = gr.SessionId JOIN Student st ON gr.StudentId = st.StudentId WHERE gr.SessionId = '$sessionid' AND m.ModuleId = '$moduleid' AND s.TeacherId = '$teacherid' AND gr.StudentId = '$studentid' AND gr.Grade = '$grade'");
$num=mysql_numrows($result);
echo "<table border='1'>
<tr>
<th>Student Id</th>
<th>Forename</th>
<th>Session Id</th>
<th>Grade</th>
<th>Mark</th>
<th>Module</th>
<th>Teacher</th>
</tr>";
while ($row = mysql_fetch_array($result)){
echo "<tr>";
echo "<td>" . $row['StudentId'] . "</td>";
echo "<td>" . $row['Forename'] . "</td>";
echo "<td>" . $row['SessionId'] . "</td>";
echo "<td>" . $row['Grade'] . "</td>";
echo "<td>" . $row['Mark'] . "</td>";
echo "<td>" . $row['ModuleName'] . "</td>";
echo "<td>" . $row['TeacherId'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysql_close();
?>
Thank You
The simplest way would be to build the WHERE part of the query dynamically based on what has been submitted, this way if the field in question is empty, that part of the where statement wouldn't exist.
Take this example, that has two possible inputs $_POST['foo'] and $_POST['bar']:
<?php
//bind vars to be used in filtering - if blank they are false
$foo = (!empty($_POST['foo']) ? mysql_real_escape_string($_POST['foo']) : false);
$bar = (!empty($_POST['bar']) ? mysql_real_escape_string($_POST['bar']) : false);
//base query: 1=1 will always be true and not filter anything
$query = "SELECT * FROM `my_table` WHERE 1=1"
if($foo){
$query.=" AND `foo` = ".$foo);
}
if($bar)
{
$query.=" AND `bar` = ".$bar;
}
$r = mysql_query($query);
?>
if you want a lazy solution so that you don't create the query one field at a time you can just add this:
foreach ($_POST as $key=>$value)
{
if (empty($value))
{
$_POST[$key] = '%';
}
}
before the block where you get the data from post and replace every equal sign (=) after the where in your query with the keywork LIKE
but the recommended solution would be the one that picus gave you with the note that you should filter the user input and escape it when you use it in the query
You want to Short circuit the logic if the entered value is empty e.g:
when you build your where for each predicate, rather than
AND m.ModuleId = '$moduleid'
use
AND ('$moduleid' = '' OR m.ModuleId = '$moduleid')
this should short circuit the column = value check
Build your query dynamically, and do not add empty variables to the WHERE clause of the query:
<?php
// Map database attributes to POST variables
$postVars = array(
'gr.SessionId' => 'sessionid',
'm.ModuleId' => 'moduleid',
's.TeacherId' => 'teacherid',
'gr.StudentId' => 'studentid',
'gr.Grade' => 'grade'
);
// Determine the parts of the WHERE clause
$whereParts = array();
foreach ($postVars as $attributeName => $postVar) {
if (isset($_POST[$postVar]) && !empty($_POST[$postVar])) {
$whereParts[] = $attributeName . " = " . mysql_real_escape_string($_POST[$postVar]);
}
}
// Create the WHERE clause if there are parts
$whereClause = "";
if (! empty($whereParts)) {
$whereClause = " WHERE " . implode(" AND ", $whereParts);
}
// Construct the complete query
$query = "SELECT * FROM table" . $whereClause;
EDIT:
Added a mapping from database attribute names to POST variable names, because these are not equal in the provided code in the question.
加载中,请稍侯......
精彩评论