Jump to content

problem with building conditional MYSQL queries via PHP

mrweaver

By mrweaver
in MySQL Help

 Share

Recommended Posts

mrweaver Newbie

mrweaver

Posted
Posted

I am running MYSQL 5.0 and PHP5.

 

I have a four-table database 


create table incident (
incid int not null auto_increment primary key,
date date not null,
time time not null,
lid int not null,
otherpatrons varchar(255),
incdec text(255) not null,
police char(1),
tpa time,
inctypid int not null,
treatment varchar(100),
othertreat varchar(100),
transport varchar(100),
othertrans varchar(100),
actions text,
nonstaffinv text,
comments text,
staffinv text,
author varchar(30) not null)
Type=innodb;

create table location (
lid int not null auto_increment primary key,
location varchar(25),
incid int not null,
index loc_data(lid),
foreign key (incid) REFERENCES incident(incid) ON DELETE CASCADE)
type=innodb;

create table inctype (
inctypid int not null auto_increment primary key,
inctype varchar(100),
incid int not null,
index inct_data(itid),
foreign key (incid) REFERENCES incident(incid) ON DELETE CASCADE)
type=innodb;

create table patrons (
pid int not null auto_increment primary key,
incid int not null,
lastname varchar(30), 
firstname varchar(30),
street varchar(50),
city varchar(30),
state char(2),
zip int(5),
gender char(1),
age varchar(15),
height varchar(15),
weight varchar(15),
hair varchar(20),
eyes varchar(10),
race varchar(15),
other text,
foreign key (incid) references incident(incid) on delete cascade)
type=innodb;

 

The database is for incident reports, so that they can be analyzed historically. I want the ability to recall all entered fields for a given "incid": a record is spread out across the four tables, and I would like to be able to retrieve an entire record at once.

Below is my search form data:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd:>
<html xmlns-"http://www.w3.org/1999/xhtml">
<head meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>
Incident Report Database Search Form.
</title>
<style type="text/css">
body {
background-color: #00008b;
font-family: Helvetica, Geneva, Arial, SunSans-Regular, sans-serif;
font-size: 16px;
}
#main {
    background-color: white;
    font-family: inherit;
    font-size: inherit;
    width: 700px;
    position: relative;
    left: 150px;
    height: 600px;
}
#header {
    font-weight: bolder;
    text-align: center;
    font-size: 18px;
padding-top: 5px;
}
#search {
font-family: "Lucida Sans Unicode", sans-serif;
background-color: #87CEFA;
width: 650px;
height: 550px;
position: absolute;
left: 25px;
padding: 5px;
}

#date {
font-size: 14px;
position: relative;
width: 650px;
height: 60px;
caption-side: top;
}

#date input {
    margin: 5px;
}

#inctype {
font-size: 14px;
text-align: center;
width: 650px;
height: 100px;
}

table.table {
    border: 2px groove #D3D3D3;
    border-collapse: collapse;
    padding-right: 5px;
    padding-left: 5px;
}
td {
        border: 2px groove #D3D3D3;
    border-collapse: collapse;
}
td#other {
    padding-left: 60px;
    padding-right: 58px;
}
#location {
    font-size: 14px;
position: absolute;
top: 215
width: 200px;
height: 260px;
}
#patronname {
font-size: 14px;
position: absolute;
top: 230px;
right: 5px;
width: 200px;
height: 75px;
}
#police {
    position: absolute;
    left: 290px;
    top: 230px;
    font-size: 14px;
    width: 75px;
    height: 75px;
}
#gender {
    position: absolute;
    right: 15px;
    top: 417px;
    font-size: 14px;
    width: 100px;
    height: 100px;
}
#treatment {
    position: absolute;
    left: 255px;
    top: 290px;
    font-size: 14px;
    width: 200px;
    height: 200px;
}
#author {
font-size: 14px;
position: absolute;
    left: 255px;
    top: 423px;
}
input#Submit {
    font-size: 20px;
    color: white;
    background-color: #DA70D6;
border: 1px inset #8A2BE2;
position: absolute;
width: 90px;
height: 30px;
left: 300px;
top: 497px;
}
input#submit:hover {
    color: black;
border: 1px outset #8A2BE2;
}
</style>

