Performing AJAX search on mysql database using dropdown menus and text input

Question

I work for a group of realtors that have tasked me with designing a property search for their website. I acquired the proper credentials to an IDX feed to grab property information that I then import into my MySQL database and am having trouble grabing specific columns based on the search query. I have only been able to return all fields instead of the ones specified in the search. I have limited knowledge about php, MySQL, mysqli, pdo, ajax, etc. and eventhough the code I am providing is based on AJAX, I am open to any solution that works. Obviously, I am a new user/coder and am happy to add anything I forgot to add. Thank you for taking the time to help me with this problem. I included the html and the separate php file

    <!DOCTYPE html>
<html>
    <head>    
        <script>
        function showUser(str) {
        if (str == "") {
        document.getElementById("txtHint").innerHTML = "";
        return;
        } else { 
        if (window.XMLHttpRequest) {
            // code for IE7+, Firefox, Chrome, Opera, Safari
            xmlhttp = new XMLHttpRequest();
        } else {
            // code for IE6, IE5
            xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
        }
        xmlhttp.onreadystatechange = function() {
            if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
                document.getElementById("txtHint").innerHTML = xmlhttp.responseText;
            }
        };
        xmlhttp.open("POST","getuser.php?q="+str,true);
        xmlhttp.send();
        }
        }
        </script>
    </head>
    <body>
                <form id="fs_quicksearch" name="fs_quicksearch">
                    <div class="form-container">
                        <div class="form-row form-row-loc">
                            <div class="form-left2">
                                <label>Location:</label>
                            </div>
                            <div class="form-right">
                                <select class="dd-form-long" id="City"
                                    name="City" onchange="showUser(this.value)">
                                    <option value="">
                                            Any City
                                        </option>
                                        <option value="Auburn">
                                            Auburn
                                        </option>
                                        <option value="Baldwin City">
                                            Baldwin City
                                        </option>
                                        <option value="Basehor">
                                            Basehor
                                        </option>
                                        <option value="Berryton">
                                            Berryton
                                        </option>
                                        <option value="Bonner Springs">
                                            Bonner Springs
                                        </option>
                                        <option value="Carbondale">
                                            Carbondale
                                        </option>
                                        <option value="DeSoto">
                                            DeSoto
                                        </option>
                                        <option value="Effingham">
                                            Effingham
                                        </option>
                                        <option value="Eudora">
                                            Eudora
                                        </option>
                                        <option value="Kansas City">
                                            Kansas City
                                        </option>
                                        <option value="Kanwaka Twp">
                                            Kanwaka Twp
                                        </option>
                                        <option value="Lawrence">
                                            Lawrence
                                        </option>
                                        <option value="Leavenworth">
                                            Leavenworth
                                        </option>
                                        <option value="Lecompton">
                                            Lecompton
                                        </option>
                                        <option value="Lenexa">
                                            Lenexa
                                        </option>
                                        <option value="Linwood">
                                            Linwood
                                        </option>
                                        <option value="Louisburg">
                                            Louisburg
                                        </option>
                                        <option value="McLouth">
                                            McLouth
                                        </option>
                                        <option value="Meriden">
                                            Meriden
                                        </option>
                                        <option value="Nortonville">
                                            Nortonville
                                        </option>
                                        <option value="Osage City">
                                            Osage City
                                        </option>
                                        <option value="Oskaloosa">
                                            Oskaloosa
                                        </option>
                                        <option value="Ottawa">
                                            Ottawa
                                        </option>
                                        <option value="Overbrook">
                                            Overbrook
                                        </option>
                                        <option value="Overland Park">
                                            Overland Park
                                        </option>
                                        <option value="Ozawkie">
                                            Ozawkie
                                        </option>
                                        <option value="Paola">
                                            Paola
                                        </option>
                                        <option value="Parker">
                                            Parker
                                        </option>
                                        <option value="Perry">
                                            Perry
                                        </option>
                                        <option value="Pomona">
                                            Pomona
                                        </option>
                                        <option value="Shawnee">
                                            Shawnee
                                        </option>
                                        <option value="Tecumseh">
                                            Tecumseh
                                        </option>
                                        <option value="Tonganoxie">
                                            Tonganoxie
                                        </option>
                                        <option value="Topeka">
                                            Topeka
                                        </option>
                                        <option value="Turner">
                                            Turner
                                        </option>
                                        <option value="Valley Falls">
                                            Valley Falls
                                        </option>
                                        <option value="Wakarusa">
                                            Wakarusa
                                        </option>
                                        <option value="Wellsville">
                                            Wellsville
                                        </option>
                                        <option value="Winchester">
                                            Winchester
                                        </option>
                                    </select><select size="20">
                                        </select><input type="text">
                                    <div>
                                    </div>
                                </div>
                            </div>
                        </div>
                        <div class="form-row">
                            <div class="form-left">
                                <label>Price:</label> <select class=
                                "dd-form-short" id="AskPrice1" name="AskPrice1"
                                onchange="showUser(this.value)">
                                    <option value="">
                                        Min Price
                                    </option>
                                    <option value="25000">
                                        $25,000
                                    </option>
                                    <option value="50000">
                                        $50,000
                                    </option>
                                    <option value="75000">
                                        $75,000
                                    </option>
                                    <option value="100000">
                                        $100,000
                                    </option>
                                    <option value="125000">
                                        $125,000
                                    </option>
                                    <option value="150000">
                                        $150,000
                                    </option>
                                    <option value="175000">
                                        $175,000
                                    </option>
                                    <option value="200000">
                                        $200,000
                                    </option>
                                    <option value="225000">
                                        $225,000
                                    </option>
                                    <option value="250000">
                                        $250,000
                                    </option>
                                    <option value="275000">
                                        $275,000
                                    </option>
                                    <option value="300000">
                                        $300,000
                                    </option>
                                    <option value="325000">
                                        $325,000
                                    </option>
                                    <option value="350000">
                                        $350,000
                                    </option>
                                    <option value="375000">
                                        $375,000
                                    </option>
                                    <option value="400000">
                                        $400,000
                                    </option>
                                    <option value="425000">
                                        $425,000
                                    </option>
                                    <option value="450000">
                                        $450,000
                                    </option>
                                    <option value="475000">
                                        $475,000
                                    </option>
                                    <option value="500000">
                                        $500,000
                                    </option>
                                    <option value="550000">
                                        $550,000
                                    </option>
                                    <option value="600000">
                                        $600,000
                                    </option>
                                    <option value="650000">
                                        $650,000
                                    </option>
                                    <option value="700000">
                                        $700,000
                                    </option>
                                    <option value="750000">
                                        $750,000
                                    </option>
                                    <option value="800000">
                                        $800,000
                                    </option>
                                    <option value="850000">
                                        $850,000
                                    </option>
                                    <option value="900000">
                                        $900,000
                                    </option>
                                    <option value="950000">
                                        $950,000
                                    </option>
                                    <option value="1000000">
                                        $1,000,000
                                    </option>
                                </select>
                            </div>
                            <div class="form-right">
                                <label>To:</label> <select class=
                                "dd-form-short" id="AskPrice2" name="AskPrice2"
                                onchange="showUser(this.value)">
                                    <option value="">
                                        Max Price
                                    </option>
                                    <option value="25000">
                                        $25,000
                                    </option>
                                    <option value="50000">
                                        $50,000
                                    </option>
                                    <option value="75000">
                                        $75,000
                                    </option>
                                    <option value="100000">
                                        $100,000
                                    </option>
                                    <option value="125000">
                                        $125,000
                                    </option>
                                    <option value="150000">
                                        $150,000
                                    </option>
                                    <option value="175000">
                                        $175,000
                                    </option>
                                    <option value="200000">
                                        $200,000
                                    </option>
                                    <option value="225000">
                                        $225,000
                                    </option>
                                    <option value="250000">
                                        $250,000
                                    </option>
                                    <option value="275000">
                                        $275,000
                                    </option>
                                    <option value="300000">
                                        $300,000
                                    </option>
                                    <option value="325000">
                                        $325,000
                                    </option>
                                    <option value="350000">
                                        $350,000
                                    </option>
                                    <option value="375000">
                                        $375,000
                                    </option>
                                    <option value="400000">
                                        $400,000
                                    </option>
                                    <option value="425000">
                                        $425,000
                                    </option>
                                    <option value="450000">
                                        $450,000
                                    </option>
                                    <option value="475000">
                                        $475,000
                                    </option>
                                    <option value="500000">
                                        $500,000
                                    </option>
                                    <option value="550000">
                                        $550,000
                                    </option>
                                    <option value="600000">
                                        $600,000
                                    </option>
                                    <option value="650000">
                                        $650,000
                                    </option>
                                    <option value="700000">
                                        $700,000
                                    </option>
                                    <option value="750000">
                                        $750,000
                                    </option>
                                    <option value="800000">
                                        $800,000
                                    </option>
                                    <option value="850000">
                                        $850,000
                                    </option>
                                    <option value="900000">
                                        $900,000
                                    </option>
                                    <option value="950000">
                                        $950,000
                                    </option>
                                    <option value="1000000">
                                        $1,000,000
                                    </option>
                                </select>
                            </div>
                        </div>
                        <div class="form-row">
                            <div class="form-left">
                                <label>Beds:</label> <select class=
                                "dd-form-short" id="Beds" name="Beds" onchange=
                                "showUser(this.value)">
                                    <option value="">
                                        Any #
                                    </option>
                                    <option value="1">
                                        1 or more
                                    </option>
                                    <option value="2">
                                        2 or more
                                    </option>
                                    <option value="3">
                                        3 or more
                                    </option>
                                    <option value="4">
                                        4 or more
                                    </option>
                                    <option value="5">
                                        5 or more
                                    </option>
                                    <option value="6">
                                        6 or more
                                    </option>
                                </select>
                            </div>
                            <div class="form-right">
                                <label>Baths:</label> <select class=
                                "dd-form-short" id="Baths" name="Baths"
                                onchange="showUser(this.value)">
                                    <option value="">
                                        Any #
                                    </option>
                                    <option value="1">
                                        1 or more
                                    </option>
                                    <option value="2">
                                        2 or more
                                    </option>
                                    <option value="3">
                                        3 or more
                                    </option>
                                    <option value="4">
                                        4 or more
                                    </option>
                                    <option value="5">
                                        5 or more
                                    </option>
                                </select>
                            </div>
                        </div>
                        <div class="form-row">
                            <div class="form-left">
                                <label>Sq.Ft.:</label> <select class=
                                "dd-form-short" id="TotSQFT" name="TotSQFT"
                                onchange="showUser(this.value)">
                                    <option value="">
                                        Min SqFt
                                    </option>
                                    <option value="500">
                                        Min 500
                                    </option>
                                    <option value="1000">
                                        Min 1000
                                    </option>
                                    <option value="1500">
                                        Min 1500
                                    </option>
                                    <option value="2000">
                                        Min 2000
                                    </option>
                                    <option value="2500">
                                        Min 2500
                                    </option>
                                    <option value="3000">
                                        Min 3000
                                    </option>
                                    <option value="3500">
                                        Min 3500
                                    </option>
                                    <option value="4000">
                                        Min 4000
                                    </option>
                                    <option value="4500">
                                        Min 4500
                                    </option>
                                    <option value="5000">
                                        Min 5000
                                    </option>
                                    <option value="6000">
                                        Min 6000
                                    </option>
                                    <option value="7000">
                                        Min 7000
                                    </option>
                                </select>
                            </div>
                            <div class="form-right">
                                <label>MLS#:</label> <input class=
                                "form-field-short" id="MLSNo" name="MLSNo"
                                onchange="showUser(this.value)" type="text">
                            </div>
                        </div>
                        <div class="form-row">
                            <input alt="Lawrence" class="search-button" id=
                            "Submit" name="Submit" value="Submit">
                        </div>
                    </div>
                </form>
            </div>
        </div>
        <div id="txtHint">
            <b>Property Info Will Be Listed Here...</b>
        </div>
    </body>
