Basic “Advanced” Search Form with 3 selectors

Posted on March 23, 2004 by ellen

Here is a basic search form done Dreamweaver MX that selects records based on the input from several form selectors.

Here is a basic search form created in Dreamweaver MX that selects records based on the input from several form selectors.

See demo here

For example, you can select results based on author, keyword and subject.

The example below selects medical learning resources based on subject classification, target audience, and keyword. There are a few dummy records so you can test the form out.

This uses simple table relations instead of table joins, which limits the complexity of the database setup.

Import this SQL file into your database

# --------------------------------------------------------

#
# Table structure for table `Classifications`
#
# Creation: Mar 16, 2004 at 04:58 PM
# Last update: Mar 20, 2004 at 01:01 AM
#

CREATE TABLE `Classifications` (
  `ClassificationID` smallint(5) NOT NULL default '0',
  `ClassificationName` text,
  PRIMARY KEY  (`ClassificationID`),
  FULLTEXT KEY `ClassificationName` (`ClassificationName`)
) TYPE=MyISAM;

#
# Dumping data for table `Classifications`
#

INSERT INTO `Classifications` VALUES (2, 'Emergency Medicine');
INSERT INTO `Classifications` VALUES (3, 'Nursing');
INSERT INTO `Classifications` VALUES (4, 'Cardiology');
INSERT INTO `Classifications` VALUES (5, 'Geriatrics');
INSERT INTO `Classifications` VALUES (6, 'Internal Medicine');
INSERT INTO `Classifications` VALUES (7, 'Policies and Procedures');
INSERT INTO `Classifications` VALUES (8, 'Safety');
INSERT INTO `Classifications` VALUES (9, 'Pediatrics');
INSERT INTO `Classifications` VALUES (10, 'Oncology and Hematology');
INSERT INTO `Classifications` VALUES (11, 'Immunology');
INSERT INTO `Classifications` VALUES (12, 'Anesthesiology');
INSERT INTO `Classifications` VALUES (13, 'Surgery');

# --------------------------------------------------------

#
# Table structure for table `Population`
#
# Creation: Mar 16, 2004 at 04:44 PM
# Last update: Mar 16, 2004 at 08:18 PM
#

CREATE TABLE `Population` (
  `PopulationID` tinyint(25) NOT NULL auto_increment,
  `PopTitle` text,
  PRIMARY KEY  (`PopulationID`),
  FULLTEXT KEY `PopTitle` (`PopTitle`)
) TYPE=MyISAM AUTO_INCREMENT=6 ;

#
# Dumping data for table `Population`
#

INSERT INTO `Population` VALUES (1, 'Adult');
INSERT INTO `Population` VALUES (2, 'Child');
INSERT INTO `Population` VALUES (3, 'Neonatal');
INSERT INTO `Population` VALUES (4, 'Geriatric');
INSERT INTO `Population` VALUES (5, 'All');

# --------------------------------------------------------

#
# Table structure for table `Resources`
#
# Creation: Mar 16, 2004 at 05:05 PM
# Last update: Mar 16, 2004 at 11:30 PM
# Last check: Mar 16, 2004 at 05:05 PM
#

CREATE TABLE `Resources` (
  `ResourceId` smallint(5) NOT NULL auto_increment,
  `ResourceTitle` text,
  `Description` text,
  `URL` text,
  `Classification` text,
  `Population` text,
  `TargetAudience` text,
  PRIMARY KEY  (`ResourceId`),
  FULLTEXT KEY `ResourceTitle` (`ResourceTitle`,`Description`,`URL`)
) TYPE=MyISAM AUTO_INCREMENT=9 

/* RELATIONS FOR TABLE `Resources`:
    `Classification`
        `Classifications` -> `ClassificationID`
    `Population`
        `Population` -> `PopulationID`
    `TargetAudience`
        `TargetAudience` -> `TargetAudienceId`
*/;

#
# Dumping data for table `Resources`
#