</head>
<body>
    <div id="main">
       <div id="header">
        WPPL Patron Incident Report Database
                 </div>
        <div id="search">
            <form id="IRsearch" action="massivequerytest.php" method="post" name="IRsearch">
            <div id="date">
<fieldset><legend>Search by Date</legend>
Starting Month:
<select name="startmonth">
    <option value="null" selected></option>
    <option value="01">Jan</option>
    <option value="02">Feb</option>
    <option value="03">Mar</option>
    <option value="04">Apr</option>
    <option value="05">May</option>
    <option value="06">Jun</option>
    <option value="07">Jul</option>
    <option value="08">Aug</option>
    <option value="09">Sep</option>
    <option value="10">Oct</option>
    <option value="11">Nov</option>
    <option value="12">Dec</option>
</select>
Starting Year:
<select name="startyear">
    <option value="null" selected></option>
    <option value="2008">2008</option>
    <option value="2009">2009</option>
    <option value="2010">2010</option>
    <option value="2011">2011</option>
    <option value="2012">2012</option>
    <option value="2013">2013</option>
    <option value="2014">2014</option>
    <option value="2015">2015</option>
    <option value="2016">2016</option>
    <option value="2017">2017</option>
</select>

Ending Month:
<select name="endmonth">
    <option value="null" selected></option>
    <option value="01">Jan</option>
    <option value="02">Feb</option>
    <option value="03">Mar</option>
    <option value="04">Apr</option>
    <option value="05">May</option>
    <option value="06">Jun</option>
    <option value="07">Jul</option>
    <option value="08">Aug</option>
    <option value="09">Sep</option>
    <option value="10">Oct</option>
    <option value="11">Nov</option>
    <option value="12">Dec</option>
</select>
Ending Year:
<select name="endyear">
    <option value="null" selected></option>
    <option value="2008">2008</option>
    <option value="2009">2009</option>
    <option value="2010">2010</option>
    <option value="2011">2011</option>
    <option value="2012">2012</option>
    <option value="2013">2013</option>
    <option value="2014">2014</option>
    <option value="2015">2015</option>
    <option value="2016">2016</option>
    <option value="2017">2017</option>
