Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

MYSQL and PHP filter from a dropdown list

Status
Not open for further replies.
mletendre

mletendre

Technical User
Dec 30, 2008
158
0
0
US
I am using dreamweaver CS5, and I have a dynamic table repeating to show all the records, which is great. I now want to add a drop down box above it to act as a filter so one of our users can select an option from that dropdown to filter the entire table to just what they need.

I added the drop down and a data set and put the DISTINCT so it only shows each option once. That is fine, however now that I try to filter the table, I am running into an issue.

I added this to the SQL in the record set where the table is:

WHERE OEM LIKE 'varOEM'

and this to the variables section:
variable name varOEM
default value of -1
run-time value of $_POST['selOEM']

Any suggestions where to look?

below is more of the code:
<label for="OEM">OEM</label>
<select name="selOEM" id="selOEM" onchange="form1.submit()">
<option value="%">All OEM</option>
<?php
do {
?>
<option value="<?php echo $row_OEMList['OEM']?>"><?php echo $row_OEMList['OEM']?></option>
<?php
} while ($row_OEMList = mysql_fetch_assoc($OEMList));
$rows = mysql_num_rows($OEMList);
if($rows > 0) {
mysql_data_seek($OEMList, 0);
$row_OEMList = mysql_fetch_assoc($OEMList);
}
?>
</select>
submit
<input type="submit" name="submit" id="submit" value="Submit" />
<table border="1">
<tr>
<td>OEM</td>
<td>OEMItemNumber</td>
<td>ShortDescription</td>
<td>ProductType</td>
<td>MachineType</td>
<td>color</td>
<td>PageYield</td>
</tr>
<?php do { ?>
<tr>
<td><?php echo $row_tonerlist['OEM']; ?></td>
<td><?php echo $row_tonerlist['OEMItemNumber']; ?></td>
<td><?php echo $row_tonerlist['ShortDescription']; ?></td>
<td><?php echo $row_tonerlist['ProductType']; ?></td>
<td><?php echo $row_tonerlist['MachineType']; ?></td>
<td><?php echo $row_tonerlist['color']; ?></td>
<td><?php echo $row_tonerlist['PageYield']; ?></td>
</tr>
<?php } while ($row_tonerlist = mysql_fetch_assoc($tonerlist)); ?>
</table>
 
Sort by date Sort by votes
Your lack of a form element would prevent any type of data ever reaching the $_POST array.

I'd look at exactly what your query ends up looking like to see if its correctly formed.

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Behind the Web, Tips and Tricks for Web Development.
 
Upvote 0 Downvote
hmmm Im pretty sure I put the form in there, and using the find feature I did see it is in there, but it isnt in what I posted since I didnt post the entire document, here is some of the part with the form tags

<body onload="bannerchanger('banner');">
<form id="form1" name="form1" method="post" action="">
<div id="top">
<div id="t1">
<div id="t3">
<div style="height:32px; text-align: right; padding-right: 15px; color: #FFFFFF;"><br />

</div>
</form>
<script type="text/javascript">
var MenuBar2 = new Spry.Widget.MenuBar("MenuBar2", {imgDown:"/SpryAssets/SpryMenuBarDownHover.gif", imgRight:"/SpryAssets/SpryMenuBarRightHover.gif"});
</script>
</body>
<!-- InstanceEnd --></html>
<?php
mysql_free_result($tonerlist);

mysql_free_result($OEMList);
?>
 
Upvote 0 Downvote
Your form is there, but has nothing to do with the other code.

Elements must reside inside the form, otherwise they are not submitted. Your form doesn't even have a submit button inside, only a div.

In other words the form must encompass both the dropdown you want the value from, and the submit button.



----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Behind the Web, Tips and Tricks for Web Development.
 
Upvote 0 Downvote
I see what you mean.

To take care of the submit button I added an Onchange element

><formOEM>
<select name="OEM" title=OEM onchange="formOEM.submit()""<?php echo $row_OEMList['OEM']; ?>">
<option value="%">All OEM</option>
<?php
do {
?>
<option value="<?php echo $row_OEMList['OEM']?>"><?php echo $row_OEMList['OEM']?></option>
<?php
} while ($row_OEMList = mysql_fetch_assoc($OEMList));
$rows = mysql_num_rows($OEMList);
if($rows > 0) {
mysql_data_seek($OEMList, 0);
$row_OEMList = mysql_fetch_assoc($OEMList);
}
?>
</select>
</formOEM>
 
Upvote 0 Downvote
Ok so I started from scratch, a page with NOTHING else on it to try to make sure it wasn't something else interfering.... still no dice. It works fine till I try to link the two (drop down and the dynamic table.

Here is the cofe from the SQL in the record set:

SELECT OEM, OEMItemNumber, ShortDescription, ProductType, MachineType, color, PageYield
FROM NSAToner
WHERE ProductType = 'Toner cartridge' and NSAToner.OEM LIKE varOEM
ORDER BY OEM ASC