</html>

-----------Now My GetUser.php----------------

<!DOCTYPE html>
<html>
    <head>

        <style>
        table {
        width: 100%;
        border-collapse: collapse;
        }

        table, td, th {
        border: 1px solid black;
        padding: 5px;
        }

        th {text-align: left;}
        </style>
        <title></title>
    </head>
    <body>
        <?php
        $q = intval($_POST['q']);

         $con = mysqli_connect('localhost','root','pw','properties');
         if (!$con) {
             die('Could not connect: ' . mysqli_error($con));
         }

         mysqli_select_db($con,"properties");
         $sql= (need help here-table called `listings`) or die(mysql_error());
         $result = mysqli_query($con,$sql);

         echo "<table>
         <tr>
         <th>Address</th>
         <th>City</th>
         <th>Zip</th>
         <th>Beds</th>
         <th>Baths</th>
         <th>AskPrice</th>
         <th>MLSNo</th>
         <th>TotSQFT</th>
         </tr>";
         while($row = mysqli_fetch_array($result)) {
             echo "<tr>";
             echo "<td>" . $row['Address'] . "</td>";
             echo "<td>" . $row['City'] . "</td>";
             echo "<td>" . $row['Zip'] . "</td>";
             echo "<td>" . $row['Beds'] . "</td>";
             echo "<td>" . $row['Baths'] . "</td>";
             echo "<td>" . $row['AskPrice'] . "</td>";
             echo "<td>" . $row['MLSNo'] . "</td>";
             echo "<td>" . $row['TotSQFT'] . "</td>";
             echo "</tr>";
         }
         echo "</table>";
         mysqli_close($con);
         ?>
    </body>
