PHP filtering Options via dropdown boxes

Question

didzmad 0 Newbie Poster

11 Years Ago

Hello php community,

Could you help me in my research? I just cannot find what I am looking for.

I am working on a website with PHP and MySQL.
I am trying to get a filter option on my website to fetch the information from database.
Here are the filter options:
-COUNTY
-CITY
-SELLLER NAME

When clients go onto the website they first need to select the county, it will then populate the list of cities, once city selected it will populate the list of sellers under that city and click search to show a list of items these suppliers sell.

Hope this makes sense. If yuou know of a website/tutorial that can help me please let me know.

Thanks
DidzMad

html-css php seo

5 Contributors
7 Replies
6K Views
23 Hours Discussion Span
Latest Post 11 Years Ago Latest Post by diafol

mattster 195 Practically a Master Poster

11 Years Ago

Well first you need:

<select name="city"><option>...</option></select>

and the PHP:

$sql = "SELECT * FROM sellers WHERE city='".mysql_real_escape_string($_POST['city'])."'";
$res = mysql_query($sql);

echo '<table>
        <tr><td>...</td></tr>'

while($row = mysql_fetch_assoc($res)){
        echo "<tr><td>".$row['some_colmn']."</td><tr>";
}

echo "</table>";

The above will generate a table populated with every row in the database with the selected city... :)

diafol

11 Years Ago

Here's an updated version: diafolCode - it prompted me to blog it (shameless plug).

Edited 11 Years Ago by diafol

cereal commented: good! +9

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.

rfrapp 17 Junior Poster in Training · Answer 1 · 2013-02-02T16:44:33+00:00

You could also use a little javascript

<?php
$tbl_name = "blah";
$sort = mysql_real_escape_string($_REQUEST['sort']);
$data = mysql_query("SELECT * FROM $tbl_name WHERE somecolumn='$sort'");
?>

<html>
<head>
<script type="text/javascript">
</head>
<body>
    <form>
        <select id="sort">
            <option value="..">..</option>
        </select>
        <input type="button" onClick="redirect();" />
    </form>

    <script type="text/javascript">
    function redirect()
    {
        var select = document.getElementById("sort").value;
        if (select != "")
            window.location = "somewebsite.com?sort=" + select;
    }
    </script>
</body>
</html>

diafol · Answer 2 · 2013-02-02T17:35:36+00:00

OK, you have three main approaches for this;

1) Pure server-side (just php)
2) Ajax (php - js - php - js - php etc)
3) Initial php with subsequent js calls only

1) Pure server side - this loads a new page on every dropdown selection.
2) Great if you have frequently updated DB values, that require a DB check'
3) Get php/mysql to create json array data. Once created, each dropdown selection just calls on the json data, bit a round trip to the server.

Which approach do you think satisfies your needs?

Octet 45 Newbie Poster Featured Poster · Answer 3 · 2013-02-02T17:54:12+00:00

I would also advise you to use MySQLi and Prepared Statements if you take the data from a Database instead of just MySQL_* as it is being depreciated, and the newer version has many more security benefits.

didzmad 0 Newbie Poster · Answer 4 · 2013-02-02T17:55:29+00:00

Thank you all for your help.
I will have a try with the options given above.
Diafol - My approach would be to use php only with js calls.

diafol · Answer 5 · 2013-02-03T01:26:56+00:00

OK, here's an example. Note this is not production code. It needs quite a bit of work. The array design could be a lot better, but I just threw it together from some old stuff I had:

/* Data from DB [0 => country id, 1=> country name, 2=>city id, 3=>city name, 4=>seller id, 5=>seller name] 
   Typically from a multiple join query, e.g.

   SELECT a.country_id, a.country, b.city_id, b.city, c.seller_id, c.seller 
    FROM countries AS a 
        INNER JOIN cities AS b 
            ON a.country_id = b.country_id 
        INNER JOIN sellers AS c 
            ON b.city_id = c.city_id 
    ORDER BY a.country, b.city, c.seller
*/

$dbarr = array(
    array(1,'Canada',7,'Montreal',100,'Jacques'),
    array(1,'Canada',7,'Montreal',101,'Pierre'),
    array(1,'Canada',8,'Toronto',102,'Andre'),
    array(1,'Canada',9,'Ottawa',103,'Michel'),
    array(2,'USA',10,'St Louis',300,'Harold'),
    array(2,'USA',11,'New Orleans',301,'Henri'),
    array(2,'USA',12,'Baton Rouge',302,'Elmer'),
    array(3,'UK',13,'Cardiff',30,'Ioan'),
    array(3,'UK',13,'Cardiff',31,'Tudor'),
    array(3,'UK',18,'Swansea',32,'Mihangel'),
    array(3,'UK',18,'Swansea',33,'Meurig'),
    array(3,'UK',18,'Swansea',34,'Iwan'),
    array(4,'Ireland',17,'Cork',631,'Paddy'),
    array(4,'Ireland',17,'Cork',632,'Seamus'),
    array(4,'Ireland',17,'Cork',633,'Sean'),
    array(4,'Ireland',17,'Cork',634,'Sean L'),
);
//For inserting specific data on load
$firstCountry = $dbarr[0][0]; //country id
$firstCity = $dbarr[0][2]; //city id

//Create multidimensional array
$phpArray = makeArr($dbarr);

//Create data to JS variable
$json = json_encode($phpArray);

//Function to make multidimensional array
function makeArr($arr){
    foreach($arr as $line){
        $r['country'][$line[0]] = $line[1];
        $r['city'][$line[0]][$line[2]] = $line[3];
        $r['sellers'][$line[2]][$line[4]] = $line[5];
    }
    return $r;  
}

//Create innars of dropdowns
function makeSelect($arr){
    $output = '';
    foreach($arr as $k => $v){
        $output .= "\n\t<option value='$k'>$v</option>";
    }
    return $output;
}
?>

<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>...</title>
</head>

<body>
<select id="country">
    <?php echo makeSelect($phpArray['country']);?>
</select>
<select id="city">
    <?php echo makeSelect($phpArray['city'][$firstCountry]);?>
</select>
<select id="seller">
    <?php echo makeSelect($phpArray['sellers'][$firstCity]);?>
</select>

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.0/jquery.min.js"></script>
<script>
    //store data in json format from php
    var json = <?php echo $json;?>

    //change selection in country dropdown
    $('#country').change(function(){
        country = $(this).val();
        changeCity(country);
    });

    //change selection in city dropdown
    $('#city').change(function(){
        city = $(this).val();
        changeSellers(city);
    });

    //change city content in response to change country
    //also run the change seller content
    function changeCity(country){
        output = '';
        cities  = json['city'][country];
        $.each(cities, function(i,v){
            output += '<option value="' + i + '">' + v + '</option>'; 
        });
        $('#city').html(output);
        city = $('#city').val();
        changeSellers(city)
    }

    //change seller content in response to change city
    function changeSellers(city){
        output = '';
        sellers = json['sellers'][city];
        $.each(sellers, function(i,v){
            output += '<option value="' + i + '">' + v + '</option>'; 
        });
        $('#seller').html(output);
    }
</script>
</body>
</html>

There are many ways to do this, and the code could be a lot trimmer. But should give you an idea. You could use js to populate the dropdowns on first load too - you don't have to use php for that.