Jump to content

[SOLVED] problem with drop-down filter

rsammy

By rsammy
in PHP Coding Help

Recommended Posts

rsammy Regular Member

rsammy

Posted
Posted

i am working with an option box(drop down box) with a list of values. i am able to display this field on the screen. but what i need is to use this value(user selects a value from this drop down) and then hit the search button to execute a query. the value of this drop down should be one of the five filters on screen(First Name, Last name, location and DOB being the others).

 

this is my query and code which displays my fields on the screen:

 

					$query1= "select pat_dgraphics.pat_first_name, pat_dgraphics.pat_mid_init, pat_dgraphics.pat_last_name, pat_dgraphics.pat_phy_id,
				DATE_FORMAT(pat_dgraphics.pat_dob, '%m/%d/%Y') as dob, pat_dgraphics.pat_ssn, admit_stat.admit_visit_loc, admit_stat.admit_room_no,
				admit_stat.admit_reason
				FROM admit_stat, pat_dgraphics
				WHERE admit_stat.admit_pat_id=pat_dgraphics.pat_ID
				AND pat_last_name like '%$patlname%'
				AND pat_first_name like '%$patfname%'
				AND (Date_Format(pat_dob, '%m/%d/%Y') $pat_dob OR  Date_Format(pat_dob, '%c/%e/%Y') $pat_dob  )
				AND admit_stat.admit_visit_loc like '%$location%'
				AND pat_dgraphics.pat_client_id $clientid
				ORDER by pat_last_name, pat_first_name ASC";

$result=mysql_db_query("$database[dbname]", $query1);

$num_rows = mysql_num_rows($result);
$num_results=mysql_num_rows($result);

