PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!
Hello, I just started learning PHP and as part of the learning process I am creating a listing page that displays data from DB and also filters the data depending on users selection in the html select controls. I got the displaying data part working but am not able to figure out how to pass the filter from html select into php function and perform the query. I took a suggestion from PHPFreaks and created the following code:
<?php
include 'config.php';
include 'opendb.php';
$result = mysql_query("select * from $table");
echo "db queried " . mysql_numrows($result) . " .\n
";
include 'query2.php';
?>
<script>
function setFilter(){
echo "in setfilter";
classVal = "";
locVal = "";
dowVal = "";
todVal = "";
sdVal = "";
filterStr = "";
//Get the dropdown values only when the value is something other than 'Please select'
if (document.form1.Classes.selectedIndex != 0){
classVal = document.form1.Classes.options[document.form1.Classes.selectedIndex].text;
filterStr = "course_title = " + classVal;
}
if (document.form1.Location.selectedIndex != 0){
locVal = document.form1.Location.options[document.form1.Location.selectedIndex].text;
filterStr = filterStr + " location = " + locVal;
}
if (document.form1.Dayofweek.selectedIndex != 0){
dowVal = document.form1.Dayofweek.options[document.form1.Dayofweek.selectedIndex].text;
filterStr = filterStr + " course_day = " + dowVal;
}
if (document.form1.TimeofDay.selectedIndex != 0){
todVal = document.form1.TimeofDay.options[document.form1.TimeofDay.selectedIndex].text;
filterStr = filterStr + " course_time = " + todVal;
}
if (document.form1.StartDate.selectedIndex != 0){
sdVal = document.form1.StartDate.options[document.form1.StartDate.selectedIndex].text;
filterStr = filterStr + " course_date = " + sdVal;
}
alert("filterstr is " + filterStr);
document.form1.filterVal.value = filterStr;
return true;
}
</script>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<TITLE>Course Listing</TITLE>
<BODY bgcolor = "#FFCC66"></body>
<form name="form1" action="query.php" method="post" onsubmit="setFilter();">
<table bgcolor = "#87CEFA" border = "1"cellspacing = "5" cellpadding = "10">
<th colspan = "5"bgcolor = "gray" >COURSE LIST</th>
<tr><th>Classes</th><th>Location</th><th>Day of the week</th><th>Time of the day</th><th>Start Date</th></tr>
<tr>
<td>
<select name="Classes">
<option value="Select">SELECT</option>
<option value="Simple Steps">Simple Steps</option>
<option value="My Business Action Plan">My Business Action Plan</option>
</select>
</td>
................
</tr>
</br>
<?php
while($row = mysql_fetch_row($result))
{
echo "<tr><td> $row[1] </td>";
echo "<td> $row[0] </td>";
echo "<td> $row[2] </td>";
echo "<td> $row[4] </td>";
echo "<td> $row[3] </td>";
echo "<td> </td></tr>";
}
?>
<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<?php
include 'closedb.php';
?>
</table>
<input type="hidden" name="filterVal" value=""/>
<input type="submit" name="submit" value="Search"/>
</form>
</HTML>
****** query.php ***********
<?php
if(isset($_REQUEST['submit']))
{
$selected_value = $_REQUEST['filterVal'];
echo "Filter string value is ". $selected_value;
$result = mysql_query("select * from $table where " . $selected_value);
}
?>
Initially I display all the data and when user selects a course from Classes dropdown I need to show data only for the selected class. How do I pass this filter? I have multiple dropdowns that need to be used as a filter condition. What is the best way of applying these filters and querying data?
<?php
include 'config.php';
include 'opendb.php';
if (isset($_POST['Classes']))
$class_choice = $_POST['Classes'];
if (isset($_POST['Location']))
$loc_choice = $_POST['Location'];
if (isset($_POST['Dayofweek']))
$dow_choice = $_POST['Dayofweek'];
if (isset($_POST['TimeofDay']))
$tod_choice = $_POST['TimeofDay'];
if (isset($_POST['StartDate']))
$sd_choice = $_POST['StartDate'];
if (isset($_POST['filterVal'])){
echo "filterVal is ". $_POST['filterVal'];
$result = mysql_query("select * from $table where " . $_POST['filterVal']);
}
else
$result = mysql_query("select * from $table");
echo "db queried " . mysql_numrows($result) . " .\n";
?>
<script>
function setFilter(){
echo "in setfilter";
classVal = "";
locVal = "";
dowVal = "";
todVal = "";
sdVal = "";
filterStr = "";
//Get the dropdown values only when the value is something other than 'Please select'
if (document.form1.Classes.selectedIndex != 0){
classVal = document.form1.Classes.options[document.form1.Classes.selectedIndex].text;
filterStr = "course_title = " + classVal;
}
if (document.form1.Location.selectedIndex != 0){
locVal = document.form1.Location.options[document.form1.Location.selectedIndex].text;
filterStr = filterStr + " location = " + locVal;
}
if (document.form1.Dayofweek.selectedIndex != 0){
dowVal = document.form1.Dayofweek.options[document.form1.Dayofweek.selectedIndex].text;
filterStr = filterStr + " course_day = " + dowVal;
}
if (document.form1.TimeofDay.selectedIndex != 0){
todVal = document.form1.TimeofDay.options[document.form1.TimeofDay.selectedIndex].text;
filterStr = filterStr + " course_time = " + todVal;
}
if (document.form1.StartDate.selectedIndex != 0){
sdVal = document.form1.StartDate.options[document.form1.StartDate.selectedIndex].text;
filterStr = filterStr + " course_date = " + sdVal;
}
alert("filterstr in setFilter is " + filterStr);
document.form1.filterVal.value = filterStr;
return true;
}
</script>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<TITLE>Course Listing</TITLE>
<BODY bgcolor = "#FFCC66"></body>
<form name="form1" action="Showlisting-0715.php" method="post" onsubmit="setFilter();">
<table bgcolor = "#87CEFA" border = "1"cellspacing = "5" cellpadding = "10">
<th colspan = "5"bgcolor = "gray" >COURSE LIST</th>
<tr><th>Classes</th><th>Location</th><th>Day of the week</th><th>Time of the day</th><th>Start Date</th></tr>
<tr>
<td>
<select name="Classes" onchange="this.form.submit();">
<option value="Select" >SELECT</option>
<option value="Simple Steps" >Simple Steps</option>
<option value="My Business Action Plan" >My Business Action Plan</option>
</select>
</td>
.........................
</tr>
</br>
<?php
while($row = mysql_fetch_row($result))
{
echo "<tr><td> $row[1] </td>";
echo "<td> $row[0] </td>";
echo "<td> $row[2] </td>";
echo "<td> $row[4] </td>";
echo "<td> $row[3] </td>";
echo "<td> </td></tr>";
}
?>
<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<?php
include 'closedb.php';
?>
</table>
<input type="hidden" name="filterVal" value=""/>
<input type="submit" name="submit" value="Search"/>
</form>
</HTML>
When my page loads first I see all the data when I change the 'Classes' dropdown nothing happens and when I click on the Search button, I get these errors:
0Connected and selected db
filterVal is
Warning: mysql_query() [http://www.mysql.com/doc]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 in C:\Program Files\Apache Group\Apache2\htdocs\Showlisting-0715.php on line 19
Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource in C:\Program Files\Apache Group\Apache2\htdocs\Showlisting-0715.php on line 23
db queried .
Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in C:\Program Files\Apache Group\Apache2\htdocs\Showlisting-0715.php on line 146
So it seems like the java script is never being executed because filter string is not set.
Am I missing something else?