multiple search field on form using php and mysql

Question

I stuck with this problem for a few days already. The logic of the system i want to develop is here

This is the code for the image

    <?php 
session_start();
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>Search</title>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <!-- <link rel="stylesheet" type="text/css" href="style.css"/> -->
</head>
<body>
    <form action="process/searchprocess.php" method="GET">
        <table width="100%">
            <tr>
                <th>
                    Client Ic<br><input type="text" name="client_name" />
                </th>
                <th>
                    Client Ic<br><input type="text" name="client_ic" />
                </th>
                <th>
                    Client Address <br><input type="text" name="client_add" />
                </th>
            </tr>
        </table>
        <table width="100%">
            <tr>
                <th>
                    <br><input type="submit" value="Search" align="center" />
                </th>
            </tr>
        </table>
    </form>
</body>
</html>

This is my process code

    <?php
    session_start();
    mysql_connect("localhost", "root", "") or die("Error connecting to database: ".mysql_error());

    mysql_select_db("waveevo") or die(mysql_error());
    ?>


        <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
        <html xmlns="http://www.w3.org/1999/xhtml">
        <head>
            <title>Search results</title>
            <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
            <!-- <link rel="stylesheet" type="text/css" href="style.css"/> -->
            <style>
            table tr:nth-child(even) {
                background-color: #eee;
            }
            table tr:nth-child(odd) {
               background-color:#fff;
            }
            table th {
                background-color: black;
                color: white;
            }
            </style>
        </head>
        <body>

    <?

php
    $query = $_GET['client_name'];
    $query2 = $_GET['client_ic'];
    $query3 = $_GET['client_add'];

    if ($query == null && $query2 == null && $query3 == null)
    {
        echo "Please at least insert one the value";
    }

    else
    {     
        $query = htmlspecialchars($query);
        $query2 = htmlspecialchars($query2);
        $query3 = htmlspecialchars($query3);

        $query = mysql_real_escape_string($query);
        $query2 = mysql_real_escape_string($query2);
        $query3 = mysql_real_escape_string($query3);

        $raw_results = mysql_query("SELECT * FROM client WHERE ('client_name' LIKE '%".$query."%') OR ('client_ic' LIKE '%".$query2."%') OR ('client_add_1' && ' ' && 'client_add_2' && ' ' && 'client_add_3' && ' ' && 'client_add_4' LIKE '%".$query3."%')") or die(mysql_error());;

        if(mysql_num_rows($raw_results) > null){ // if one or more rows are returned do following

            while($results = mysql_fetch_array($raw_results)){
                ?>
                <table width="100%">
                    <tr>
                        <th>ID</th>
                        <th>Name</th>
                        <th>IC</th>
                        <th>Mobile</th>
                        <th>Address</th>
                        <th>Marital Status</th>
                        <th>Race</th>
                        <th>Asset Type</th>
                        <th>Bank</th>
                        <th>Amount</th>
                        <th>Nationality</th>
                        <th>Limit</th>
                    </tr>
                    <tr>
                        <td><?php echo $results["client_id"]; ?></td>
                        <td><?php echo $results["client_name"]; ?></td>
                        <td><?php echo $results["client_ic"]; ?></td>
                        <td><br><?php echo $results["client_mobile_1"]."<br>".$results["client_mobile_2"]."<br>".$results["client_mobile_3"]; ?></td>
                        <td><?php echo $results["client_add_1"]."<br>".$results["client_add_2"]."<br>".$results["client_add_3"]."<br>".$results["client_city"]."<br>".$results["client_postcode"]; ?></td>
                        <td><?php echo $results["client_marital_status_id"]; ?></td>
                        <td><?php echo $results["client_race_id"]; ?></td>
                        <td><?php echo $results["client_asset_type_id"]; ?></td>
                        <td><?php echo $results["client_bank_id"]; ?></td>
                        <td><?php echo $results["amount"]; ?></td>
                        <td><?php echo $results["client_nationality_id"]; ?></td>
                        <td><?php echo $results["client_limit"]; ?></td>
                    </tr>
                </table>

            <?php
            }

        }



        else{ // if there is no matching rows do following
            echo "No results";
        }
    }
?>
</body>

So I try enter value for client ic, for example 1234, there is no data in the database that match the value that i entered just now but the result still show have, can i know why because i already don't have way to solve this

You're using wildcards, so basically there might be a row with a value that is '12345' or '51234' and it will match because of the % characters around your query parameters. MySQL can't make up data, so if you get a result it means it somehow matched. — Florian Humblot
– Florian Humblot, Commented May 29, 2017 at 8:01
Also, if you submit an empty field, you'll basically run a query that says select everything where this column starts with anything, and ends with anything. so that could be problematic as well — Florian Humblot
– Florian Humblot, Commented May 29, 2017 at 8:02
the data in the database for client_ic is 1234, and i search 99, there is a result shown. my database only have one data — shaqirzul
– shaqirzul, Commented May 29, 2017 at 8:04
You're using OR which means that it will return all rows which match any of the where statements separated with the OR so, if you only give it client_ic = 1234 and client_name='%%' it won't match on the '1234' but it will match on the client_name — Florian Humblot
– Florian Humblot, Commented May 29, 2017 at 8:07
The 10 year old ext/mysql extension was deprecated in 5.5 and has been removed in 7.0 - you should learn to use newer APIs, like PDO or MySQLi. — tereško
– tereško, Commented May 29, 2017 at 8:08

nacho · Accepted Answer · 2017-05-29 10:06:32Z

1

Thatś because when you use wildcards LIKE '%".$query."%' if your variable $query is empty you are just getting all because you compare with everything LIKE '%%'. You need to change this sentence:

$raw_results = mysql_query("SELECT * FROM client WHERE ('client_name' LIKE '%".$query."%') OR ('client_ic' LIKE '%".$query2."%') OR ('client_add_1' && ' ' && 'client_add_2' && ' ' && 'client_add_3' && ' ' && 'client_add_4' LIKE '%".$query3."%')") or die(mysql_error());;

whith something like this

$sql_query="SELECT * FROM client WHERE ";
$other=false;    

if ($query != null and $query!="") {
    $sql_query=$sql_query."('client_name' LIKE '%".$query."%')";
    $other=true;
}

if ($query2 != null and $query2!="") {
    if ($other) {
        $sql_query=$sql_query." OR ";    
    }
    $sql_query=$sql_query."('client_ic' LIKE '%".$query2."%')";
    $other=true;
}
if ($query3 != null and $query3!="") {
    if ($other) {
        $sql_query=$sql_query." OR ";    
    }
    $sql_query=$sql_query."('client_add_1' && ' ' && 'client_add_2' && ' ' && 'client_add_3' && ' ' && 'client_add_4' LIKE '%".$query3."%')";
    $other=true;
}    
if ($other) {
    $raw_results = mysql_query($sql_query) or die(mysql_error());
}

edited May 29, 2017 at 10:06

answered May 29, 2017 at 8:18

nacho

5,3772 gold badges28 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

shaqirzul Over a year ago

it still show the result even there is no result should be shown

nacho Over a year ago

Try to set if ($query != null and $query!="") for $query, $query2 & $query3 to avoid the "" value

Collectives™ on Stack Overflow

multiple search field on form using php and mysql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related