$i=1;
if(!isset($rowNum))
{
	$rowNum = 0;
}
$rowNumHold = $rowNum;
$count = 1;
$limit_results = 25;
if ($num_rows > 0)
{
	for($q=0;$q<$limit_results && $rowNum < $num_rows;$q++)
	{
		if(mysql_data_seek($result, $rowNum++))
		{
			if ($i++%2)
			{
			print ("<tr  bgcolor=\"#cccccc\">");
			}
			else
			{
			print ("<tr>");
			}

			if ($row = mysql_fetch_array($result))
			{
					$pat_first_name=$row["pat_first_name"];

					$pat_mid_init=$row["pat_mid_init"];

					$pat_last_name=$row["pat_last_name"];

					$pat_ssn=$row["pat_ssn"];

					$dob=$row["dob"];

					$admit_visit_loc=$row["admit_visit_loc"];

					$admit_room_no=$row["admit_room_no"];

					$phy_id=$row["pat_phy_id"];

					$client_id=$row["pat_client_id"];

?>
              <td width="124"> <div align="left"><font class="plnTxtBl"> <? print ("$pat_last_name"); print (", ");  print("$pat_first_name"); ?>
                  </font> </div></td>
              <?  print ("<td width=\"120\"> <div align=\"center\"><font class=\"plnTxtBl\"> "); print ("$pat_ssn"); ?></font></div></td>
              <td width="97"> <div align="center"><font class="plnTxtBl"> <? print ("$dob"); ?></font></div></td>
              <td width="129"><div align="left"><font class="plnTxtBl"> <? print ("$admit_visit_loc");

										   print (" ");

										   print"Room #: ";

										   print ("$admit_room_no");

										   ?> </font> </div></td>
              <td width="110"> <div align="left"><font class="plnTxtBl"> <?

														if ($phy_id != 0)
														{
															$phyidquery="SELECT phy_fname, phy_lname FROM phy_det WHERE phy_id='$phy_id'";
															$resultphyidquery=mysql_db_query("$database[dbname]", $phyidquery);
															$rowphy_id=mysql_fetch_array($resultphyidquery);

															$phy_fname=$rowphy_id["phy_fname"];

															$phy_lname=$rowphy_id["phy_lname"];
														}
														else
														{

															$phy_fname=" ";
															$phy_lname=" ";

														}
              												print ("$phy_fname"); print "  "; print("$phy_lname");

              												?></font></div></td>
              </tr>
              <?

		}

		while($row = mysql_fetch_array($result));

	}

}

?>

 

the filters for this query are:

 

                <td rowspan="3" align="left" valign="top" width="498"> <form method="post" action="AdmitPatsearch.php" name="Form" onSubmit="return validate()">
                    <table width="497" border="0" cellspacing="0" cellpadding="0">
                      <tr>
                        <td height="14" width="232"> </td>
                        <td height="14" width="265"> </td>
                      </tr>
                      <tr>
                        <td height="14" width="232"> </td>
                        <td height="14" width="265"> </td>
                      </tr>
                      <tr>
                        <td height="22"> <div align="right"><font class="inputLbl">First
                            Name:    </font></div></td>
                        <td height="22" width="265"> <input class="txtboxLarge" type="text" name="patfname"  value="<? if (isset ($patfname)){ print ("$patfname"); } ?>" onBlur="javascript:changeCase(document.Form.patfname);" >
                        </td>
                      </tr>
                      <tr>
                        <td height="22"> <div align="right"><font class="inputLbl">Last
                            Name:    </font></div></td>
                        <td height="22" width="265"><font face="Verdana, Arial, Helvetica, sans-serif" size="2">
                          <input class="txtboxLarge" type="text" name="patlname" value="<? if (isset($patlname)){ print ("$patlname"); } ?>" onBlur="javascript:changeCase(document.Form.patlname);" >
                          </font></td>
                      </tr>
                      <tr>
                        <td height="22"><div align="right"><font class="inputLbl">Location:
                              </font></div></td>
                        <td height="22"><font face="Verdana, Arial, Helvetica, sans-serif" size="2">
                          <input name="location" type="text" class="txtboxLarge" id="location" value="<? if (isset($location)){ print ("$location"); } ?>" onBlur="javascript:changeCase(document.Form.location);" >
                          </font></td>
                      </tr>
                      <tr>
                        <td height="22"><div align="right"><font class="inputLbl">DOB as (mm/dd/yyyy):   </font></div></td>
                        <td height="22"><input name="patdob" type="text" class="txtboxLarge" id="patdob" value="<? if (isset($patdob)) { print ("$patdob"); } ?>" ></td>
                      </tr>
                      <tr>
                        <td width="283"> <div align="right"><font class="inputLbl">
                            <input type="hidden" name="client_id" value="<? print ("1"); ?>">
                            </font></div></td>
                        <td colspan="2"> </td>
                      </tr>
                      <tr>
                        <td height="46" width="232"> </td>
                        <td height="46" width="265"><font face="Verdana, Arial, Helvetica, sans-serif" size="2">						 
					  <input name="searchon" type="hidden" id="searchon" value="Yes">
                          <input class="sbttn" type="submit" name="Submit" value="Search">
                          </font></td>
                      </tr>
                    </table>

 

this part works fine. what i need to do now is to add another filter - preferred provider which is a drop down box with values from the database table. user should be able to select a provider and then hit search on the screen to be able to display a list like the one above.

 

i want to add the following code to the filters part(before the Search button code) so user can be able to select a value from this drop down and then search.

 

					 <tr>
					<td height="22"> <div align="right"><font class="inputLbl">Preferred Provider:  </font></div></td>
					<td colspan="2">
					<?PHP

							$query="select phy_id, phy_fname, phy_lname from phy_det where phy_disabled = 'Enable' order by phy_lname, phy_fname";
							$result = mysql_query ($query);
							 if ($result)
							 {
								print ("<select name='physician'>\n");
								print ("<option value=\"\"></option>\n");
								while($row = mysql_fetch_array($result))
								{
									if($pat_phy_id == $row["phy_id"])
									{

										print ('<option selected value="'.$row["phy_id"].'">'.$row["phy_lname"].', '.$row["phy_fname"].'</option>');											
									}
									else
									{

									    print ('<option value="'.$row["phy_id"].'">'.$row["phy_lname"].', '.$row["phy_fname"].'</option>');
									}

								}
								print ("</select>");
							 }


					?>
			     	</td>
			      </tr>

 

how can i do this. the query should also include the preferred provider filter in it.

 

i am stuck here and am not able to proceed further with this one... all other filters on screen are just text boxes!

 

Link to comment
https://forums.phpfreaks.com/topic/47768-solved-problem-with-drop-down-filter/
Share on other sites
boo_lolly Regular Member

boo_lolly

Posted
Posted

you've got a lot of code to dig through and it's not very organised, so here's how i do mine...

 

here's my search form:

<?php
        /*input couple's first/last name*/
        echo "<form method=\"POST\" action=\"admin1.php?action=simplesearch\">\n".
             "<table border=\"0\">\n".
             "\t<tr><td width=\"125\" valign=\"top\" align=\"right\">First:</td><td><input type=\"text\" name=\"fname\" size=\"48\" maxlength=\"35\"></td></tr>\n".
             "\t<tr><td width=\"125\" valign=\"top\" align=\"right\">Last:</td><td><input type=\"text\" name=\"lname\" size=\"48\" maxlength=\"35\"></td></tr>\n".
             "\t<tr><td width=\"125\" valign=\"top\" align=\"right\">Event:</td>\n".
             "\t\t<td>\n";

        /*begin event date form table inside this cell*/
        echo "<table>\n".
             "<tr><td>";

        /*set month array for [foreach] loop*/
        $month_array = array("01" => "January", "02" => "February", "03" => "March",
                             "04" => "April", "05" => "May", "06" => "June",
                             "07" => "July", "08" => "August", "09" => "September",
                             "10" => "October", "11" => "November", "12" => "December");

        echo "<SELECT NAME=\"event_month\">\n".
             "<OPTION VALUE=\"\">select a month\n";

        /*populate month dropdown menu*/
        foreach($month_array as $key => $val){
                echo "<OPTION VALUE=\"". $key ."\">". $val ."\n";
        }
        echo "</SELECT>\n".
             "</td>\n<td>\n";


        /*set days array for [foreach] loop*/
        $days = range(1, 31);
        echo "<SELECT NAME=\"event_day\">\n".
             "<OPTION VALUE=\"\">select a day\n";

        /*populate day dropdown menu*/
        foreach($days as $day){
        /*if day is not 2 digit, prepend a '0'*/
                if($day >= 1 && $day <= 9){
                        $day = "0". $day;
                }
                echo "<OPTION VALUE=\"". $day ."\">". $day ."\n";
        }
        echo "</SELECT>\n".
             "</td>\n<td>\n";

        /*set years array for [foreach] loop*/
        $years = range(2006, 2015);
        echo "<SELECT NAME=\"event_year\">\n".
             "<OPTION VALUE=\"\">select a year\n";

        /*populate years dropdown menu*/
        foreach($years as $year){
                echo "<OPTION VALUE=\"". $year ."\">". $year ."\n";
        }
        echo "</SELECT>";

        /*end event date table within cell*/
        echo "</td>\n".
             "</tr>\n".
             "</table>";

        echo "\t\t</td>\n".
             "\t</tr>\n".
             "\t<tr>\n".
             "\t\t<td colspan=\"2\" align=\"center\">\n".
             "\t\t<input type=\"SUBMIT\" value=\"Search\" width=\"35\">\n";

        /*rename $_POST variables*/
        $fname = $_POST['fname'];
        $lname = $_POST['lname'];
        $event_day = $_POST['event_day'];
        $event_month = $_POST['event_month'];
        $event_year = $_POST['event_year'];

        echo "\t\t</form>\n".
             "\t\t</td>".
             "\t</tr>".
             "</table>";
?>

 

 

then here's how i build my query with the filters:

<?php
       /*
        *if the "Search" button is clicked
        *check to see if there is anything in the input fields
        */
        if($action == "simplesearch"){
                if(empty($lname) && empty($fname) && empty($event_day) && empty($event_month) && empty($event_year)){
                        echo "<FONT COLOR=\"FF0000\">You must enter information in at least one input field. Please try again.</FONT>\n";
                        die;
                }else{
       /*
        *checks to see which input fields had data
        *then depending on the input field
        *enter in the correct sql query for that input field
        *into a new index in the array $query_array
        */
                        $query_array = array();
                        if(!empty($lname)){
                                $query_array[] = "brideLname LIKE '%". $lname ."%' OR groomLname LIKE '%". $lname ."%'";
                        }
                        if(!empty($fname)){
                                $query_array[] = "brideFname LIKE '%". $fname ."%' OR groomFname LIKE '%". $fname ."%'";
                        }
                        if(!empty($event_day)){
                                $query_array[] = "event_day = '". $event_day ."'";
                        }
                        if(!empty($event_month)){
                                $query_array[] = "event_month = '". $event_month ."'";
                        }
                        if(!empty($event_year)){
                                $query_array[] = "event_year = '". $event_year ."'";
                        }

       /*
        *$query string becomes all the sql queries
        *with 'AND' in between them
        */
                        $query_string = implode(" AND ", $query_array);

                        /*check sql query string*/
                        #echo $query_string ."<br />\n";

                        $result = mysql_query("SELECT * FROM my_search_table WHERE ". $query_string ."") OR die(mysql_error());
                }
?>

 

i hope that helps/makes sense.

rsammy Regular Member

rsammy

Posted
  • Author
Posted

thanx for response buddy! but, my drop-down filter is filled with options from the database(phy_det table in the database). what you have shown up here is filling up the option box with hardcoded options. i want something where i can populate the filter field(Provider) with options from the phy_det table (phy_lname, phy_fname). i am able to concatenate the two fields (phy_lname and phy_fname) and display them. i need to be able to choose one of these names and forward the id(phy_id) of that name so it can be picked up from the query. also, i need to add this condition(filter) in the query!

 

hope my explanation is clear.

boo_lolly Regular Member

boo_lolly

Posted
Posted

that's simple... just replace my 'hard-coded' options with your database query results. the important thing to take note of is the value="" inside your option tag. that is the value that will be passed to the $_POST array when you submit your search form. as you mentioned in your post, this would be where the id of that specific row would be placed.

 

for example:

<php
        echo "<SELECT NAME=\"your_menu\">\n".
             "<OPTION VALUE=\"\">select a month\n";

        /*populate dropdown menu from database*/
        $sql = "SELECT phy_id, phy_fname, phy_lname FROM phy_det";
        $query = mysql_query($sql);
        while($row = mysql_fetch_array($query)){
                echo "<option value=\"{$row['phy_id']}\">{$row['phy_fname']} {$row['phy_lname']}\n";
        }

        echo "</SELECT>\n".
?>

 

just like that. remember, whatever is inside value="" will be the information that is passed to your search filter. i assume you want it to be the id, but you can change it to be whatever you think it should be.

 

keep in mind that this is the easy part. the code i have provided that conducts the filter is where it may get a little tricky for you, so pay close attention to that part of the code and let me know if you need anymore help.

Archived

This topic is now archived and is closed to further replies.

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.