Multi-Textbox Search

Question

ngonix 0 Light Poster

11 Years Ago

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?

php

Edited 11 Years Ago by ngonix

3 Contributors
7 Replies
1K Views
18 Hours Discussion Span
Latest Post 11 Years Ago Latest Post by broj1

All 7 Replies

broj1 356 Humble servant

11 Years Ago

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.

Edited 11 Years Ago by broj1

broj1 356 Humble servant

11 Years Ago

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.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

ahmedhamdy 0 Light Poster · Answer 1 · 2013-02-25T16:44:59+00:00

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());
}

ngonix 0 Light Poster · Answer 2 · 2013-02-26T06:38:22+00:00

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 356 Humble servant Featured Poster · Answer 3 · 2013-02-26T09:04:44+00:00

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 0 Light Poster · Answer 4 · 2013-02-26T09:25:41+00:00

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 356 Humble servant Featured Poster · Answer 5 · 2013-02-26T09:33:44+00:00

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

Multi-Textbox Search

Recommended Answers Collapse Answers

All 7 Replies

Recommended Answers