Here is where I defined varOEM
(in Dreamweaver so it looks like a table)
Name varOEM
default -1
Runtime value: $_POST['selOEM']

Here is the html code:

<?php require_once('Connections/nsatoner.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}

$varOEM_TonerList = "-1";
if (isset($_POST['selOEM'])) {
$varOEM_TonerList = $_POST['selOEM'];
}
mysql_select_db($database_nsatoner, $nsatoner);
$query_TonerList = sprintf("SELECT OEM, OEMItemNumber, ShortDescription, ProductType, MachineType, color, PageYield FROM NSAToner WHERE ProductType = 'Toner cartridge' and NSAToner.OEM LIKE %s ORDER BY OEM ASC", GetSQLValueString($varOEM_TonerList, "int"));
$TonerList = mysql_query($query_TonerList, $nsatoner) or die(mysql_error());
$row_TonerList = mysql_fetch_assoc($TonerList);
$totalRows_TonerList = mysql_num_rows($TonerList);

mysql_select_db($database_nsatoner, $nsatoner);
$query_OEMlist = "SELECT DISTINCT OEM FROM NSAToner ORDER BY OEM ASC";
$OEMlist = mysql_query($query_OEMlist, $nsatoner) or die(mysql_error());
$row_OEMlist = mysql_fetch_assoc($OEMlist);
$totalRows_OEMlist = mysql_num_rows($OEMlist);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "<html xmlns="<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<p>&nbsp;</p>
<form id="formOEM" name="formOEM" method="post" action="">
<label for="selOEM">OEM</label>
<select name="selOEM" id="selOEM" onchange="formOEM.submit()">
<option value="%">All OEM </option>
<?php
do {
?>
<option value="<?php echo $row_OEMlist['OEM']?>"><?php echo $row_OEMlist['OEM']?></option>
<?php
} while ($row_OEMlist = mysql_fetch_assoc($OEMlist));
$rows = mysql_num_rows($OEMlist);
if($rows > 0) {
mysql_data_seek($OEMlist, 0);
$row_OEMlist = mysql_fetch_assoc($OEMlist);
}
?>
</select>
</form>
<p>&nbsp;</p>
<p>&nbsp;</p>
<table border="1" cellpadding="2">
<tr>
<td>OEM</td>
<td>OEMItemNumber</td>
<td>ShortDescription</td>
<td>ProductType</td>
<td>MachineType</td>
<td>color</td>
<td>PageYield</td>
</tr>
<?php do { ?>
<tr>
<td><?php echo $row_TonerList['OEM']; ?></td>
<td><?php echo $row_TonerList['OEMItemNumber']; ?></td>
<td><?php echo $row_TonerList['ShortDescription']; ?></td>
<td><?php echo $row_TonerList['ProductType']; ?></td>
<td><?php echo $row_TonerList['MachineType']; ?></td>
<td><?php echo $row_TonerList['color']; ?></td>
<td><?php echo $row_TonerList['PageYield']; ?></td>
</tr>
<?php } while ($row_TonerList = mysql_fetch_assoc($TonerList)); ?>
</table>
</body>
</html>
<?php
mysql_free_result($TonerList);

mysql_free_result($OEMlist);
?>
 
Upvote 0 Downvote
I'm assuming what happens is you get no table, correct? ITs just empty.

If so, then look at your query, what happens when there is nothing selected? You have a query that tries to match records to -1. I'm guessing there are no records with that value.

Try this:

Code: 
$varOEM_TonerList = "";
if (isset($_POST['selOEM'])) {
  $varOEM_TonerList = "and NSAToner.OEM LIKE " . $_POST['selOEM'];
}

$query_TonerList = sprintf("SELECT OEM, OEMItemNumber, ShortDescription, ProductType, MachineType, color, PageYield FROM NSAToner WHERE ProductType = 'Toner cartridge' %s ORDER BY OEM ASC", GetSQLValueString($varOEM_TonerList, "text"));
$TonerList = mysql_query($query_TonerList, $nsatoner) or die(mysql_error());
$row_TonerList = mysql_fetch_assoc($TonerList);
$totalRows_TonerList = mysql_num_rows($TonerList);








----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Behind the Web, Tips and Tricks for Web Development.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

EdLentz
  • Locked
  • Question
Need to compare two tables and list the differences
Replies
3
Views
103
EdLentz
EdLentz
lupidol
  • Locked
  • Question
Cannot create new table in mySQL 3
Replies
6
Views
136
lupidol
lupidol
lupidol
  • Locked
  • Question
mysql cannot add rows with &quot;UNION&quot; 1
Replies
5
Views
116
Chris Miller
Chris Miller
penguinspeaks
  • Locked
  • Question
Stored Procedures and triggers
Replies
9
Views
113
penguinspeaks
penguinspeaks
dylim
  • Locked
  • Question
Why MySQL 5.7 is still getting updates
Replies
3
Views
84
spamjim
spamjim

Part and Inventory Search

Sponsor

Back
Top