</select>
                            </fieldset>
            </div>
            <br style="font-size: 10px;" />
            <div id="inctype"><fieldset><legend>
                 Type of Incident:</legend>
                    <table class="table" name="incidenttype[]">
                            <tr><td><label for="incidenttype[vandalism]">Vandalism</label><br /><input type="checkbox" name="incidenttype[vandalism]" value="Vandalism" /></td>
		    <td><label for="incidenttype[theft]">Theft</label><br /><input type="checkbox" name="incidenttype[theft]" value="Theft" /></td>
                            <td><label for="incidenttype[sex]">Sex Offense</label><br /><input type="checkbox" name="incidenttype[sex_Offense]" value="Sex_Offense" /></td>
		    <td><label for="incidenttype[inappropriate_internet]">Inappropriate use of Internet</label><br /><input type="checkbox" name="incidenttype[inappropriate_internet]" value="Inappropriate_internet" /></td></tr>
                            <tr><td><label for="incidenttype[bombthreat_firealarm]">Bomb Threat/Fire Alarm</label><br /><input type="checkbox" name="incidenttype[bombthreat_firealarm]" value="bombthreat_firealarm" /></td>
                            <td><label for="incidenttype[disruptive_behavior]">Disruptive Behavior</label><br /><input type="checkbox" name="incidenttype[disruptive_behavior]" value="disruptive_behavior" /></td>
                            <td><label for="incidenttype[illness_accident]">Illness/Accident</label><br /><input type="checkbox" name="incidenttype[illess_accident]" value="illess_accident" /></td>
		    <td><label for="incidenttype[unattendedkid]">Unattended Child</label><br /><input type="checkbox" name="incidenttype[unattendedkid]" value="unattendedkid" /></td></tr>
                            </table>
                            <table class="table" >
                            <tr><td id="other"><label for="incidenttype[other_incident]">Other (Retrieves records with additional incident types added by staff):</label><input type="checkbox" name="incidenttype[other_incident]" value="other_incident"/ ></td></tr>
                    </table></fieldset>
    </div>
              <br style="font-size: 10px;" />
            <div id="patronname"><fieldset><legend>Search by patron name:</legend>(if patron was <b>subject</b> of incident report)<br /><label for="lastname"> Last Name</label>
                <input type="textbox" size="25" name="lastname" /><br /><label for="lastname">First Name<input type="textbox" size="25" name="lastname" />
             </fieldset></div>
            <br style="font-size: 20px;" />
            <div id="location"><fieldset><legend>Library Location</legend><select name="location[]" multiple="multiple">
                <option value="[Circulation]">Circulation</option>
	<option value="[Lobby]">Lobby</option>
	<option value="[Reference]">Reference</option>
	<option value="[Popular_Materials">Popular Materials</option>
	<option value="[Garden]">Reading Garden</option>
	<option value="[Meeting_Room]">Meeting Room</option>
	<option value="[YouthServ]">Youth Services</option>
	<option value="[ElecServ]">Electronic Services</option>
	<option value="[TechServ]">Tech Services</option>
	<option value="[Administration]">Administration Offices</option>
	<option value="[Parking_Lot]">Parking Lot</option>
	<option value="[buildingServices]">Building Services</option>
                <option value="[Restroom-YS]">Restroom-YS</option>
	<option value="[Restroom-Ref/ElecServices]">Restroom-Ref/Elec. Services</option>
	<option value="[Restroom-Lobby-Male]">Restroom-Lobby-Male</option>
                <option value="[Restroom-Lobby-Female]">Restroom-Lobby-Female</option>
	<option value="[Restroom-MeetingRoom-Male]">Restroom-MeetingRoom-Male</option>
                <option value="[Restroom-MeetingRoom-Female]">Restroom-MeetingRoom-Female</option>
	</select></fieldset></div>
                <br style="font-size: 10px;" />
                <div id="police"><fieldset><legend>Police called</legend>
                <input style="margin-right: 5px;" type="CHECKBOX" name="police" value="police" />YES
                </div>
                <div id="gender"><fieldset><legend>Gender:</legend>of subject of incident report
                    <select name="gender" size="1">
	    <option selected="selected" value=""></option>
	    <option value="M">Male</option>
	    <option value="F">Female</option>
	    <option value="?">Unknown</option>
	    </select></fieldset>
                </div>
                <div id="treatment"><fieldset><legend>Search by Treatment</legend>
                <table class="table" name="treatment[]">
                    <tr><td><label for="[TreatmentNone]">None</label></td><td><input type="checkbox" name="treatment[TreatmentNone]" value="No_Treatment" /></td></tr>
                    <tr><td><label for="[Taken_went_home]">Taken/Went Home</label></td><td><input type="checkbox" name="treatment[Taken_went_home]" value="Taken_went_home" /></td></tr>
                    <tr><td><label for="[hospital]">To Hospital:</label></td><td><input type="checkbox" name="treatment[hospital]" value="hospital" /></td></tr>
                    <tr><td><label for="[other_treatment]">Other:</label></td><td><input type="checkbox" name="treatment[other_treatment]" value="other_treatment" /></td></tr>
                </table>
                </fieldset></div>
                <div id="author"><fieldset><legend>Report Author</legend><input type="textbox" size="40" name="reportauthor" /></fieldset>
                </div>
                <input id="Submit" type="SUBMIT" name="Submit" value="Submit" />
                </form>
    </div>
    
</body>
</html>

 

I have been thinking that I should use conditional logic in the construction of "select" statements to get my incids and then use the incids to collect the rest of the fields for each incid.

 

My current efforts in this area have not been successful.

 

<?php

//print_r($_POST['Submit']['incidenttype']);
$input = $_POST['Submit'];

if (isset($_POST['location'])) {
   $location = $_POST['location'];   
} else {
   $location = null;      
}

if (isset($_POST['incidenttype'])) {
   $inctype = $_POST['incidenttype'];   
} else {
   $inctype = null;      
}

if (isset($_POST['incidenttype']['other_incident'])) {
    $other_inc= $_POST['incidenttype']['otherinc'];
} else {
    $other_inc = null;
}


if (isset($_POST['treatment'])) {
$treatment = $_POST['treatment'];
} else {
$treatment = null;
}

if (isset($_POST['treatment']['other_treatment'])) {
    $othertreat = $_POST['treatment']['other_treatment'];
} else {
    $othertreat = null;
}
if (isset($_POST['treatment']['hospital'])) {
    $hospital = $_POST['treatment']['hospital'];
} else {
    $hospital = null;
    }
