Help with multiple fields form and queries

EllenM1

I am trying to create a search page for some learning resources as shown in
LINK

I've figured out (sort of) how to use form variables to filter the list - what I have so far is here:

and the code for it is shown below. The script works only partially. Is there a better way to go about this?

Thanks,

Ellen

<?php
mysql_select_db($database_Competencies, $Competencies);
$query_Classifications = "SELECT * FROM Classifications";
$Classifications = mysql_query($query_Classifications, $Competencies) or die(mysql_error());
$row_Classifications = mysql_fetch_assoc($Classifications);
$totalRows_Classifications = mysql_num_rows($Classifications);

$varTargetAudience_Resources = "%";
if (isset($HTTP_POST_VARS['selectTargetAudience'])) {
  $varTargetAudience_Resources = (get_magic_quotes_gpc()) ? $HTTP_POST_VARS['selectTargetAudience'] : addslashes($HTTP_POST_VARS['selectTargetAudience']);
}
$varClassification_Resources = "%";
if (isset($HTTP_POST_VARS['selectClassification'])) {
  $varClassification_Resources = (get_magic_quotes_gpc()) ? $HTTP_POST_VARS['selectClassification'] : addslashes($HTTP_POST_VARS['selectClassification']);
}
mysql_select_db($database_Competencies, $Competencies);
$query_Resources = sprintf("SELECT * FROM Resources WHERE Classification = '%s' AND TargetAudience = '%s' ORDER BY ResourceTitle ASC ", $varClassification_Resources,$varTargetAudience_Resources);
$Resources = mysql_query($query_Resources, $Competencies) or die(mysql_error());
$row_Resources = mysql_fetch_assoc($Resources);
$totalRows_Resources = mysql_num_rows($Resources);

mysql_select_db($database_Competencies, $Competencies);
$query_TargetAudience = "SELECT * FROM TargetAudience";
$TargetAudience = mysql_query($query_TargetAudience, $Competencies) or die(mysql_error());
$row_TargetAudience = mysql_fetch_assoc($TargetAudience);
$totalRows_TargetAudience = mysql_num_rows($TargetAudience);
?>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<form name="form1" method="post" action="">
  <select name="selectClassification" size="5" id="selectClassification">
    <?php
do {
?>
    <option value="<?php echo $row_Classifications['ClassificationID']?>"<?php if (!(strcmp($row_Classifications['ClassificationID'], "%"))) {echo "SELECTED";} ?>><?php echo $row_Classifications['ClassificationName']?></option>
    <?php
} while ($row_Classifications = mysql_fetch_assoc($Classifications));
  $rows = mysql_num_rows($Classifications);
  if($rows > 0) {
      mysql_data_seek($Classifications, 0);
	  $row_Classifications = mysql_fetch_assoc($Classifications);
  }
?>
  </select>
  <select name="selectTargetAudience" id="selectTargetAudience">
    <?php
do {
?>
    <option value="<?php echo $row_TargetAudience['TargetAudienceId']?>"<?php if (!(strcmp($row_TargetAudience['TargetAudienceId'], "%"))) {echo "SELECTED";} ?>><?php echo $row_TargetAudience['TargetAudienceName']?></option>
    <?php
} while ($row_TargetAudience = mysql_fetch_assoc($TargetAudience));
  $rows = mysql_num_rows($TargetAudience);
  if($rows > 0) {
      mysql_data_seek($TargetAudience, 0);
	  $row_TargetAudience = mysql_fetch_assoc($TargetAudience);
  }
?>
  </select>
  <input type="submit" name="Submit" value="Submit">
</form>
<p>&nbsp;</p>
<?php do { ?>
<p>&nbsp;<?php echo $row_Resources['ResourceTitle']; ?>| <?php echo $row_Resources['Creator']; ?><br>
  <?php echo $row_Resources['Description']; ?></p>
<?php } while ($row_Resources = mysql_fetch_assoc($Resources)); ?>
</body>
</html>
<?php
mysql_free_result($Classifications);

mysql_free_result($Resources);

mysql_free_result($TargetAudience);
?>

OhLordy

