Problem passing values from a multi-field search page to the MySQL statement

[jej1216]

I am trying to put together a PHP search page in which the user can select none, one, two, or three fields to search, and then the results php will build the SQL with dynamic where caluses to reflect the fields chosen.

The first page, where they select the search fields and submit:

<?php
$db = mysql_connect("localhost", "root", "yeahright");
if (!$db)
   {
   die('Could not connect:'.mysql_error);
   }
mysql_select_db("mytest",$db);
$sql = "SELECT distinct fac_id FROM incidents ORDER BY fac_id";
$result = mysql_query($sql);
if (!$result)
    {
    die('Error: '.mysql_error());
    }
$sql2 = "SELECT distinct person_type FROM incidents ORDER BY person_type";
$result2 = mysql_query($sql2);
if (!$result2)
    {
    die('Error: '.mysql_error());
    }
$sql3 = "SELECT distinct severity FROM incidents ORDER BY severity";
$result3 = mysql_query($sql3);
if (!$result3)
    {
    die('Error: '.mysql_error());
    }
mysql_close($db);
?>
 

and

<FORM METHOD="POST" ACTION="ir_multiquery.php">
<p>Search existing Incident Reports by selecting one of the search fields below:</p>
<select name="fac_id" size="1">
<option selected value="">Facility</option>
 

<?php
while ($myrow = mysql_fetch_array($result))
   {
   echo "<option value='fac_id'>".$myrow["fac_id"]."</option>\n";
   }
?>
 

</select>
<select name="person_type" size="1">
<option selected value="">Person Type</option>
 

<?php
while ($myrow2 = mysql_fetch_array($result2))
   {
   echo "<option value='person_type'>".$myrow2["person_type"]."</option>\n";
   }
?>
 

</select>
<select name="severity" size="1">
<option selected value="">Severity Level</option>
 

<?php
while ($myrow3 = mysql_fetch_array($result3))
   {
   echo "<option value='severity'>".$myrow3["severity"]."</option>\n";
   }
?>
 

</select>
<p>
<input type="submit" value="Search" />
</p>
</FORM>
 

ir_multiquery.php:

<?php
$db = mysql_connect("localhost", "root", "yeahright");
if (!$db)
   {
   die('Could not connect:'.mysql_error);
   }
mysql_query("mytest", $db);
// The basic SELECT statement
$select = 'SELECT fac_id';
$from = ' FROM incidents';
$where = ' WHERE 1=1';
// If facility was selected
$facility = $_REQUEST["fac_id"];
if ($facility != '') {
echo "REQUEST Facility: ";
echo $_REQUEST["fac_id"];
echo " ";
$where .= ' AND fac_id='.$_REQUEST["fac_id"].'';
//$where .= " AND fac_id='000955'";
}
// If person type was selected
$persontype = $_REQUEST["person_type"];
if ($persontype != '') {
echo "Persontype: ";
echo $_REQUEST["person_type"];
$where .= ' AND person_type='.$_REQUEST["person_type"].'';
}
// If severity was selected
$severity = $_REQUEST["severity"];
if ($severity != '') {
echo "Severity: ";
$_REQUEST["severity"];
$where .= ' AND severity='.$_REQUEST["severity"].'';
}
echo " Final Select: ";
echo $select;
echo $from;
echo $where;
?>
 

and

<table>
<tr><th>Query</th><th>Results</th></tr>
 

<?php
$query  = sprintf($select . $from . $where);
echo 'query: ';
echo $query;
if (!$query)
   {
   die('Could not run query:'.mysql_error);
   }
$result = mysql_query($query);
echo '<p>result: ';
echo $result;
echo '</p>';
if (!$result) {
echo '</table>';
exit('<p>Error retrieving data from db - this is from the $result var<br />'.
'Error: ' . mysql_error() . '</p>');
}
else
{
echo '<p>Eureka!</p>';
}
while ($myrow = mysql_fetch_array($result))
{
echo "<tr valign='top'>\n";
$facility = $myrow['fac_id'];
echo "<td>$facility</td>\n";
echo "</tr>\n";
}
mysql_close($db);
?>
 

</table>
 

When I run this, I get the following:

The echo of $query shows that the three values I am trying to pass do not get passed - for example, if I select Facility it shows
SELECT fac_id FROM jos_incidents_combined WHERE 1=1 AND fac_id=fac_id

This of course causes the $result to be blank.

I then get the error:
Error retrieving data from db - this is from the $result var
Error: No database selected

I have checked and rechecked my code for the db connection, and it is functioning. Also, when I paste the $query in phpmyadmin without the AND part, it runs successfully.

Is the problem just the fact that I'm not passing the values or does the 'No database selected' indicate a second problem as well?

What changes do I need to do to my code to get the variables passed?

Thanks - I apologize for the large content - but I have re-read some of the PHP materials and tried a few changes but nothing seems to work.

TIA,

jej1216

[ronnil]

sounds like you are missing a mysql_select_db('db_name'); statement, but you're not. :)

You say that if you select facility it returns 0 rows? is that with other options than the hardcoded one? (with value=""), you shouldn't be getting any result if it's that one. (since fac_id in the query will then be "")

Why do you use 1=1 in your SQL statement?

[jej1216]

sounds like you are missing a mysql_select_db('db_name'); statement

Well, I feel like an idiot. That's exactly what was missing.

Thanks,

jej1216

[ronnil]

lol... think it's getting late here... you got two files :P

well... congrats on solving the problem :)

[jej1216]

It definitely was late. The missing a mysql_select_db('db_name') got rid of the error about the database, but it's still not passing the variables from the first php page to the second.

I get this as the resulting SQL statement:
"query: SELECT fac_id, room_descr, person_type, severity FROM jos_incidents_combined WHERE 1=1 AND fac_id=fac_id"

It's probably a quote thing, but why isn't the variable from the first php page not getting passed?

Also, I use WHERE 1=1 so the other AND statements can be in any combination. Apparently, WHERE 1=1 is a way to run the query whether the user picks none, one, two, or three search variables.

[jej1216]

I've almost got it -- I changed to the following code in the 1st php:

while ($myrow = mysql_fetch_array($result))
   {
   echo "<option value='$myrow[fac_id]'>".$myrow[fac_id]."</option>\n";
   }
?>
 

For fac_id, which is numeric, this works - the variable is passed and the query results are correct.

Now I have a new error. When the variable to be passed is character, I need to know how to change the following code to enclose it in quotes for the select statement:

<?php
while ($myrow2 = mysql_fetch_array($result2))
   {
   echo "<option value='$myrow2[person_type]'>".$myrow2[person_type]."</option>\n";
   }
?>
 

This code returns this SQL:
SELECT fac_id, room_descr, person_type, severity FROM incidents WHERE 1=1 AND person_type =Outpatient
and this message:
"Error: Unknown column 'Outpatient' in 'where clause'"

How do I get the php code to enclose the value "Outpatient?"

thanks,

jej1216

[jej1216]

Since this is a new problem, I have started a new thread for how to add single quotes to a passed value.

Thanks,

jej1216