if(isset($_POST['police'])) {
    $police = $_POST['police'];
} else {
    $police = null;
}
/*
if(isset($_POST['date'])) {
    $date = $_POST['date'];
} else {
    $date = '';
}*/
if(isset($_POST['author'])) {
    $author = $_POST['author'];
} else {
    $author = null;
}

if (isset($_POST['startmonth'])) {
$startmonth = $_POST['startmonth'];
} else {$startmonth = null;
}

if (isset($_POST['startyear'])) {
    $startyear = $_POST['startyear'];
} else {
    $startyear = null;
}
if (isset($_POST['endmonth'])) {
    $endmonth = $_POST['endmonth'];
} else {
    $endmonth = null;
}

if (isset($_POST['endyear'])) {
    $endyear = $_POST['endyear'];
} 
else {
    $endyear = null;
}

if ($input) {
$gender = ($_POST['gender']);
$lastname = ($_POST['lastname']);
}
//Database connect
$hostname = "localhost";
$database = "incident_form";
$username = "root";
$password = "aMi3pswd";

$con = mysql_connect($hostname,$username,$password);


if (mysql_select_db($database, $con)) {
echo "Successful connection<br />";
}

/*

if (isset($startyear) && isset($endyear)) {

    while ($startyear > $endyear && $endyear != '') {
    die('Your starting year is larger than your end year');
    }
}


*/  

if (isset($location)) {
$table = 'location';
$field = 'location';
$var = '{$location}';
}

if (isset($startyear) || isset($gender) || isset($police) || isset($author) || isset($treatment)){
    $table= 'incident';
    $field = 'startmonth' || 'startyear' || 'endmonth' || 'endyear' || 'police' || 'author' || 'treatment'|| 'gender';
    $var = array($startmonth || $startyear || $endmonth || $endyear || $police || $author || $treatment || $gender);
}

if (isset($inctype)) {
    $table= 'inctype';
    $field = 'inctype';
    $var = '{$inctype}';
}

if (isset($lastname)) {
    $table= 'patrons';
    $field = 'lastname';
    $var = '{$lastname}';
} 

    $query = "SELECT distinct incid from ";
    
    if(is_array($table)) {
      $query .= implode(',', $table);
    } else {
      $query .= $table;
    }
    
    $query .= ' WHERE ';
    
    if (empty($startmonth) && isset($startyear) && empty($endmonth) && empty($endyear)){
-            $query .= "year(date) = '{$startyear}' ";
    }
      elseif(isset($startmonth) && isset($startyear) && empty($endmonth) && empty($endyear)){ 
-                $query .= "month(date) = '{$startmonth}'  and year(date) = '{$startyear}' ";
}
      elseif (isset($startmonth) && isset($startyear) && isset($endmonth) && empty($endyear)){
-            $query .= "month(date) between '($startmonth)' AND '{$endmonth}' and year(date) = '{$startyear}' ";
            }
     //below works
elseif(isset($startmonth) && isset($startyear) && isset($endmonth) && isset($endyear)) {-                $query .= "month(date) between '($startmonth)' AND '{$endmonth}' AND year(date) BETWEEN '{$startyear}' AND '{$endyear}' ";
            }             else {
-                $startmonth = '';
-                $startyear = '';
-                $endmonth = '';
-                $startmonth = '';
    }
    
    if (is_array($field)) {
        $query .= implode(',', $field);
    }
    else {
        $query .= $field;
    }
    
    if($lastname){
      $query .= ' LIKE '%$var%' ';
    } else {
    $query .= " = $var";
    }



