?
Solved

Multiple criteria php query

Posted on 2008-10-04
2
Medium Priority
?
255 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 750 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 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 750 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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

This article discusses four methods for overlaying images in a container on a web page
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
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.
Suggested Courses

770 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