Multiple selection boxes to search a database

Question

I've changed the code with the selection boxes to the below:

<html>

<head>
<script type="text/javascript">

function loadXMLDoc() {
var xmlhttp;
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.frm.modelSelection.innerHTML=xmlhttp.responseText;
    }
}

var makevalue=document.frm.makeSelection.value;

xmlhttp.open("GET","http://www.autodeal.co.za/newsite/model-selection?ajaxmake="+‌makevalue,true);
xmlhttp.send();
}

</script>
</head>

<body>

<?php 

$dbName = "F:/Domains/autodeal/autodeal.co.za/wwwroot/newsite/db/savvyautoweb.mdb";

// Throws an error if the database cannot be found
if (!file_exists($dbName)) {
    die("Could not find database file.");
}

// Connects to the database
// Assumes there is no username or password
$conn = odbc_connect("Driver={Microsoft Access Driver (*.mdb)};Dbq=$dbName", '', '');

?>

<form action="index.php?option=com_content&view=article&id=99" method="post" name="frm">

<select name="makeSelection" onchange="loadXMLDoc()">

<?php
//Loads the Makes from the database into a dropdown
$resultMake = odbc_exec($conn, "SELECT DISTINCT Make FROM Vehicle ORDER BY Make") or die (odbc_errormsg());
while ($rowMake = odbc_fetch_array($resultMake)) {
    echo "<option value='$rowMake[Make]'>$rowMake[Make]</option>";

}

?>
</select><br />

    <select name="modelSelection">

    </select><br />

    <select name="yearSelection">
        <option>2004</option>
        <option>2005</option>
        <option>2006</option>
        <option>2007</option>
        <option>2008</option>
        <option>2009</option>
        <option>2010</option>
        <option>2011</option>
        <option>2012</option>
        <option>2013</option>
        <option>2014</option>
    </select><br />

    <select name="priceSelection">
        <option>< 5000</option>
        <option>5000 - 20 000</option>
        <option>20 000 - 50 000</option>
        <option>50 000 - 100 000</option>
        <option>100 000 - 200 000</option>
        <option>200 000 - 300 000</option>
        <option>300 000 - 400 000</option>
        <option>400 000 - 500 000</option>
        <option>50 000 - 1 000 000</option>
        <option>> 1 000 000</option>
    </select>

<input type="submit" name="submit" value="Go">
</form>

</body>
</html>

Hi,

I've updated the code to reflect the answers below, but now, when you make the first selection, the Model selection box remains empty.

modelSelection.php

<?php
        $dbName = "F:/Domains/autodeal/autodeal.co.za/wwwroot/newsite/db/savvyautoweb.mdb";

        // Throws an error if the database cannot be found
        if (!file_exists($dbName)) {
            die("Could not find database file.");
        }

        $conn = odbc_connect("Driver={Microsoft Access Driver (*.mdb)};Dbq=$dbName", '', '');
        //loads the models based on the makes selection into a dependant dropdown
        if (isset($_REQUEST['ajaxmake'])) {

            $resultModel = odbc_exec($conn, "SELECT Model FROM Vehicle WHERE Make = '".$_REQUEST['ajaxmake']."'") or die (odbc_errormsg());

                while ($rowModel = odbc_fetch_array($resultModel)) {

                    echo "<option value='$rowModel[Model]'>$rowModel[Model]</option>";
                    die(); //I'm not sure where to put this because I assume this is the reason why this selection must be first
                }

        }

?>

For start, you should remove this `die();` and try. If your option is still not first there are other ways to make it. — AyB, Mar 25 '14 at 08:02
See if I do remove that? The options from the remaining selection boxes are dumped in the "Model" selection box. — Deon, Mar 25 '14 at 08:07

score 1 · Accepted Answer · edited May 23 '17 at 10:26

1

As far as I can see, the problem is that you are loading the whole request response text inside a select button. I've looked at your request response and it is responding the whole page with the models loaded, so basically it is getting all options and loading them on the Model select box, because you are inserting the whole page on the model select box.