</html>

Yes, they are separate files. I failed to post them correctly. Used the hr line now, hope that's the right way to show them. If not please let me know — NoobasaurusRex
– NoobasaurusRex, Commented Aug 25, 2016 at 5:28
the way it's set up right now, you can only ever query against City or Min Price or Max Price or Beds or Baths or Max SqFt. It'll never show results based on a combination of those things. Is that what you want, or should they be able to combine all of the possibilities? (P.S. the reason is every time a dropdown changes, you run showUser again, but only pass the value selected in that individual dropdown. The selection from the others will be ignored). — ADyson
– ADyson, Commented Aug 25, 2016 at 7:44
@ADyson It would be great it they could search one dropdown and get the result OR select multiple dropdowns to narrow their results further. The showUser part was confusing so I just put it in every selection field :) If you have time, could you provide an example of how I could accomplish this? — NoobasaurusRex
– NoobasaurusRex, Commented Aug 25, 2016 at 14:29

ADyson · Accepted Answer · 2016-08-26 08:38:35Z

Something like this should be the solution. Because of the potential number of variables, the code to build the SQL ended up being fairly complicated. Apologies if there are any errors in there, I haven't got the tools to do any sort of testing available to me at the moment. But hopefully you see the pattern where we build up the WHERE clause of the SQL based on whether the user chose a value for each parameter or not (if they didn't, we allow any value in that column, so no need for a WHERE restriction on it)

