Problem w/form+drop down menu after query by 3 fields

[Arl8]

Hey ya'll,

I have been writing script that has a form with three input boxes, let's call them LName, FName, and DOB.  What i'm trying to figure out is a way to search by any combination of the three, but you don't necessarily have to use each field.  Like i'd like to be able to search by LName and DOB, or all three, or just LName and FName.  Here's the scripts I have so far, omitting form code to make this faster.

[color=red]frmptinfo.php[/color] ==> Sets up first form, calls to second form, has 3 input fields, and works.  Just posting to be thorough.

[code]
<p> </p>
<form name="ptform" method="post" action="ptdisplay.php">
  <table width="250" border="0" align="center" cellpadding="3">
    <tr>
      <td colspan="2">
        <center><b>Enter Patient Info</b></center>
      </td>
    </tr>
    <tr >
      <td width="100">
        <center>Last Name</center>
      </td>
      <td width="150">
        <input type="text" name="LastName">
      </td>
    </tr>
    <tr >
      <td width="100">
        <center>First Name</center>
      </td>
      <td width="150">
        <input type="text" name="FirstName">
      </td>
    </tr>
    <tr >
      <td width="100">
        <center>DOB</center>
      </td>
      <td width="150">
        <input type="text" name="DOB">
      </td>
    </tr> 
    <tr>
      <td colspan="2" >
        <center>
          <input type="submit" name="Submit" value="Submit">
        </center>
      </td>
    </tr>
[/code]


[color=red]ptdisplay.php[/color] ==> Here is where my problems arise, just going to post PHP/SQL jazz.

[code]
<?php
require_once('odbc.php');

//$lname=$_REQUEST['LastName'];
//$fname=$_REQUEST['FirstName'];
//$dob=$_REQUEST['DOB'];


if ($lname=null){
do_nothing();}
else{
$lname=$_REQUEST['LastName'];}
if ($fname=null){
do_nothing();}
else{
$fname=$_REQUEST['FirstName'];}
if ($dob=null){
do_nothing();}
else{
$lname=$_REQUEST['DOB'];}
$query="SELECT * from tblPatient WHERE LastName='$lname' AND FirstName='$fname' AND DOB='$dob'";
$result=odbc_exec($odbc, $query) or die (odbc_errormsg());
$row=odbc_fetch_array($result);


?>
[/code]

Also, how would I make it so that when I search I don't have to write out the entire last or first name, yet it would still find the correct record from my database.  It's possible that a search could pull up more than one patient, and if so is there a way I can send that to a drop down menu to choose which patient it is?  Thanks for the help

[hitman6003]

Something like this should work:

[code]$query = "SELECT * from tblPatient";

if ( $_POST['lname'] != "") {
$where[] = "lname LIKE '%$lname%'";
}

if ( $_POST['fname'] != ""){
$where[] = "fname LIKE '%$fname%'";
}

if ($_POST['dob'] != ""){
$where[] = "dob LIKE '%$dob%'";
}

if (count($where) > 0) {
$query .= " WHERE " . implode(" AND ", $where);
}

$result = odbc_exec($odbc, $query) or die (odbc_errormsg());
$row = odbc_fetch_array($result);[/code]

[Arl8]

Thanks for the feedback.  You are awesome.

[hitman6003]

Make sure you do some checking on the user inputs to verify that they are valid input and not an attempt to cause harm to your database.

[Arl8]

Thanks, i checked the input and it's the way it should be.  I also use this script with some small modifications to make an edit/update script as well.

Quick question:

If I query and more than one record satisfies the query, how would I write that into a drop down menu where I can select which record I want, then go to my ptdisplay.php ?  Thanks again for the help.

[hitman6003]

[code]$query = "SELECT * from tblPatient";

if ( $_POST['lname'] != "") {
$where[] = "lname LIKE '%$lname%'";
}

if ( $_POST['fname'] != ""){
$where[] = "fname LIKE '%$fname%'";
}

if ($_POST['dob'] != ""){
$where[] = "dob LIKE '%$dob%'";
}

if (count($where) > 0) {
$query .= " WHERE " . implode(" AND ", $where);
}

$result = odbc_exec($odbc, $query) or die (odbc_errormsg());

