Solved

Multiple criteria php query

Posted on 2008-10-04
2
246 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
2 Comments
 
LVL 17

Accepted Solution

by:
nanharbison earned 250 total points
Comment Utility
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 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 250 total points
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article discusses four methods for overlaying images in a container on a web page
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
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.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now