INSERT INTO `Resources` VALUES (1, 'Pain and the 3 M's: Medication, Monitoring and Management          ', 'Competency Statement:                  Effectively manages all patient's pain through appropriate administration of medications, monitoring and management                 ', 'http://www.umich.edu/', '12', '1', '5');
INSERT INTO `Resources` VALUES (6, 'Cardiac Arrest Response', 'Competency Statement: The clerk responds appropriately in the event of a cardiac arrest.                 ', 'http://www.med.umich.edu/', '4', '1', '1');
INSERT INTO `Resources` VALUES (3, 'Basic Life Support/Emergency Procedures', 'Competency: Provide Basic Life Support for infants, children and adults according to American Heart Association Standards.', 'http://www.umich.edu/', '2', '1', '1');
INSERT INTO `Resources` VALUES (4, 'Diversity', 'Competency: Ability to supervise effectively in a diverse environment.rnThe Essentials for Leading a Diverse Workforce focuses on executing one's basic supervisory responsibilities in the context of a diverse  environment. The curriculum consists of four modules:', 'http://www.umich.edu', '7', '1', '1');
INSERT INTO `Resources` VALUES (5, 'Teaching Physician Documentation Rules', 'Documentation    for physician services', 'http://www.umich.edu/', '7', NULL, '5');
INSERT INTO `Resources` VALUES (7, 'Psychology for House Officers', 'Competency Statement: Psychology basics for House Officers.', 'http://www.umich.edu/', '5', '4', '4');
INSERT INTO `Resources` VALUES (8, 'a nursing competency', 'nursing stuff', NULL, '3', '2', '1');

# --------------------------------------------------------

#
# Table structure for table `TargetAudience`
#
# Creation: Mar 16, 2004 at 05:01 PM
# Last update: Mar 20, 2004 at 01:00 AM
#

CREATE TABLE `TargetAudience` (
  `TargetAudienceId` tinyint(25) NOT NULL auto_increment,
  `TargAudTitle` text,
  PRIMARY KEY  (`TargetAudienceId`)
) TYPE=MyISAM AUTO_INCREMENT=7 ;

#
# Dumping data for table `TargetAudience`
#

INSERT INTO `TargetAudience` VALUES (1, 'RN');
INSERT INTO `TargetAudience` VALUES (2, 'LPN');
INSERT INTO `TargetAudience` VALUES (3, 'AP');
INSERT INTO `TargetAudience` VALUES (4, 'House Officer');
INSERT INTO `TargetAudience` VALUES (5, 'MD');

Create a connection file – this one was created by Dreamweaver. Put it in the root of your site – set the permissions to 644 – or set up the connection in Dreamweaver, and it will do it for you:

<?php
# FileName="Connection_php_mysql.htm"
# Type="MYSQL"
# HTTP="true"
$hostname_Competencies = "localhost";
$database_Competencies = "databaseName";
$username_Competencies = "userName";
$password_Competencies = "password";
$Competencies = mysql_pconnect($hostname_Competencies, $username_Competencies, $password_Competencies) or trigger_error(mysql_error(),E_USER_ERROR); 
?>

In my example, the database connection is called “$Competencies”, which is used throughout – you need to go through and replace with your own variable names to match your own setup.

Then create the php form itself:

<?php require_once('../Connections/Competencies.php'); ?>
<?php
mysql_select_db($database_Competencies, $Competencies);
$query_rsClassification = "SELECT * FROM Classifications ORDER BY ClassificationName ASC";
$rsClassification = mysql_query($query_rsClassification, $Competencies) or die(mysql_error());
$row_rsClassification = mysql_fetch_assoc($rsClassification);
$totalRows_rsClassification = mysql_num_rows($rsClassification);

mysql_select_db($database_Competencies, $Competencies);
$query_rsTargetAudience = "SELECT * FROM TargetAudience ORDER BY TargAudTitle ASC";
$rsTargetAudience = mysql_query($query_rsTargetAudience, $Competencies) or die(mysql_error());
$row_rsTargetAudience = mysql_fetch_assoc($rsTargetAudience);
$totalRows_rsTargetAudience = mysql_num_rows($rsTargetAudience);

$textField_rsResources = "%";
if (isset($_POST['textField'])) {
  $textField_rsResources = (get_magic_quotes_gpc()) ? $_POST['textField'] : addslashes($_POST['textField']);
}
$selectClassification_rsResources = "%";
if (isset($_POST['selectClassification'])) {
  $selectClassification_rsResources = (get_magic_quotes_gpc()) ? $_POST['selectClassification'] : addslashes($_POST['selectClassification']);
}
$selectTargetAudience_rsResources = "%";
if (isset($_POST['selectTargetAudience'])) {
  $selectTargetAudience_rsResources = (get_magic_quotes_gpc()) ? $_POST['selectTargetAudience'] : addslashes($_POST['selectTargetAudience']);
}
mysql_select_db($database_Competencies, $Competencies);
$query_rsResources = sprintf("SELECT DISTINCT ResourceTitle, Description, URL FROM Resources WHERE Classification LIKE '%s' AND TargetAudience LIKE '%s' AND Description LIKE '%%%s%%' ORDER BY ResourceTitle ASC", $selectClassification_rsResources,$selectTargetAudience_rsResources,$textField_rsResources);
$rsResources = mysql_query($query_rsResources, $Competencies) or die(mysql_error());
$row_rsResources = mysql_fetch_assoc($rsResources);
$totalRows_rsResources = mysql_num_rows($rsResources);
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>form test</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
.style1 {
font-family: Arial, Helvetica, sans-serif;
font-weight: bold;
}
.style2 {font-family: "Courier New", Courier, mono}
-->
</style>
</head>

<body>



<form name="form2" action="" method="POST">
  <p>classification
    <select name="selectClassification" id="selectClassification">
    <option value="%" <?php if (!(strcmp("%", "%"))) {echo "SELECTED";} ?>>No Selection</option>
    <?php
do {  
?>
    <option value="<?php echo $row_rsClassification['ClassificationID']?>"<?php if (!(strcmp($row_rsClassification['ClassificationID'], "%"))) {echo "SELECTED";} ?>><?php echo $row_rsClassification['ClassificationName']?></option>
    <?php
} while ($row_rsClassification = mysql_fetch_assoc($rsClassification));
  $rows = mysql_num_rows($rsClassification);
  if($rows > 0) {
      mysql_data_seek($rsClassification, 0);
  $row_rsClassification = mysql_fetch_assoc($rsClassification);
  }
?>
    </select>
    <br> 
TargetAudience: 
<select name="selectTargetAudience" id="selectTargetAudience">
    <option value="%" <?php if (!(strcmp("%", "%"))) {echo "SELECTED";} ?>>No Selection</option>
    <?php
do {  
?>
    <option value="<?php echo $row_rsTargetAudience['TargetAudienceId']?>"<?php if (!(strcmp($row_rsTargetAudience['TargetAudienceId'], "%"))) {echo "SELECTED";} ?>><?php echo $row_rsTargetAudience['TargAudTitle']?></option>
    <?php
} while ($row_rsTargetAudience = mysql_fetch_assoc($rsTargetAudience));
  $rows = mysql_num_rows($rsTargetAudience);
  if($rows > 0) {
      mysql_data_seek($rsTargetAudience, 0);
  $row_rsTargetAudience = mysql_fetch_assoc($rsTargetAudience);
  }
?>
</select>
</p>
  <p>
    <input name="textField" type="text" id="textField">
    <br> 
    <input type="submit" value="Submit"> 
    </p>
</form> 


<?php do { ?>
<p class="style1"><?php echo $row_rsResources['ResourceTitle']; ?></p>
<p class="style2"><?php echo $row_rsResources['Description']; ?></p>
<hr>
<p>
  <?php
  while(list($key, $Value) = each($_POST)){
  echo $key ." : ". $Value . "<br>";
  }
  ?>
</p>
<?php } while ($row_rsResources = mysql_fetch_assoc($rsResources)); ?>
<p> </p>
</body>
</html>
<?php
mysql_free_result($rsClassification);

mysql_free_result($rsTargetAudience);

mysql_free_result($rsResources);
?>

2 thoughts on “Basic “Advanced” Search Form with 3 selectors”

chipatcho says:

November 26, 2008 at 11:43 am

wow that looks powerful but i wonder how can i make extra two search parameters for timestamp between two dates considering i have a timestamp field in my database.

Log in to Reply
Jay says:

October 1, 2011 at 4:48 am

Awesome!!!
—–

Log in to Reply

2 thoughts on “Basic “Advanced” Search Form with 3 selectors”

Leave a Reply Cancel reply

Categories

My Scarves on Etsy