if (odbc_num_rows($result) > 1) {
... form that submits to script that will pull the record for processing ...
echo '<select name="selectname" size="1">';
while ($row = odbc_fetch_row($result)) {
echo '<option value="' . $row['id'] . '">' . $row['lastname'] . ', ' . $row['firstname'] . ' - ' . $row['dob'] . '</option>';
}
echo '</select>';
} else {
$row = odbc_fetch_row($result);
... continue with previous script ...
}[/code]
 

[Arl8]

Ok.

I have the drop down menu on my screen now, but nothing shows in my form.  This is my code atm.

[color=red]ptdisplay.php[/color]

[code]
<?php
require_once('odbc.php');

$lname=$_REQUEST['LastName'];
$fname=$_REQUEST['FirstName'];
$dob=$_REQUEST['DOB'];

$query = "SELECT * from tblPatient";

if ( $_POST['LastName'] != "") {
$where[] = "LastName LIKE '%$lname%'";
}

if ( $_POST['FirstName'] != ""){
$where[] = "FirstName LIKE '%$fname%'";
}

if ($_POST['DOB'] != ""){
$where[] = "DOB LIKE '%$dob%'";
}

if (count($where) > 0) {
$query .= " WHERE " . implode(" AND ", $where);
}

$result = odbc_exec($odbc, $query) or die (odbc_errormsg());

if (odbc_num_rows($result) > 1) {
//... form that submits to script that will pull the record for processing ...
echo '<select name="selectname" size="1">';
while ($row = odbc_fetch_array($result)) {
echo '<option value="' . $row['PatientID'] . '">' . $row['LastName'] . ', ' . $row['FirstName'] . ' - ' . $row['DOB'] . '</option>';
}
echo '</select>';
} else {
$row = odbc_fetch_array($result);
            //...continue with previous script...

}

?>

<p> </p>
<form name="ptdisplay" method="post">
  <table width="1000" border="1" align="center" cellpadding="2">
    <tr>
      <td colspan="8">
        <div align="center"><b>Patient Information</b></div>
      </td>
    </tr>
<tr>
<td width="100">
<div align="right">LName </div>
</td>
<td width="150">
<input type="text" name="LastName" value="<? echo $row['LastName']; ?>">
</td>

.....
[/code]

Form continues from there and ends.  When you said continue with previous script, does that mean I need to put the form construction, etc in the else statement under there?  Or do they need to be in both places?  Slightly confused, and thanks again.

[Arl8]

Bump

[Arl8]

Clarifying,

With the drop down menu added, if I do a query that returns only one record, no data is displayed.  If a query returns more than one record, they appear in the drop down menu, but selecting one does not populate the form with the record data.  Any input?

[Arl8]

bump

[hitman6003]

[code]<?php
require_once('odbc.php');

if ($_POST) {
$lname=$_POST['LastName'];
$fname=$_POST['FirstName'];
$dob=$_POST['DOB'];

$query = "SELECT * from tblPatient";

if (isset($_POST['select']) {
$query .= " WHERE id = '" . $_POST['select'] . "'";
} else {
if ( $_POST['LastName'] != "") {
$where[] = "LastName LIKE '%$lname%'";
}

if ( $_POST['FirstName'] != ""){
$where[] = "FirstName LIKE '%$fname%'";
}

if ($_POST['DOB'] != ""){
$where[] = "DOB LIKE '%$dob%'";
}

if (count($where) > 0) {
$query .= " WHERE " . implode(" AND ", $where);
}
}

$result = odbc_exec($odbc, $query) or die (odbc_errormsg());

if (odbc_num_rows($result) > 1) {
$select = '<select name="select" size="1">';
while ($row = odbc_fetch_array($result)) {
$select .= '<option value="' . $row['PatientID'] . '">' . $row['LastName'] . ', ' . $row['FirstName'] . ' - ' . $row['DOB'] . '</option>';
}
$select .= '</select>';
} else {
$row = odbc_fetch_array($result);
}
}

?>

<form method="post">
<table width="1000" border="1" align="center" cellpadding="2">
<tr>
<td colspan="8"><div align="center"><b>Patient Information</b></div></td>
</tr>
<?php
if ($select != "") {
echo '
<tr>
<td>Please select a patient to edit:</td>
<td>' . $select . '</td>
</tr>
<tr>
<td colspan="8" style="text-align: center;"><input type="submit" name="submit" value="Display"></td>
</tr>';
} ?>
<tr>
<td width="100"><div align="right">LName</div></td>
<td width="150"><input type="text" name="LastName" value="<?php echo $row['LastName']; ?>"></td>
</tr>
... rest of form inputs in same format ...
</table>[/code]