Solved

Multiple criteria php query

Posted on 2008-10-04
2
251 Views
Last Modified: 2013-12-12
I need a form that allows users to search for healthcare providers by state,up to 3 services, and insurance company. I would like the form to have a drop down box that list the options. I have attached the form I have built with the code I have worked on with help from other solutions in the forum. I get the below error :

Table '3071_snf.Skilled_Nursing_Facilities' doesn't exist

If the selection criteria field is null I would like for it to be ignored in the search. I am a beginner so much of what I have done is from trial and error. What am I missing here?
<html>
<title>
 
</title>
<body>
<table border="0" height="273" width="932"><font size="3" color="#00ffff">
  <tbody><form name="frm" action="" method="post">  
 
 <tr>
    <td><font size="3" color="#00ffff">* 
      State to 
      Search:                             
      <select name="state1"> <option value="" selected="">[Select State]<option value="NN" state_nn="">Non US<option value="AL" state_al="">Alabama<option value="AK" state_ak="">Alaska<option value="AZ" state_az="">Arizona<option value="AR" state_ar="">Arkansas<option value="CA" state_ca="">California<option value="CO" state_co="">Colorado<option value="CT" state_ct="">Connecticut<option value="DE" state_de="">Delaware<option value="FL" state_fl="">Florida<option value="GA" state_ga="">Georgia<option value="HI" state_hi="">Hawaii<option id="" value="ID" state_="">Idaho<option value="IL" state_il="">Illinois<option value="IN" in="" state_="">Indiana<option value="IA" state_ia="">Iowa<option value="KS" state_ks="">Kansas<option value="KY" state_ky="">Kentucky<option value="LA" state_la="">Louisiana<option value="ME" state_me="">Maine<option value="MD" state_md="">Maryland<option value="MA" state_ma="">Massachusetts<option value="MI" state_mi="">Michigan<option value="MN" state_mn="">Minnesota<option value="MS" state_ms="">Mississippi<option value="MO" state_mo="">Missouri<option value="MT" state_mt="">Montana<option value="NE" state_ne="">Nebraska<option value="NV" state_nv="">Nevada<option value="NH" state_nh="">New 
        Hampshire<option value="NJ" state_nj="">New Jersey<option value="NM" state_nm="">New Mexico<option value="NY" state_ny="">New York<option value="NC" state_nc="">North Carolina<option value="ND" state_nd="">North 
        Dakota<option value="OH" state_oh="">Ohio<option value="OK" state_ok="">Oklahoma<option value="OR" state_or="">Oregon<option value="PA" state_pa="">Pennsylvania<option value="PR" state_pr="">Puerto Rico<option value="RI" state_ri="">Rhode Island<option value="SC" state_sc="">South 
        Carolina<option value="SD" state_sd="">South Dakota<option value="TN" state_tn="">Tennessee<option value="TX" state_tx="">Texas<option value="UT" state_ut="">Utah<option value="VT" state_vt="">Vermont<option value="VA" state_va="">Virginia<option value="WA" state_wa="">Washington<option value="DC" state_dc="">Washington D.C.<option value="WV" state_wv="">West 
        Virginia<option value="WI" state_wi="">Wisconsin<option value="WY" state_wy="">Wyoming<option value="NN" state_nn="">Non 
      US</option></select>   </font></td>
    </tr>
 <tr>
    <td><font size="3" color="#00ffff">* 
      Select up to 3 Services Needed:    <select name="Service 1"> <option value="" selected="">[Select Services]<option value="PT" service_pt="">Physical Therapy<option value="OT" service_ot="">Occupational Therapy<option value="ST" service_st="">Speech Therapy<option value="RT" service='rt""'>Respiratory Therapy<option value="WC" service_wc="">Wound Care<option value="NP" service_np="">Negative Pressure Dressing<option value="OX" service_ox="">Oxygen<option value="TR" service_tr="">Tracheostomy<option value="VT" service_vt="">Ventilator<option value="AH" service_ah="">Aids/HIV<option value="HD" service_hd="">Hemodialysis<option value="PD" service_pd="">Peritoneal Dialysis<option value="CT" service_ct="">Chemotherapy 
        <option value="RD" service_rd="">Radiation Therapy<option value="AB" service_ab="">IV Antibiotics<option value="OM" service_om="">Other Medication</option></select><select name="Service 2"> <option value="" selected="">[Select Service]<option value="PT" service_pt="">Physical Therapy<option value="OT" service_ot="">Occupational Therapy<option value="ST" service_st="">Speech Therapy<option value="RT" service='rt""'>Respiratory Therapy<option value="WC" service_wc="">Wound Care<option value="NP" service_np="">Negative Pressure Dressing<option value="OX" service_ox="">Oxygen<option value="TR" service_tr="">Tracheostomy<option value="VT" service_vt="">Ventilator<option value="AH" service_ah="">Aids/HIV<option value="HD" service_hd="">Hemodialysis<option value="PD" service_pd="">Peritoneal Dialysis<option value="CT" service_ct="">Chemotherapy 
        <option value="RD" service_rd="">Radiation Therapy<option value="AB" service_ab="">IV Antibiotics<option value="OM" service_om="">Other Medication</option></select><select name="Service 3"> <option value="" selected="">[Select Service]<option value="PT" service_pt="">Physical Therapy<option value="OT" service_ot="">Occupational Therapy<option value="ST" service_st="">Speech Therapy<option value="RT" service='rt""'>Respiratory Therapy<option value="WC" service_wc="">Wound Care<option value="NP" service_np="">Negative Pressure Dressing<option value="OX" service_ox="">Oxygen<option value="TR" service_tr="">Tracheostomy<option value="VT" service_vt="">Ventilator<option value="AH" service_ah="">Aids/HIV<option value="HD" service_hd="">Hemodialysis<option value="PD" service_pd="">Peritoneal Dialysis<option value="CT" service_ct="">Chemotherapy 
        <option value="RD" service_rd="">Radiation Therapy<option value="AB" service_ab="">IV Antibiotics<option value="OM" service_om="">Other Medication</option></select>      </font></td></td></tr>
	</td>
	<td><font size="3" color="#00ffff">Select Primary Payor:                       
	<select name="Payor"> <option value="" selected="">[Select Payor]<option value="MC" payor_mc="">Medicare<option value="MD" payor_md="">Medicaid<option value="BC" payor_bc="">Blue Cross Blue Shield<option value="CG" payor_cg="">Cigna 
        <option value="AT" payor_at="">Aetna<option value="UH" payor_uh="">United Health Care<option value="WC" payor_wc="">Workers Compensation<option value="TR" Payor_tr="">Tricare<option value="HM" payor_hm="">Humana<option value="MA" payor_ma="">Medicare Advantage Plan<option value="OP" payor_op="">Other Payor</option></select>
	  </font></td></tr><br><br></option></select></font></tbody></table>
 
