PHP/MySQL search not working

Question

I am having some difficulty with my PHP/MySQL search. It worked great a year ago, but for some reason it has just stopped working. I have tried searching and cannot figure it out. I have it echo my query but it never adds in the WHERE clause. Any help is much appreciated.

Form Code:

<form name="search" method="post">
    <p><b>Name of Property:</b> <input type="text" id="NameSrch" name="NameSrch" /> (optional)</p>
    <p><b>City Your Looking to Stay In:</b> <input type="text" id="CitySrch" name="CitySrch" /> (optional)</p>
    <p><b>Listing ID Number:</b> <input type="text" id="RentalIDSrch" name="RentalIDSrch" /> (optional)</p>
    <p><b>Number of Bedrooms:</b> <input type="text" id="BedroomSrch" name="BedroomSrch" /> (optional)</p>
    <p><b>Number of Bathrooms:</b> <input type="text" id="BathroomSrch" name="BathroomSrch" /> (optional)</p>
    <p><b>Maximum Occupancy:</b> <input type="text" id="SleepsSrch" name="SleepsSrch" /> (optional)</p>
    <input type="hidden" name="method" value="exSearch">
    <p><input type="submit" value="Search" /></p>
</form>

PHP Code:

<? 
                        $method = $_POST['method'];

                        if($method == 'exSearch') {

                            // Start Results Display 


                            $con = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql: ' . mysql_error());
                            mysql_select_db($dbname);

                            $query_string = 'SELECT * FROM TABLE ';

                            $location_result_string = "";
                            $location_count = 0;


                                if ($NameSrch) {
                                    if ($location_count > 0) {
                                        $location_result_string = $location_result_string . " AND ";
                                    }
                                    $location_count = $location_count + 1;
                                    $location_result_string = $location_result_string . ' (propName) LIKE ("%$NameSrch%") ';
                                }

                                if ($CitySrch) {
                                    if ($location_count > 0) {
                                        $location_result_string = $location_result_string . " AND ";
                                    }
                                    $location_count = $location_count + 1;
                                    $location_result_string = $location_result_string . ' (propCity) LIKE ("%$CitySrch%") ';
                                }

                                if ($RentalIDSrch) {
                                    if ($location_count > 0) {
                                        $location_result_string = $location_result_string . " AND ";
                                    }
                                    $location_count = $location_count + 1;
                                    $location_result_string = $location_result_string . ' (propID) LIKE ("%$RentalIDSrch%") ';
                                }

                                if ($BedroomSrch) {
                                    if ($location_count > 0) {
                                        $location_result_string = $location_result_string . " AND ";
                                    }
                                    $location_count = $location_count + 1;
                                    $location_result_string = $location_result_string . ' (propBedrooms) LIKE ("%$BedroomSrch%") ';
                                }

                                if ($BathroomSrch) {
                                    if ($location_count > 0) {
                                        $location_result_string = $location_result_string . " AND ";
                                    }
                                    $location_count = $location_count + 1;
                                    $location_result_string = $location_result_string . ' (propBaths) LIKE ("%$BathroomSrch%") ';
                                }

                                if ($SleepsSrch) {
                                    if ($location_count > 0) {
                                        $location_result_string = $location_result_string . " AND ";
                                    }
                                    $location_count = $location_count + 1;
                                    $location_result_string = $location_result_string . ' (propSleeps) LIKE ("%$SleepsSrch%") ';
                                }



                            if ($location_count > 0) {
                                $location_result_string = "WHERE (" . $location_result_string . ")";

                                $query_string = $query_string . $location_result_string;    
                            }

                            $re = mysql_query($query_string, $con) or die (mysql_error());

                            $number_of_rows = mysql_num_rows($re);




                            // Loop through each item in the result set                 
                            for ($h = 0; $h < $number_of_rows; $h++)
                            {

                            $propID = mysql_result($re, $h, 'propID');
                            $propAvail = mysql_result($re, $h, 'propAvail');
                            $propPets = mysql_result($re, $h, 'propPets');
                            $propName = mysql_result($re, $h, 'propName');
                            $propPropertyType = mysql_result($re, $h, 'propPropertyType');
                            $propPriceRange = mysql_result($re, $h, 'propPriceRange');
                            $propBedrooms = mysql_result($re, $h, 'propBedrooms');
                            $propBaths = mysql_result($re, $h, 'propBaths');
                            $propPropertyType = mysql_result($re, $h, 'propPropertyType');
                            $propSleeps = mysql_result($re, $h, 'propSleeps');
                            $propPic1 = mysql_result($re, $h, 'propPic1');
                            $propPicDesc1 = mysql_result($re, $h, 'propPicDesc1');

                            $nameLen = strlen($propName);
                            $petsLen = strlen($propPets);
                            $pets = $propPets;


                                                           //Results go here

                            }
                            echo $query_string;
                            }


                            // End Results Display

                            ?>

