CODEWITHSUNDEEP
phpjquerymysqlselect
bob
bob

Reputation: 486

MYSQL Select based on HTML select option

I have a select that is echoing out a column (jobTitle) from the database, on change I'm trying to display in another html select, selecting another column (company_name), but only values related to the first html select.

I'm not getting an errors. The first html select value is being passed, but for some reason it's not selecting from the db based on that in the second select.

I'm thinking the issue is with my second sql query.

P.S if anyone knows of a more effective way of doing this i'd be most gratefully to find out.

PHP & HTML:

<select name="jobtitle_select" class="jobtitle_select">
    <option class="" name="" value="" >-- Job title --</option> 
    <?php  
    $sql = $dbh->prepare("SELECT * FROM jobs_list");
    if($sql->execute()) {
        $sql->setFetchMode(PDO::FETCH_ASSOC);
    }
    while($row = $sql->fetch()) { 
        $jobTitle = $row['jobTitle'];
        echo "<option class='' name='' value='$jobTitle' > $jobTitle </option>"; 
    } // end of while //  ?>
</select>
<?php 
$jobtitle_select = $_POST['jobtitle_select'];
if ($jobtitle_select){
    $sql = $dbh->prepare("SELECT * FROM jobs_list WHERE company_name = :jobtitle_select");
    $sql->bindParam(':jobtitle_select', $jobtitle_select, PDO::PARAM_STR);  
    if($sql->execute()) {
        $sql->setFetchMode(PDO::FETCH_ASSOC);
    }
?>     
<select class="company_name_select" >   
    <option class="" name="" value="" >-- Company name --</option>      
    <?php while($row = $sql->fetch()) { 
        $company_name = $row['company_name'];
        echo "<option class='' name='' value='$company_name'> $company_name   </option>"; 
    } // end of while //        
}?>  <!-- end of if -->
</select>

JQUERY:

$('.jobtitle_select').change(function(){
        $.ajax({
            //create an ajax request to load_page.php
            type: "POST", 
            data:$('.jobtitle_select'),     
            dataType: "html",   //expect html to be returned                
            success: function(date){
                $('.company_name_select').html(date);
            }
        })
    });

Upvotes: 3

Views: 1721

Answers (1)

moni_dragu
moni_dragu

Reputation: 1163

Your ajax call is not correct, or better say not complete. You need to provide the url, and the posted variable with name and value.

$('.jobtitle_select').change(function(){
    $.ajax({
        //create an ajax request to load_page.php
        url: "load_page.php",
        type: "POST", 
        data: {jobtitle_select: $('.jobtitle_select').val()},     
        dataType: "html",   //expect html to be returned                
        success: function(date){
            $('.company_name_select').html(date);
        }
    })
});

and your load_page.php file should have the php of code to read data from the database and the html code to display only the inner part of the select:

<?php 
$jobtitle_select = $_POST['jobtitle_select'];
if ($jobtitle_select){
    $sql = $dbh->prepare("SELECT * FROM jobs_list WHERE company_name = :jobtitle_select");
    $sql->bindParam(':jobtitle_select', $jobtitle_select, PDO::PARAM_STR);  
    if($sql->execute()) {
        $sql->setFetchMode(PDO::FETCH_ASSOC);
    }
?>     

    <option class="" name="" value="" >-- Company name --</option>      
    <?php while($row = $sql->fetch()) { 
        $company_name = $row['company_name'];
        echo "<option class='' name='' value='$company_name'> $company_name   </option>"; 
    } // end of while //        
}?>  <!-- end of if -->

Upvotes: 1

Related Questions