echo $query;
$rs = mysql_query($query);
if ($rs)
{ echo "Records for your date query" . "<br />";
//return with assoc array, you can change to MYSQL_NUM or MYSQL_BOTH
while ($tmp=mysql_fetch_array($rs, MYSQL_ASSOC))
{
$result[] = $tmp;
echo " {$result} <br />";
}
mysql_free_result($rs);
}
   
   
/*     
            foreach ($data as $id) {
//incident table query
        $query1 = "select * from incident where incident.incid = '{$id}'";
        
        $result1 = mysql_query($query1) or die(mysql_error(). '<br />' . $query1);

           $data1 = mysql_fetch_array($result1);
               print_r($data1);
            }

          
//patron table query
           $query2 = "select * from patrons where patrons.incid = all (select incid from incident where incid = '{$id}')";

        $result2 = mysql_query($query2) or die(mysql_error(). '<br />' . $query2);

           $data2 = mysql_fetch_array($result2);
                //print_r($data2);
                
//location table query     
                $query3 = "select location from location where location.incid = all (select incid from incident where incid = '{$id}')";
    
        $result3 = mysql_query($query3) or die(mysql_error(). '<br />' . $query3);

           $data3 = mysql_fetch_array($result3);
                //print_r($data3);
           
//incident type table query     
                $query4 = "select inctype from inctype where inctype.incid = all (select incid from incident where incid = '{$id}')";
       
        $result4 = mysql_query($query4) or die(mysql_error(). '<br />' . $query4);

           $data4 = mysql_fetch_array($result4);
           
            if($data3 = null) {
            echo "No patron information available for your search.";
           }
           if($data4 = null) {
            echo "No incident type available for your search.";
           }
           echo "{$data1['date']}" . "<br />" . "{$data1['time']}" . "<br />" . "{$data1['otherpatrons']}";
        
        

*/    
?>
       

 

Has anyone done a project like this and can give me some "big picture"-type guidance, because I'm feeling rather lost.

 

mrweaver

mrweaver Newbie

mrweaver

Posted
  • Author
Posted

The database is populated by an electronic form, on a per-incident basis.

 

I have created a separate search form for the database so that historical analysis can be accomplished.

 

After consulting with the final users of this database, I came up with the different means of searching included in the search form--code included in previous post.

 

Given that someone may search using only one item (they want to recall all of the reports about incidents when police where called) or they may do a more complex search (for a date range as well as the number of incidents that occurred in a single location), I want to know how to locate the incid numbers (the primary key for the parent table in the database) by building a "select" statement based on the types of searches: if someone doesn't use a search function, that option is not passed to the "select" statement, and if someone chooses multiple search functions, all of those searches are included in the "select" statement.

 

I hope that explains things. Thanks for responding.

 

 

mrweaver Newbie

mrweaver

Posted
  • Author
Posted

Well, I am pulling the incid number out of my other three tables, as well. For instance: "patrons" has its own table. So, to get the "incid" for an incident involving one patron. Then I would also need to take into account variables for the table name, not just the "Where" clause.

 

$query = "select incid from ";
if ($lastname) {
$query .= " patrons where lastname LIKE '%$lastname%';
}

 

Now, if I want to also include a query on the 'incident' table, like for whether the police where called, also with the initial query statement above, it would like like this:

 

if ($police) {
$query .= " incident where police = 'Yes';
}

 

If I were to combine these two queries into one statement using PHP, I get a huge mess. Should I keep the queries separate (and queries that would be run on the other two tables in the database), combine the result sets, and then get rid of duplicates?

 

Thanks.

mrweaver Newbie

mrweaver

Posted
  • Author
Posted

So, if I'm only pulling the "incid" out of the tables at this point, that would be a valid use of the  "union" statement. Intriguing.

 

This would greatly reduce the number of conditional clauses that I have to play around with, correct?

mrweaver Newbie

mrweaver

Posted
  • Author
Posted

Hm...

 

In my form I have different search options for date searches, and then all of the various other searches. Do these constitute an array, so that I need to process the results as arrays?

 

Here is my form code, so you can see all of the search options:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd:>
<html xmlns-"http://www.w3.org/1999/xhtml">
<head meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>
Incident Report Database Search Form.
</title>
<style type="text/css">
body {
background-color: #00008b;
font-family: Helvetica, Geneva, Arial, SunSans-Regular, sans-serif;
font-size: 16px;
}
#main {
    background-color: white;
    font-family: inherit;
    font-size: inherit;
    width: 700px;
    position: relative;
    left: 150px;
    height: 600px;
}
#header {
    font-weight: bolder;
    text-align: center;
    font-size: 18px;
padding-top: 5px;
}
#search {
font-family: "Lucida Sans Unicode", sans-serif;
background-color: #87CEFA;
width: 650px;
height: 550px;
position: absolute;
left: 25px;
padding: 5px;
}

#date {
font-size: 14px;
position: relative;
width: 650px;
height: 60px;
caption-side: top;
}

