Multi-Textbox Search

[ngonix]

Hello, I have a php script with 3 textboxes that is supposed to search for and retrieve records using the following criteria:
Policy Number, First Name and Last Name.

The code is as follows:

search_page.php

 <table width="1076" align="center" bgcolor="#eee">
   <tr>
    <td height="10" valign="top">

    <p>
    <div align="center">
    <form id="form1" name="form1" method="post" action="search_page.php">
Policy No.:
<input name="policyNum" type="text" id="policyNum" size="20" value="<?php echo stripcslashes($_REQUEST["policyNum"]); ?>" />
Surname:
<input name="surname" type="text" id="surname" size="20" value="<?php echo stripcslashes($_REQUEST["surname"]); ?>"/>
 First Name:
<input name="name" type="text" id="name" size="20" value="<?php echo stripcslashes($_REQUEST["name"]); ?>"/>
&nbsp; 
<input type="submit" name="button" id="button" value="Search Filter" />
  </label>
  <a href="search_page.php"> 
  reset</a>
    </form>
<br /><br />


<table width="700" border="1" cellspacing="0" cellpadding="4">
  <tr>
    <td width="90" bgcolor="#CCCCCC"><strong>Policy No.</strong></td>
    <td width="95" bgcolor="#CCCCCC"><strong>Name</strong></td>
    <td width="159" bgcolor="#CCCCCC"><strong>Surname</strong></td>

  </tr>
<?php
if ($_REQUEST["policyNum"]<>'') {
    $search_policy = " AND (policyNumber LIKE '%".mysql_real_escape_string($_REQUEST["policyNum"])."%')"; 
}

if ($_REQUEST["surname"]<>'') {
    $search_surname = " AND (lastName LIKE '%".mysql_real_escape_string($_REQUEST["surname"])."%')"; 
}

if ($_REQUEST["name"]<>'') {
    $search_name = " AND (firstName LIKE '%".mysql_real_escape_string($_REQUEST["name"])."%')"; 
}

else {
    $sql = "SELECT * FROM tblclients WHERE clientID > 0".$search_policy.$search_surname.$search_name;
}

$sql_result = mysql_query($sql) or die (mysql_error());
if (mysql_num_rows($sql_result)>0) {
    while ($row = mysql_fetch_assoc($sql_result)) {
?>
  <tr>
    <td><?php echo $row["policyNumber"]; ?></td>
    <td><?php echo $row["firstName"]; ?></td>
    <td><?php echo $row["lastName"]; ?></td>

  </tr>
<?php
    }
} else {
?>
<tr><td colspan="5">No results found.</td>
<?php    
}
?>
</table>

and the config.php

<?php


    $con = mysql_connect('localhost', 'root', '');
    if (!$con) {
        die('Could not connect: ' . mysql_error());
    }

    mysql_select_db("underwrite", $con);


?>

My problem is that i can retrieve the records using Policy No. and Surname textboxes, but i cannot do so using First Name textbox and it displays the error "Query was empty". I have all the data in my database and have double checked the names of the database fields but to no avail. What could be wrong?

[ahmedhamdy]

because you make sql statement into else statement

if ($_REQUEST["name"]<>'') {
    $search_name = " AND (firstName LIKE '%".mysql_real_escape_string($_REQUEST["name"])."%')"; 
}
else {
    $sql = "SELECT * FROM tblclients WHERE clientID > 0".$search_policy.$search_surname.$search_name;
}

sql statement excute only when condition if be false in

if ($_REQUEST["name"]<>'')

correct is :

if ($_REQUEST["policyNum"]<>'' && $_REQUEST["surname"]<>'' && $_REQUEST["name"] <> '' ){

               $search_policy = " AND (policyNumber
                         LIKE'%".mysql_real_escape_string($_REQUEST["policyNum"])."%')"; 

               $search_surname = " AND (lastName LIKE
                               '%".mysql_real_escape_string($_REQUEST["surname"])."%')"; 

               $search_name = " AND (firstName LIKE
                               '%".mysql_real_escape_string($_REQUEST["name"])."%')";

             $sql = "SELECT * FROM tblclients  WHERE clientID >
                                0".$search_policy.$search_surname.$search_name;

                                $sql_result = mysql_query($sql) or die (mysql_error());
}

[broj1]

Another way of doing it:

Frst initialize the variables to empty string:

$search_policy = '';
$search_surname = '';
$search_name = '';

Then you assign a condition to any of the variables if $_POST element exists, as you have done it.

On lines 43 to 45 chremove the else bit from:

else {
    $sql = "SELECT * FROM tblclients WHERE clientID > 0".$search_policy.$search_surname.$search_name;
}

