Solved

Multiple criteria php query

Posted on 2008-10-04
2
252 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

Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

Question has a verified solution.

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

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
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 count occurrences of each item in an array.

717 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