Solved

Multiple criteria php query

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

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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…

820 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