MYSQL Select based on HTML select option

Question

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);
            }
        })
    });

moni_dragu · Accepted Answer · 2017-01-13 10:32:32Z

1

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 -->

answered Jan 13, 2017 at 10:32

moni_dragu

1,15310 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

bob Over a year ago

I don't need a url if i'm sending it to the same page. by defualt ajax sends it to the same page.

moni_dragu Over a year ago

Above I have put piece of your code together to make it work. If you want to send the call to the same page then the success function is not correct because it loads the entire page into your <select >.

bob Over a year ago

Good point, I tried this structure however the second html select is not being populated on change of the first, no errors

moni_dragu Over a year ago

Can you post the exact code you tried? Maybe a stupid question, but do you have data in the database to populate the second select? :)

bob Over a year ago

I did exactly what you suggested, seperated it in to two files, I added a html select with the class="company_name_select" and the ajax call above into the index file and the php above into the load_page.php and yes, I have data in the database :P

|

Collectives™ on Stack Overflow

MYSQL Select based on HTML select option

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related