to:

$sql = "SELECT * FROM tblclients WHERE clientID > 0".$search_policy.$search_surname.$search_name;

That is how the conditions or empty strings will be added to the query.

@ahmedhamdy: your solution will work only if all three boxes have been filled-in and I am not sure if ngonix wants that.

[ngonix]

Well nw it is working the way i want. It's returning data even if i dont fill in all textboxes which is wat i want. Now another small issue is that i do not want the table to show by default & i want it to show only after i enter values in textboxes and press the search button. Pagination would be nice too. How do i go about it?

[broj1]

Now another small issue is that i do not want the table to show by default & i want it to show only after i enter values in textboxes and press the search button

You can achieve that by placing the whole php block on the beginning and wrapping it in aanother if condition checking for whether the button has been pressed. Within that block you construct the table.

I will try to prepare a code and show it in next post so you can experiment on your own in the mean time.

[broj1]

Here is corrected / rearranged code. Please see the comments within the code.

<?php 
// !!!!!!!!!
// this is my stuff to connect to the DB
// please use your DB connection code here
include '../common/connect.php';
$link = dbConnect();

// variables for filling-in the form fields
$policyNum = '';
$surname = '';
$name = '';

// changed the $_REQUEST to $_POST to avoid possible clashes if you decided to
// also use $_GET in future
// OK; lest's check if the user clicked on submit button
// please note name and ID of the button were changed to 'submit' to avoid confusion
if(isset($_POST['submit'])) {

    // initialize variables that represent conditions
    $search_policy = '';
    $search_surname = '';
    $search_name = '';    

    // isset function is also used in checking for the existence of data in $_POST
    if (isset($_POST["policyNum"]) && $_POST["policyNum"] != '') {
        $policyNum = mysql_real_escape_string($_POST["policyNum"]);
        $search_policy = " AND (policyNumber LIKE '%$policyNum%')";
    }

    if (isset($_POST["surname"]) && $_POST["surname"] != '') {
        $surname = mysql_real_escape_string($_POST["surname"]);
        $search_surname = " AND (lastName LIKE '$surname')";
    }

    if (isset($_POST["name"]) && $_POST["name"] != '') {
        $name = mysql_real_escape_string($_POST["name"]);
        $search_name = " AND (firstName LIKE '%$name%')";
    }

    $sql = "SELECT * FROM tblclients WHERE clientID > 0".$search_policy.$search_surname.$search_name;   

    $sql_result = mysql_query($sql) or die (mysql_error());

    // now echo the code for the table heading
    // echo table head
    echo '<table width="700" border="1" cellspacing="0" cellpadding="4">';
    echo '<tr><td width="90" bgcolor="#CCCCCC"><strong>Policy No.</strong></td>';
    echo '<td width="95" bgcolor="#CCCCCC"><strong>Name</strong></td>';
    echo '<td width="159" bgcolor="#CCCCCC"><strong>Surname</strong></td></tr>';

    // if there are any rows, echo each of them
    if (mysql_num_rows($sql_result)>0) {

        while ($row = mysql_fetch_assoc($sql_result)) {

            echo '<tr>';
            echo "<td>{$row['policyNumber']}</td>";
            echo "<td>{$row['firstName']}</td>";
            echo "<td>{$row['lastName']}</td>";
            echo '</tr>';
        }

    // if no rows were found, echo a row with the message
    } else {

        echo '<tr><td colspan="5">No results found.</td></tr>';
    }

    // echo the closing table tag
    echo '</table>';
}
?>

<!--  
The code for table with the form had issues (missing end table, tr and div tags, missing a label start tag...)
I have corrected those I hope the layout is still what you wanted :-)
-->
<table width="1076" align="center" bgcolor="#eee">
   <tr>
    <td height="10" valign="top">
    <div align="center">
    <form id="form1" name="form1" method="post" action="search_page.php">
Policy No.:
<input name="policyNum" type="text" id="policyNum" size="20" value="<?php echo stripcslashes($policyNum); ?>" />
Surname:
<input name="surname" type="text" id="surname" size="20" value="<?php echo stripcslashes($surname); ?>"/>
 First Name:
<input name="name" type="text" id="name" size="20" value="<?php echo stripcslashes($name); ?>"/>
&nbsp; 
<input type="submit" name="submit" id="submit" value="Search Filter" />
  <a href="search_page.php"> 
  reset</a>
    </form>
<br /><br />
</div>
</tr>
</table>

[ngonix]

I had done it myself before you posted that last piece of code and it's now working fine. I couldn't have done it without your help. Thanks a lot man!

[broj1]

You are welcome. Please mark as solved if there are no questions. Happy coding.