Auto filter between two search inputs

[paul-lucas]

Hello, please tell me how to make auto filter between two search inputs? For exemple: while I choose a Zip code on list, then list of City name is filterd automatic according to this Zip code.

These are my code:

// Connect to db:
        $dsn      = "mysql:host=".DB_HOST.";dbname=".DB_NAME;
        $db             = new PDO($dsn, DB_USER, DB_PASSWORD, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
        $sqlAll = "SELECT count(*)  FROM `ab_staff` WHERE `anadd` IS NOT NULL";
        $sth = $db->query($sqlAll);
        $rowAll = $sth->fetch(PDO::FETCH_ASSOC);
        $cntAll = number_format($rowAll['count(*)'], 0, ',', '.');          
        $title = "Trouver votre medecin/therapeute parmi nos $cntAll membres";

        // Remove by Hoa 24/03/2015
        // wp_enqueue_script('google-map-libs');
        // wp_enqueue_script('mymap');
        // Remove by Hoa 24/03/2015

        $template = $events_page =  get_pages(array('meta_key' => '_wp_page_template','meta_value' => 'recherche-medecin.php'));
        $output = ($title_style == 'simple')?'<div class="col-title"><h2>'.$title.'</h2></div>':'<div class="gray-title"><span><i class="fa fa-search"></i></span><h3>'.$title.'</h3><h5>'.$tag_line.'</h5></div>';
        $output .= '<div class="find-doctor">
                        <form class="doctor-search" method="post" action="'.get_permalink(sh_set(sh_set($template, 0), 'ID')).'">
                            <input type="hidden" name="page_id" value="'.sh_set(sh_set($template, 0), 'ID').'" />
                            <input class="span3" type="text" name="NomPrenom" placeholder="Nom/Prénom"/>
                            <select class="span3" name="Categorie">
                            <option value="Tous les spécialistes">Tous les spécialistes</option>';
// Categorie                                    
        $sqlRech = "SELECT distinct `ancat` from `ab_staff` ORDER BY `ancat`";
        $sthRech = $db->query($sqlRech);
        while ($rowRech = $sthRech->fetch(PDO::FETCH_ASSOC))    {
            $cat = trim($rowRech['ancat']);
            $selection = "";
            if ( $cat === $Categorie ) {
                $selection = " selected";
            }   
            $output .='<option value="'.$cat.'" '.$selection.'>'.ucwords(strtolower($cat))."</option>";
        }
        $output .='</select>';
// Code Postal
        $output .='<select class="span3" name="CodePostal"></option>';
        $output .='<option value="" disabled selected style="display:none;">Code Postal</option>';
        $sqlCP = "SELECT * FROM `HTC_zipcode` GROUP BY `zc_Cp` ORDER BY `zc_Cp`";
        $sthCP = $db->query($sqlCP);
        $outputVille = "";
        while ($rowCP = $sthCP->fetch(PDO::FETCH_ASSOC))    {
            $cp = $rowCP['zc_Cp'];
            $selection = "";
            if ( $cp === trim($CodePostal) ) {
                $selection = " selected";
            }
            $output .='<option value="'.$cp.'" '.$selection.'>'.$cp.'</option>';
        }
        $output .='</select>';

// Ville        
        $output .= '<select class="span3" name="Ville"><option value=""></option>';
        $output .='<option value="" disabled selected style="display:none;">Ville</option>';
        $sqlCP = "SELECT * FROM `HTC_zipcode` GROUP BY `zc_Ville` ORDER BY `zc_Ville`";
        $sthCP = $db->query($sqlCP);
        while ($rowCP = $sthCP->fetch(PDO::FETCH_ASSOC))    {
            $selection = "";
            $ville = $rowCP['zc_Ville'];
            $selection = $selectionVille = "";
            if ( $ville === $Ville ) {
                $selection = " selected";
            }
            $output .= '<option value="'.$ville.'" '.$selection.'>'.$ville.'</option>';
        }
        $output .='</select>
                            <input type="hidden" name="Recherche" value=1>
                            <input type="submit" name="Recherche1" keyword="name" value=""/>
                        </form>
                        </div>';
        // print_r($atts);
        return $output ;
    }

Thanks!

[Keimpe]

change
$sqlCP = "SELECT * FROMHTC_zipcodeGROUP BYzc_VilleORDER BYzc_Ville";

into
Doing a search on postalcode

$sqlCP = "SELECT * FROMHTC_zipcodeWHEREzc_CpLIKE '%"$cp"%' GROUP BYzc_VilleORDER BYzc_Ville";

[diafol]

Just a few remarks here. This code looks horrendously mashed. Where possible have static form markup and just supply the variable content with PHP variables. This also allows you to Ajaxify "the change Ville due to change in Zipcode" process.

Example:

function get_ville($db, $zipcode)
{
    $stmt = $db->prepare("SELECT DISTINCT `zc_Ville` FROM `HTC_zipcode` WHERE `zc_Cp` LIKE ? ORDER BY `zc_Ville`);
    $stmt->execute(array($cp.'%'));
    $options= '';
    while($data = $stmt->fetchColumn())
    {
        $options .= "<option value='$data'>$data</option>\n";
    }
    return $options;
}
$villeOptions = '';
if(isset($_GET['zip']))
{
    //assume DBH is PDO and set up already as $pdo.
    $villeOptions = get_ville($pdo, $_GET['zip']);
    if(isset($_GET['ajax'])) echo $villeOptions;
}

So the above can be used to populate the Ville dropdown from a default value if required (e.g. from GeoData/GPS) - depending if that's what you need but it can also react to an Ajax event, for example the blur() event on the zipcode dropdown. If you decide to do this, then pass the 'zipcode' as a 'zip' param and an ajax param set to '1' via GET and place the resulting output (html) into the Ville dropdown.

On page load:

<select id="ville" name="ville">
    <?=$villeOptions;?>
</select>

If using jQuery - after your ajax routine, e.g.:

ajaxRoutine.done(function(data)){
    $('#ville').html(data);
});

Sorry bit all over the place, but your code looks very difficult to manage.