You have multiple options here: You can create a page that only loads the Model options, so have a file which has only this part:

            $dbName = "F:/Domains/autodeal/autodeal.co.za/wwwroot/newsite/db/savvyautoweb.mdb";

            // Throws an error if the database cannot be found
            if (!file_exists($dbName)) {
                die("Could not find database file.");
            }

            $conn = odbc_connect("Driver={Microsoft Access Driver (*.mdb)};Dbq=$dbName", '', '');
            //loads the models based on the makes selection into a dependant dropdown
            if (isset($_REQUEST['ajaxmake'])) {

                $resultModel = odbc_exec($conn, "SELECT Model FROM Vehicle WHERE Make = '".$_REQUEST['ajaxmake']."'") or die (odbc_errormsg());

                    while ($rowModel = odbc_fetch_array($resultModel)) {

                        echo "<option value='$rowModel[Model]'>$rowModel[Model]</option>";
                    }

            }

And change the page you are calling through ajax to point to that page:

 xmlhttp.open("GET","newpage.php?ajaxmake="+ makevalue,true);

The other option, and the one I suggest you is to look into some javascript library, such as jQuery which has functions to easen your work.

If you include jQUery library, adding the select name as id="makeSelection" and id="modelSelection" you could write a javascript function like this:

jQuery(document).ready(function(){
     jQuery("#makeSelection").change(function(){
         jQuery("#modelSelection").load("?ajaxmake="+ makevalue + #modelSelection option");
     });
 });

BTW! Be aware that you may have a huge security problem in your sql queries, since people can attack you through the ajaxmake variable, and truncate/drop your tables or anything. I suggest you to sanitize and validate the data coming from your requests, specially if you post some sensitive data like your database tables on the internet!!! If you want to know more about SQL Injection (how this security issue is called): How can I prevent SQL injection in PHP?

edited May 23 '17 at 10:26

Community

1
1

answered Mar 25 '14 at 09:26

Daniel Ramos

137
1
8

Okay, I went for your first option. I now have the model selection block of code in a separate file. How do I go go about calling `xmlhttp.open("GET","modelSelection.php?ajaxmake="+ makevalue,true);` and where do I do that? I've tried adding that to the head in the page. That doesn't work. Shouldn't there be some function call on the select? – Deon Mar 25 '14 at 10:44
You basically do it the same way you were doing it but instead of calling the current page you call the new page that only displays a small piece of html containing the model options, which you insert in the model select the same way you were doing it now. – Daniel Ramos Mar 25 '14 at 10:53
Ok, the ajax request is failing, because of the nice urls, you have to give a nice url to your file or call it directly with the full url, like: `xmlhttp.open("GET","http://yoururl.com/nice/url/?ajaxmake="+makevalue,true);` or `xmlhttp.open("GET","http://yoururl.com/file/path/modelSelection.php?ajaxmake="+makevalue,true);` – Daniel Ramos Mar 25 '14 at 11:38
I used `xmlhttp.open("GET","http://autodeal.co.za/newsite/db/modelSelection.php?ajaxmake="+‌makevalue,true);` but it's still not working. I updated the code above – Deon Mar 25 '14 at 12:09
I tried to go to your this page: http://autodeal.co.za/newsite/db/modelSelection.php?ajaxmake=CHANGAN It does not work, are you sure the path is correct or is there a fatal error? It's displaying a blank page but it should display a list of – Daniel Ramos Mar 25 '14 at 13:08
Maybe you forgot to set the database connection on $conn, edited my code. – Daniel Ramos Mar 25 '14 at 13:16
I set the database connection and then I published the code to a menu item and used that URL. If you search http://www.autodeal.co.za/newsite/model-selection?ajaxmake=JMC now you get a single option when there are actually over twenty options. It's still not showing in the selection box though. I updated my code. – Deon Mar 25 '14 at 13:32
Remove the die after you set the first, that is stoping the code to populate the rest of the items, I edited my code – Daniel Ramos Mar 25 '14 at 13:41
Thank you. This link: http://www.autodeal.co.za/newsite/model-selection?ajaxmake=JMC now shows all the JMC model, but it's still not populating the drop down box on this page: http://www.autodeal.co.za/newsite/new-cars-for-sale-south-africa/search-new-car-prices – Deon Mar 25 '14 at 13:45
Firebug is displaying me a javascript error `SyntaxError: illegal character`, that is making it not to work, can you check if your javascript syntax is correct? I think it might be a good idea to add a die(); at the end of the modelSelection.php to avoid loading the whole page template. – Daniel Ramos Mar 25 '14 at 13:53
Hi, I can't seem to find that error in my text editor. I know where it supposedly is, if you look at JSFiddle and the like. What would your suggestion be for getting rid of this error? I'm not very great in Javascript. My editor is Sublime Text 3, but I also have BBEdit 10 and the latest version of TextWrangler. – Deon Mar 25 '14 at 18:57
I haven't changed anything in the above code. So no. But, according to JS Bin and JS Fiddle, the error is here: `xmlhttp.open("GET","http://www.autodeal.co.za/newsite/model-selection?ajaxmake="+‌makevalue,true);` JS Bin gives me a red dot between (") and (+). – Deon Mar 26 '14 at 03:39
It looks fine to me. Try to write the line again with no copy pasting. Sometimes when doing copy & paste we copy some strange chars. – Daniel Ramos Mar 26 '14 at 08:16
After two days of back and forth, it finally works. And I have you to thank for that. – Deon Mar 26 '14 at 09:32
Thank so so so so much for your help with this issue. IT WORKS! – Deon Mar 26 '14 at 09:32
Np, you are welcome! That's what stackoverflow is for! :) Still, look at jQuery and the SQL Injection link I sent you if you want to learn some interesting stuff! – Daniel Ramos Mar 26 '14 at 09:40

score 0 · Answer 2 · answered Mar 25 '14 at 09:21

0

I am not sure why you have html included in your ajax processing file. Usually you keep a .php file consisting only of php code and then you can be sure no html or script code are being included (which is currently happening in your page now).

For one, try to change your model dropdown code to:

    <?php
            //loads the models based on the makes selection into a dependant dropdown
            if (isset($_REQUEST['ajaxmake'])) {

                echo "<select name='modelSelection'>"; //select tag placed here
                $resultModel = odbc_exec($conn, "SELECT Model FROM Vehicle WHERE Make = '".$_REQUEST['ajaxmake']."'") or die (odbc_errormsg());

                    while ($rowModel = odbc_fetch_array($resultModel)) {

                        echo "<option value='$rowModel[Model]'>$rowModel[Model]</option>";
                    }

                    echo "</select><br>";
                    die(); //<-- the die placed here will not execute the rest of
                           //the code and also all the options will be populated

            }
    ?>

answered Mar 25 '14 at 09:21

AyB

11,609
4
32
47

Hi, The above code suggested stops the Year and Price selection options from showing in the Model Selection Box, but the "Make" Options still appears in the Model selection box after a selection has been made. – Deon Mar 25 '14 at 10:21
@deon4110 There are too many places in your code that needs to be dodged to not print the html and script (if you see with the developer tool, your select box is including the ` – AyB Mar 25 '14 at 10:33
@I Can Has Cheezburger How do I call that then? Also, should the actual Ajax function also be moved to the new page? – Deon Mar 25 '14 at 10:48
@deon4110 The ajax function remains in the same page. Only this `xmlhttp.open("GET","?ajaxmake="+ makevalue,true);` part changes like in the answer below. – AyB Mar 25 '14 at 10:51
@I Can Has Cheezburger please see the edited code above. – Deon Mar 25 '14 at 11:01
@deon4110 The developer console is showing a `404 Article not found` error. Guess you are using Joomla, I haven't worked with it but you might need to check on the article's permission or whether the article category is published... – AyB Mar 25 '14 at 11:09

Multiple selection boxes to search a database

2 Answers2