The javascript submits the entire form via ajax (using jQuery's ajax functions, which are much easier to use than the raw XmlHTTP object) when the user clicks the "submit" button. I've done some very rudimentary validation, just to ensure the user selects at least one option to restrict the query, otherwise you could end up with a query just selecting everything from the database, which might be problematic.

Firstly, your HTML page:

<!DOCTYPE html>
<html>
<head>
    <script type="Text/JavaScript" src="https://code.jquery.com/jquery-2.2.3.min.js"></script>
   <script type="text/javascript" language="javascript">
    $(function() {
        //adds an event listener to handle the form's "submit" event
        $("#fs_quicksearch").submit(function(event) {
          event.preventDefault(); //prevent the default postback behaviour

        //make ajax request to the server
        $.ajax({
            contentType: 'application/x-www-form-urlencoded; charset=UTF-8',
            dataType: 'text/html',
            type: 'POST',
            url: 'getuser.php',
            data: $(this).serialize(), //automatically picks up all the form fields and translates them into a valid format for a POST request
            success: function(response) { //runs when the request succeeeds (no HTTP errors)
                $("#divResults").html(response);
            },
            error: function (jQXHR, textStatus, errorThrown) { //runs when the request fails with a HTTP error
                alert("An error occurred whilst trying to contact the server: " + jQXHR.status + " " + textStatus + " " + errorThrown);
            }
        });
    });
    </script>
</head>
<body>
   <form id="fs_quicksearch" name="fs_quicksearch">
        <div class="form-container">
            <div class="form-row form-row-loc">
                <label for="City">Location:</label>
              <select class="dd-form-long" id="City" name="City">
                    <option value="">Any City</option>
                    <option value="Auburn">Auburn</option>
                    <option value="Baldwin City">Baldwin City</option>
                 <option value="Basehor">Basehor</option>
                 <option value="Berryton">Berryton</option>
                    ...rest of your options here
              </select>
            </div>
            <div class="form-row">
                <div class="form-left">
                    <label for="AskPrice1">Price:</label>
                    <select class="dd-form-short" id="AskPrice1" name="AskPrice1">
                        <option value="">Min Price</option>
                        <option value="25000">$25,000</option>
                        <option value="50000">$50,000</option>
                        ...rest of your options here
                    </select>
                </div>
                <div class="form-right">
                    <label for="AskPrice2">To:</label>
                    <select class="dd-form-short" id="AskPrice2" name="AskPrice2">
                        <option value="">Max Price</option>
                        <option value="25000">$25,000</option>
                     <option value="50000">$50,000</option>
                        ...rest of your options here
                 </select>
              </div>
            </div>
          <div class="form-row">
                <div class="form-left">
                    <label for="Beds">Beds:</label>
                    <select class="dd-form-short" id="Beds" name="Beds">
                        <option value="">Any #</option>
                        <option value="1">1 or more</option>
                        <option value="2">2 or more</option>
                        ...rest of your options here
                 </select>
              </div>
              <div class="form-right">
                    <label for="Baths">Baths:</label>
                    <select class="dd-form-short" id="Baths" name="Baths">
                        <option value="">Any #</option>
                        <option value="1">1 or more</option>
                        <option value="2">2 or more</option>
                        ...rest of your options here
                 </select>
              </div>
          </div>
            <div class="form-row">
                <div class="form-left">
                    <label for="TotSQFT">Sq.Ft.:</label>
                    <select class="dd-form-short" id="TotSQFT" name="TotSQFT">
                        <option value="">Min SqFt</option>
                        <option value="500">Min 500</option>
                        <option value="1000">Min 1000</option>
                        <option value="1500">Min 1500</option>
                        ...rest of your options here
                    </select>
                </div>
                <div class="form-right">
                    <label for="MLSNo">MLS#:</label> 
                    <input class="form-field-short" id="MLSNo" name="MLSNo" type="text">
                </div>
            </div>
            <div class="form-row">
                <input alt="Lawrence" class="search-button" id="Submit" value="Submit">
            </div>
        </div>
   </form>
    <br/>
   <div id="divResults">
        <b>Property Info Will Be Listed Here...</b>
    </div>
</body>

Secondly, the PHP (there's no need for any of the raw HTML you had in getuser, you only want to return the table snippet, which will get added to the rest of the first page):

<?php
$city = $_POST['City'];
$askprice1 = $_POST['AskPrice1'];
$askprice2 = $_POST['AskPrice2'];
$beds = $_POST['Beds'];
$baths = $_POST['Baths'];
$totsqft = $_POST['TotSQFT'];
$mlsno = $_POST['MLSNo'];

//this is just some very crude validation, you should probably make it more sophisticated
if ($city == "" && $askprice1 == "" && $askprice2 == "" && $beds == "" && $baths == "" && $totsqft == "" && $mlsno == "")
{
  echo "Validation error: Please choose at least one option";
}
else
{
$con = mysqli_connect('localhost','root','pw','properties');

if (mysqli_connect_errno())
{
    echo "Failed to connect: " . mysqli_connect_error();
}

mysqli_select_db($con, "properties") or die('ERROR! Could not select database');

//build the sql based on what the user selected
$sql = "select * from `listings` where ";
$paramArr = array(); //will contain the query parameters.
$paramTypes = ""; //will indicate the data type of each parameter
$sqlwhere = "";
if ($city != "") { $sqlwhere .= " City = ?"; $paramArr[] = $city; $paramTypes .= "s";}
if ($askPrice1 != "") { $sqlwhere .= ($sqlwhere == "" ? "" : " and ")." AskPrice >= ?"; $paramArr[] = &$askPrice1; $paramTypes .= "i";}
if ($askPrice2 != "") { $sqlwhere .= ($sqlwhere == "" ? "" : " and ")." AskPrice <= ?"; $paramArr[] = &$askPrice2; $paramTypes .= "i";}
if ($beds != "") { $sqlwhere .= ($sqlwhere == "" ? "" : " and ")." Beds >= ?"; $paramArr[] = &$beds; $paramTypes .= "i";}
if ($baths != "") { $sqlwhere .= ($sqlwhere == "" ? "" : " and ")." Baths >= ?"; $paramArr[] = &$baths; $paramTypes .= "i";}
if ($totsqft != "") { $sqlwhere .= ($sqlwhere == "" ? "" : " and ")." TotSQFT >= ?"; $paramArr[] = &$totsqft; $paramTypes .= "i";}
if ($mlsno != "") { $sqlwhere .= ($sqlwhere == "" ? "" : " and ")." MLSNo = ?"; $paramArr[] = &$mlsno; $paramTypes .= "s";} //if the MLSNo column in the DB is actually an integer, you'll need to change "s" to "i" here
$sql .= $sqlwhere;

$initialParams = array(&$stmt, &$paramTypes);
$callbackParams = array_merge($initialParams, $paramArr);
$statement = mysqli_prepare($con, $sql); //use prepared statements to guard against SQL injection
call_user_func_array("mysqli_stmt_bind_param", $callbackParams)); //bind the parameters to the statement

$result = mysqli_query($con, $sql) or die(mysqli_error($con));

echo "<table>
    <tr>
    <th>Address</th>
    <th>City</th>
    <th>Zip</th>
    <th>Beds</th>
    <th>Baths</th>
    <th>AskPrice</th>
    <th>MLSNo</th>
    <th>TotSQFT</th>
    </tr>";
while($row = mysqli_fetch_array($result)) {
    echo "<tr>";
    echo "<td>" . $row['Address'] . "</td>";
    echo "<td>" . $row['City'] . "</td>";
    echo "<td>" . $row['Zip'] . "</td>";
    echo "<td>" . $row['Beds'] . "</td>";
    echo "<td>" . $row['Baths'] . "</td>";
    echo "<td>" . $row['AskPrice'] . "</td>";
    echo "<td>" . $row['MLSNo'] . "</td>";
    echo "<td>" . $row['TotSQFT'] . "</td>";
    echo "</tr>";
}
echo "</table>";
mysqli_close($con);
}
?>

Lastly, one other small recommendation: Don't use the "root" account to log in to mysql from your web application. It can do anything, and you run a small risk that a malicious user could either steal the credentials, or manage to otherwise inject some malicious sql and then destroy your server, or steal data from your app. Set up a user specifically for this app, and give it permission only to the tables, procedures etc that it actually needs. That way you minimise the risk. I've used mysqli prepared statements as well which also minimises the risk of sql injection in the first place.

Collectives™ on Stack Overflow

Performing AJAX search on mysql database using dropdown menus and text input

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related