How are the variables such as $NameSrch, $CitySrch set? Are they set at all or are you expecting them to be set through the POST? Because that doesn't work. You can access posted content through $_POST['fieldName']. Also, access variables inside a string like: 'blablabla '.$sName.' extra'; or if you use double quotes you can do it like "blablabla {$sName} extra". — Stefan Candan
– Stefan Candan, Commented Mar 27, 2013 at 18:44
That is probably the problem. If you have the settings REGISTER_GLOBALS=ON this code can work but with REGISTER_GLOBALS=OFF (as it should be), you have to access the variables via $_POST — kero
– kero, Commented Mar 27, 2013 at 18:46
If variables like $NameSrch are straightly taken from the user’s input, you’re script is probably vulnerable to SQL injection. — Gumbo
– Gumbo, Commented Mar 27, 2013 at 18:50

Stefan Candan · Accepted Answer · 2013-03-27 18:55:34Z

If my assumptions were right about the variables being extracted from the form directly, you'd have to have register globals on, which is not adviced, since it brings a great security risk. Instead, I've adjusted your PHP code with the correct way, also fixing the variables included in the query string.

<?php
$method = $_POST['method'];
if($method == 'exSearch') {
    // Start Results Display 
    $con = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql: ' . mysql_error());
    mysql_select_db($dbname);
    $query_string = 'SELECT * FROM TABLE ';
    $location_result_string = "";
    $location_count = 0;
    if ($_POST['NameSrch']) {
        if ($location_count > 0) {
            $location_result_string = $location_result_string . " AND ";
        }
        $location_count = $location_count + 1;
        $location_result_string = $location_result_string . ' (propName) LIKE ("%'.$_POST['NameSrch'].'%") ';
    }
    if ($_POST['CitySrch']) {
        if ($location_count > 0) {
            $location_result_string = $location_result_string . " AND ";
        }
        $location_count = $location_count + 1;
        $location_result_string = $location_result_string . ' (propCity) LIKE ("%'.$_POST['CitySrch'].'%") ';
    }
    if ($_POST['RentalIDSrch']) {
        if ($location_count > 0) {
            $location_result_string = $location_result_string . " AND ";
        }
        $location_count = $location_count + 1;
        $location_result_string = $location_result_string . ' (propID) LIKE ("%'.$_POST['RentalIDSrch'].'%") ';
    }
    if ($_POST['BedroomSrch']) {
        if ($location_count > 0) {
            $location_result_string = $location_result_string . " AND ";
        }
        $location_count = $location_count + 1;
        $location_result_string = $location_result_string . ' (propBedrooms) LIKE ("%'.$_POST['BedroomSrch'].'%") ';
    }
    if ($_POST['BathroomSrch']) {
        if ($location_count > 0) {
            $location_result_string = $location_result_string . " AND ";
        }
        $location_count = $location_count + 1;
        $location_result_string = $location_result_string . ' (propBaths) LIKE ("%'.$_POST['BathroomSrch'].'%") ';
    }
    if ($_POST['SleepsSrch']) {
        if ($location_count > 0) {
            $location_result_string = $location_result_string . " AND ";
        }
        $location_count = $location_count + 1;
        $location_result_string = $location_result_string . ' (propSleeps) LIKE ("%'.$_POST['SleepsSrch'].'%") ';
    }
    if ($location_count > 0) {
        $location_result_string = "WHERE (" . $location_result_string . ")";
        $query_string = $query_string . $location_result_string;    
    }
    $re = mysql_query($query_string, $con) or die (mysql_error());
    $number_of_rows = mysql_num_rows($re);
    // Loop through each item in the result set                 
    for ($h = 0; $h < $number_of_rows; $h++)
    {
        $propID = mysql_result($re, $h, 'propID');
        $propAvail = mysql_result($re, $h, 'propAvail');
        $propPets = mysql_result($re, $h, 'propPets');
        $propName = mysql_result($re, $h, 'propName');
        $propPropertyType = mysql_result($re, $h, 'propPropertyType');
        $propPriceRange = mysql_result($re, $h, 'propPriceRange');
        $propBedrooms = mysql_result($re, $h, 'propBedrooms');
        $propBaths = mysql_result($re, $h, 'propBaths');
        $propPropertyType = mysql_result($re, $h, 'propPropertyType');
        $propSleeps = mysql_result($re, $h, 'propSleeps');
        $propPic1 = mysql_result($re, $h, 'propPic1');
        $propPicDesc1 = mysql_result($re, $h, 'propPicDesc1');
        $nameLen = strlen($propName);
        $petsLen = strlen($propPets);
        $pets = $propPets;
        // Results go here
    }
    echo $query_string;
}
// End Results Display

?>

I'd also suggest validating your inputs and check if you're getting the type of data you want, like if you want numbers, validate it is a number, so you're less prone to SQL injection. Also use mysql_real_escape_string(http://php.net/manual/en/function.mysql-real-escape-string.php) to sanitize the data.

And if you want your script to work in the future aswel, I'd suggest taking up a lesson or two in PDO, because in the near future, PHP will be getting rid of the MySQL library.

Michael · Accepted Answer · 2013-03-27 18:56:26Z

0

You're using the variables $NameSrch, $CitySrch etc. without defining them. As they match the names of the form inputs, I'd assume that you originally had register_globals turned on and that now it's turned off (or if you're using PHP 5.4.0, removed).

You need to change them to $_POST['NameSrch'], $_POST['CitySrch'], etc.

answered Mar 27, 2013 at 18:56

Michael

12.9k6 gold badges57 silver badges69 bronze badges

Collectives™ on Stack Overflow

PHP/MySQL search not working

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related