#date input {
    margin: 5px;
}

#inctype {
font-size: 14px;
width: 650px;
height: 100px;
}

table.table {
    border: 2px groove #D3D3D3;
    border-collapse: collapse;
    padding-right: 5px;
    padding-left: 5px;
}
td {
        border: 2px groove #D3D3D3;
    border-collapse: collapse;
}
td#other {
    padding-left: 60px;
    padding-right: 58px;
}
#location {
    font-size: 14px;
position: absolute;
top: 350px;
width: 200px;
height: 260px;
}
#patronname {
font-size: 14px;
position: absolute;
top: 100px;
right: 75px;
width: 200px;
height: 75px;
}
#police {
    position: absolute;
    
    top: 290px;
    font-size: 14px;
    width: 75px;
    height: 75px;
}
#gender {
    position: absolute;
    right: 15px;
    top: 290px;
    font-size: 14px;
    width: 100px;
    height: 100px;
}
#treatment {
    position: absolute;
    left: 255px;
    top: 290px;
    font-size: 14px;
    width: 200px;
    height: 200px;
}
#author {
font-size: 14px;
position: absolute;
    left: 255px;
    top: 423px;
}
input#Submit {
    font-size: 20px;
    color: white;
    background-color: #DA70D6;
border: 1px inset #8A2BE2;
position: absolute;
width: 90px;
height: 30px;
left: 300px;
top: 497px;
}
input#submit:hover {
    color: black;
border: 1px outset #8A2BE2;
}
</style>

</head>
<body>
    <div id="main">
       <div id="header">
        WPPL Patron Incident Report Database
                 </div>
        <div id="search">
            <form id="IRsearch" action="massivequerytest.php" method="post" name="IRsearch">
            <div id="date">
<fieldset><legend>Search by Date</legend>
Starting Month:
<select name="startmonth">
    <option value="" selected></option>
    <option value="01">Jan</option>
    <option value="02">Feb</option>
    <option value="03">Mar</option>
    <option value="04">Apr</option>
    <option value="05">May</option>
    <option value="06">Jun</option>
    <option value="07">Jul</option>
    <option value="08">Aug</option>
    <option value="09">Sep</option>
    <option value="10">Oct</option>
    <option value="11">Nov</option>
    <option value="12">Dec</option>
</select>
Starting Year:
<select name="startyear">
    <option value="" selected></option>
    <option value="2008">2008</option>
    <option value="2009">2009</option>
    <option value="2010">2010</option>
    <option value="2011">2011</option>
    <option value="2012">2012</option>
    <option value="2013">2013</option>
    <option value="2014">2014</option>
    <option value="2015">2015</option>
    <option value="2016">2016</option>
    <option value="2017">2017</option>
</select>

Ending Month:
<select name="endmonth">
    <option value="" selected></option>
    <option value="01">Jan</option>
    <option value="02">Feb</option>
    <option value="03">Mar</option>
    <option value="04">Apr</option>
    <option value="05">May</option>
    <option value="06">Jun</option>
    <option value="07">Jul</option>
    <option value="08">Aug</option>
    <option value="09">Sep</option>
    <option value="10">Oct</option>
    <option value="11">Nov</option>
    <option value="12">Dec</option>
</select>
Ending Year:
<select name="endyear">
    <option value="" selected></option>
    <option value="2008">2008</option>
    <option value="2009">2009</option>
    <option value="2010">2010</option>
    <option value="2011">2011</option>
    <option value="2012">2012</option>
    <option value="2013">2013</option>
    <option value="2014">2014</option>
    <option value="2015">2015</option>
    <option value="2016">2016</option>
    <option value="2017">2017</option>