<input type="submit" value="Submit">
</form><?
//establishes connection to database
$db = mysql_pconnect("localhost", "username", "password");
 
mysql_select_db("3071_snf");
 
if ( !$db)
 
{
      echo "Could not connect to database.";
      die(mysql_error());
}
 
/*
sets empty variable then tests to see if search criteria is
null.  if criteria is null then unsets variable.  end result is
only criteria specified by user.
*/
$search ='';
 
if ( empty($state1) ) {
unset($state);}
else{
$search = $search . " and State='$state1'";}
 
if ( empty($service1) ) {
unset($service1);}
else{
$search = $search . " and Service1='$service1'";}
 
if ( empty($Service2) ) {
unset($service2);}
else{
$search = $search . " and Service2='$Service2'";}
 
if ( empty($service3) ) {
unset($service3);}
else{
$search = $search . " and Service3='$Service3'";}
 
if ( empty($payor) ) {
unset($payor);}
else{
$search = $search . " and Payor='$payor'";}
 
 
$query= "SELECT * from Skilled_Nursing_Facilities where 'Name' > 0" . $search;
 
 
 
$result = mysql_query($query)
or die(mysql_error());
mysql_query("COMMIT");
 
$num_result = mysql_num_rows($result);
 
echo "<p><h2>Number of records found:  " . $num_result . "</h2></p>";
 
/*
Sets variable to 0 then specifies to run the loop while varialbe
less than the number of rows returned from query.  Then
increments the variable by one.
*/
for ($i=0; $i < $num_result; $i++)
{
   //puts array of results in variable
   $row = mysql_fetch_array($result);
   echo "<p><b>Providers Name:</b>  " . $row["Name"] . "<br>";
   echo "<b>State:  </b>" . $row["State"] . "<br>";
   echo "<b>Service 1:  </b>" . $row["Service1"] . "<br>";
   echo "<b>Service 2: </b>" . $row["Service2"] . "<br>";
   echo "<b>Service 3:  </b>" . $row["Service3"] . "<br>";
   echo "<b>Payor:  </b>" . $row["Payor"] . "<br>";
   echo "<b>Phone:  </b>" . $row["Phone"] . "</p>";
}
?>
</body>
</html>

Open in new window

0
Comment
Question by:hughe1dl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 17

Accepted Solution

by:
nanharbison earned 250 total points
ID: 22642846
if you get the message that the table doesn't exist, you have a typo or something else wrong. Make sure you are using the correct capitalization on the name of the table. Maybe is isn't  Skilled_Nursing_Facilities, but is Skilled_nursing_facilities or something? Go into your database and check. I try to always copy and paste names of tables and fields in the database because this is a common mistake.
0
 
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 250 total points
ID: 22645343
You can run something like the code snippet (it will need a little bit of adaptation for DB connectivity, etc) to find the true names of the tables you are working with.  That should help clear up any misspellings or similar errors.  HTH, ~Ray
<?php
/* ************************************************************************** */
function get_my_db_tables(){
global $db_connection, $my_dbt_prefix;
	$sql_t	= "SHOW TABLES";
	if (!$res_t = mysql_query("$sql_t", $db_connection)) { fatal_query_error($sql_t); }
	if (mysql_num_rows($res_t) == 0) {
return false; }
	while ($show_tables	= mysql_fetch_array($res_t)) {
		$my_tables[]	= $show_tables[0];
	}
return ($my_tables);
}
/* ************************************************************************** */
 
$my_db_tables = get_my_db_tables();
var_dump($my_db_tables);

Open in new window

0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question