<?
mysql_select_db($database_Competencies, $Competencies); 
$query_Classifications = "SELECT * FROM Classifications"; 
$Classifications = mysql_query($query_Classifications, $Competencies) or die(mysql_error());

$varTargetAudience_Resources="%";
if (isset($_POST['selectTargetAudience'])) { 
  $varTargetAudience_Resources = (get_magic_quotes_gpc()) ? $_POST['selectTargetAudience'] :  addslashes($_POST['selectTargetAudience']
); 

$varClassification_Resources = "%"; 
if (isset($_POST['selectClassification'])) { 
  $varClassification_Resources = (get_magic_quotes_gpc()) ? $_POST['selectClassification'] :  addslashes($_POST['selectClassification']
); 

$query_Resources="SELECT * FROM Resources WHERE Classification='".$varClassification_Resources."' && TargetAudience='".$varTargetAudience_Resources."' ORDER BY ResourceTitle ASC";
$Resources=mysql_query($query_Resources,$Competencies) or die(mysql_error());

$query_TargetAudience="SELECT * FROM TargetAudience";
$TargetAudience=mysql_query($query_TargetAudience,$Competencies) or die(mysql_error());
?>
<html> 
<head> 
<title>Untitled Document</title> 
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> 
</head> 

<body> 
<form name="form1" method="post" action=""> 
  <select name="selectClassification" size="5" id="selectClassification">
<? while($row_Classifications=mysql_fetch_array($Classifications)) { ?>
<option value="<?=$row_Classifications['ClassificationID']?>"<?=(strcmp($row_Classifications['ClassificationID'],"%")?"":" selected")?>><?=$row_Classifications['ClassificationName']?></option>
<? } ?>
</select>
<select name="selectTargetAudience" id="selectTargetAudience">
<? while ($row_TargetAudience = mysql_fetch_assoc($TargetAudience)) { ?>
<option value="<?=$row_TargetAudience['TargetAudienceId']?>"<?(strsmp($row_TargetAudience['TargetAudienceId'],
 "%")?"":" SELECTED"?>><?=$row_TargetAudience['TargetAudienceName']?></option> 
    <? } ?>
</select>
<input type="submit" name="Submit" value="Submit"> 
</form> 
<p>&nbsp;</p> 
<? while($row_Resources = mysql_fetch_assoc($Resources)) { ?>
<p>&nbsp;<?=$row_Resources['ResourceTitle']?>| <?=$row_Resources['Creator']?><br> 
  <?=$row_Resources['Description']?></p> 
<?php } ?>
</body></html>
<?
mysql_free_result($Classifications); 

mysql_free_result($Resources); 

mysql_free_result($TargetAudience); 
?>

No real logic changes here. Just a couple of aesthetic things. Also, there seems to be no reason why you must pull the first result of each query before the loop, also there is no need to do a mysql_data_seek back to zero if you're not going to be using that data again before you free_result. If you are going to be using the data again it would be quicker to place it in a big array on the first pass and then use the array in subsequent usages.
HTH
Rob

EllenM1

LOL, I think you may be attributing to me more knowledge than I have - I was actually trying to somehow come up with an "if nothing is selected, show all records" statement. I need to somehow add an "all" clause to each selector, or have them simply default to all. Currently they don't do this - and I am not sure how to add an "all" or "Make a selection" option to a dynamic form element.

I did have a litttle trouble getting some of the new code to work - some of the problem was due to copying /pasting from the browser, but even after I went through very carefully and fixed those issues, there was a problem in the option value area that I could not solve - it kept giving me a parse error saying unexpected ';' (semicolon) even though there is none there.

<option value="<?=$row_Classifications['ClassificationID']?>"<?_=(strcmp($row_Classifications['ClassificationID'],
"%")?"":" selected")?>><?=$row_Classifications['ClassificationName']?></option>

I finally ended up replacing it with the code from the earlier version and it seemed to work - although probably less efficiently than it would have.

Your new (adjusted by me) version is up at http://umhscompliance.org/indexDW15.php and I am attaching it as a text file here if you want to look at it.

Thanks very much for your help!

🙂