</select>
                            </fieldset>
            </div>
            <br style="font-size: 10px;" />
            <div id="inctype"><fieldset><legend>
                 Type of Incident:</legend>
                    <select  name="incidenttype" multiple="none">
                        
                            <option value=""></option>
                            <option value="illness_accident">Illness/Accident</option>
                            <option value="vandalism">Vandalism</option>
                            <option value="theft">Theft</option>
                            <option value="sex">Sex Offense</option>
                            <option value="inappropriate_internet">Inappropriate use of Internet</option>
                            <option value="bombthreat_firealarm">Bomb Threat/Fire Alarm</option>
                            <option value="disruptive_behavior">Disruptive Behavior</option>
                            <option value="unattendedkid">Unattended Child</option>
                            <option value="other_incident">Other (Additional incident types added by staff)</option>
                            
                        </select></fieldset>
    </div>
              <br style="font-size: 10px;" />
            <div id="patronname"><fieldset><legend>Search by patron name:</legend>(if patron was <b>subject</b> of incident report)<br /><label for="lastname"> Last Name</label>
                <input type="textbox" size="25" name="lastname" /><br /><label for="lastname">First Name<input type="textbox" size="25" name="lastname" />
             </fieldset></div>
            <br style="font-size: 20px;" />
            <div id="location"><fieldset><legend>Library Location</legend><select name="location" >
                <option value="" selected></option>
                <option value="[Circulation]">Circulation</option>
	<option value="[Lobby]">Lobby</option>
	<option value="[Reference]">Reference</option>
	<option value="[Popular_Materials">Popular Materials</option>
	<option value="[Garden]">Reading Garden</option>
	<option value="[Meeting_Room]">Meeting Room</option>
	<option value="[YouthServ]">Youth Services</option>
	<option value="[ElecServ]">Electronic Services</option>
	<option value="[TechServ]">Tech Services</option>
	<option value="[Administration]">Administration Offices</option>
	<option value="[Parking_Lot]">Parking Lot</option>
	<option value="[buildingServices]">Building Services</option>
                <option value="[Restroom-YS]">Restroom-YS</option>
	<option value="[Restroom-Ref/ElecServices]">Restroom-Ref/Elec. Services</option>
	<option value="[Restroom-Lobby-Male]">Restroom-Lobby-Male</option>
                <option value="[Restroom-Lobby-Female]">Restroom-Lobby-Female</option>
	<option value="[Restroom-MeetingRoom-Male]">Restroom-MeetingRoom-Male</option>
                <option value="[Restroom-MeetingRoom-Female]">Restroom-MeetingRoom-Female</option>
	</select></fieldset></div>
                <br style="font-size: 10px;" />
                <div id="police"><fieldset><legend>Police called</legend>
                <input style="margin-right: 5px;" type="CHECKBOX" name="police" value="police" />YES
                </div>
                <div id="gender"><fieldset><legend>Gender:</legend>of subject of incident report
                    <select name="gender" size="1">
	    <option selected="selected" value=""></option>
	    <option value="M">Male</option>
	    <option value="F">Female</option>
	    <option value="?">Unknown</option>
	    </select></fieldset>
                </div>
                <div id="treatment"><fieldset><legend>Search by Treatment</legend>
                <table class="table" name="treatment[]">
                    <tr><td><label for="[TreatmentNone]">None</label></td><td><input type="checkbox" name="treatment[TreatmentNone]" value="No_Treatment" /></td></tr>
                    <tr><td><label for="[Taken_went_home]">Taken/Went Home</label></td><td><input type="checkbox" name="treatment[Taken_went_home]" value="Taken_went_home" /></td></tr>
                    <tr><td><label for="[hospital]">To Hospital:</label></td><td><input type="checkbox" name="treatment[hospital]" value="hospital" /></td></tr>
                    <tr><td><label for="[other_treatment]">Other:</label></td><td><input type="checkbox" name="treatment[other_treatment]" value="other_treatment" /></td></tr>
                </table>
                </fieldset></div>
                <div id="author"><fieldset><legend>Report Author</legend><input type="textbox" size="40" name="reportauthor" /></fieldset>
                </div>
                <input id="Submit" type="SUBMIT" name="Submit" value="Submit" />
                </form>
    </div>
    
</body>
</html>

 

Would I combine each of these fields' related queries with UNION DISTINCT? And, as a build the query with conditional logic, how do I work that in there?

 

This project is driving me crazy at this point. I think my existing code is such a mess that I may have to start from scratch.

 

 

fenway Advanced Member

fenway

Posted
Posted

In my form I have different search options for date searches, and then all of the various other searches. Do these constitute an array, so that I need to process the results as arrays?

 

Would I combine each of these fields' related queries with UNION DISTINCT? And, as a build the query with conditional logic, how do I work that in there?

I'm not sure I know what you mean... you'll need to group the related options as they pertain to your DB structure (a simple prefix will make them easy to gather).

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.