Running a query based on another query using CodeIgniter

Question

I am populating information from a database to a drop down list on my home page. I have a table called "banks" with the respective column names "id" and "bankname". I also have another table called "bankbranches" with the respective column names "id","bankbranch" and "bank". The "bank" column in the bank branch has the same "id" of the "table". I want to run a query in my model to popupulate the bank branch based on the choice of the bank drop down list before it. So far, in my model I have:

        function getBank()
        {
            $query = $this->db->query('SELECT id,bankname FROM banks');
            return $query->result_array();
        }
        function getBankBranch()
        {
            $query = $this->db->query('SELECT id,bankbranch FROM bankbranches');
            return $query->result_array();

But the bank branch drop down is listing all the bank branches in my database. I want the drop down list for the bank branch to be populated based on the selection of the bank drop down by the user. Here is my view:

        <label>Bank</label>
                <select name="banks" id="banks">
                <option value="">--Please select--</option>
                <?php
                foreach($banks as $bank)
                {
                echo '<option value="'.$bank['id'].'">'.$bank['bankname'].'</option>';

                }
                ?>  
        </select> <br/>

        <label>Bank Branch</label>
                <select name="bankbranches" id="bankbranches">
                <option value=""></option>
                <?php
                foreach($bankbranch as $branch)
                {
                    echo '<option value="'.$branch['id'].'">'.$branch['bankbranch'].'</option>';
                }
                ?>  
        </select> <br/>

Here is my controller:

        public function index()
        { 
          $this->load->helper('form');
          $data['cities'] = $this->site_model->getCity();
          $data['modes'] = $this->site_model->getPaymentMode();
          $data['banks'] = $this->site_model->getBank();
          $data['bankbranch'] = $this->site_model->getBankBranch();
          $data['categories'] = $this->site_model->getCategories();
          $data['staffs'] = $this->site_model->getStaff();

          $this->load->view('supplier_add',$data); 
        }

I have really tried to be elaborate.

noufalcep · Accepted Answer · 2016-10-27 08:57:21Z

1

You can use jquery and ajax to get branches when select a bank.

In your view :-

<label>Bank</label>
<select name="banks" id="banks">
    <option value="">--Please select--</option>
    <?php foreach($banks as $bank) {
        echo '<option value="'.$bank['id'].'">'.$bank['bankname'].'</option>';
    } ?>  
</select><br/>

<label>Bank Branch</label>
<select name="bankbranches" id="bankbranches">
    <option value=""></option>
</select> <br/>

<script type= text/javascript>
    $('#banks').on('change', function() {
        var bank = $(this).val();
        $.ajax({
            type: 'post',
            url: '<?php echo base_url('controller/get_branches'); ?>, //set your controller name here
            data: { bank_id : bank },
            success: function (response) {
                 $('#bankbranches').html(response); 
            }
        });
    });
</script>

In your controller :-

public function index()
{ 
    $this->load->helper('form');
    $data['cities'] = $this->site_model->getCity();
    $data['modes'] = $this->site_model->getPaymentMode();
    $data['banks'] = $this->site_model->getBank();
    $data['categories'] = $this->site_model->getCategories();
    $data['staffs'] = $this->site_model->getStaff();

    $this->load->view('supplier_add',$data); 
}

public function get_branches()
{ 
     $bank_id = $this->input->post('bank_id');
     $bankbranch = $this->site_model->getBankBranch($bank_id);
     foreach($bankbranch as $branch) {
         echo '<option value="'.$branch['id'].'">'.$branch['bankbranch'].'</option>';
     }
}

In your model

function getBankBranch($bank_id)
{
     $query = $this->db->query('SELECT id,bankbranch FROM bankbranches WHERE bank='.$bank_id);
     return $query->result_array();
}

answered Oct 27, 2016 at 8:57

noufalcep

3,54415 gold badges36 silver badges54 bronze badges

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

4 Comments

ALFIE Over a year ago

I am getting an error of "undefined variable bank_id"....which gets me to think, this line: $query = $this->db->query('SELECT id,bankbranch FROM bankbranches WHERE bank='.$bank_id); Is it not supposed to be: $query = $this->db->query('SELECT id,bankbranch FROM bankbranches WHERE bank="$bank_id"); for the column name bank_id? or?

noufalcep Over a year ago

May be you are missed to add function parameter bank_id getBankBranch($bank_id)

ALFIE Over a year ago

Yes, thanks a lot. I had missed that part and after correcting it, the bank branch drop down is still empty. There are no errors

ALFIE Over a year ago

Thanks a lot, the error was due to the invalid url I had placed in my script, the drop down now populates the information.

Razib Al Mamun · Accepted Answer · 2016-10-27 10:34:17Z

1

In your model add a param in getBankBranch function, param name is $bank_id and then add a condition in your sql query.

In Model :

<?php
function getBankBranch($bank_id){
    $query = $this->db->query("SELECT id,bankbranch FROM bankbranches where bank = '$bank_id'");
    return $query->result_array();
}
?>

Add a new method in your controller BankBranch. this method retrieve branch name bank_id wise.

In Controller :

<?php
public function BankBranch() { 
    $this->load->helper('form');
    if ($this->input->is_ajax_request()) {
        $bank_id = $this->input->post('bank_id');
        $bankbranch = $this->site_model->getBankBranch($bank_id);

        echo '<option value=""></option>';
        foreach($bankbranch as $branch)
        {
            echo '<option value="'.$branch['id'].'">'.$branch['bankbranch'].'</option>';
        }
    }
}
?>

View Page :

<label>Bank</label>
        <select name="banks" id="banks">
        <option value="">--Please select--</option>
        <?php
        foreach($banks as $bank)
        {
        echo '<option value="'.$bank['id'].'">'.$bank['bankname'].'</option>';

        }
        ?>  
</select> <br/>

<label>Bank Branch</label>
<select name="bankbranches" id="bankbranches">

</select>

Finally you can use jquery, and call your BankBranch() method from your controller by using AJAX Request.

In Script :

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>

<script>
jQuery(document).ready(function ($) {
    $('#banks').on('change', function (e) {
        e.preventDefault();
        var bank_id = $(this).val();

        var base_url = "<?php base_url(); ?>";

        $.ajax({
            url: base_url+"your_controller/BankBranch", // Url to which the request is send
            type: "POST", // Type of request to be send, called as method
            data: {bank_id:bank_id}, // Data sent to server, a set of key/value pairs representing form fields and values
        }).done(function (data) {
            $("#bankbranches").html(data);            
        }).fail(function (data) {
            console.log('failed');
        });
    });
}(jQuery));
</script>

edited Oct 27, 2016 at 10:34

answered Oct 27, 2016 at 8:51

Razib Al Mamun

2,7111 gold badge19 silver badges24 bronze badges

11 Comments

ALFIE Over a year ago

I am getting this error: Unknown column '$bank_id' in 'where clause' The columns for the table "bankbranches" are "id","bankbranch" and "bank". Should I change the '$bank_id' to '$bank'? Since the column is 'bank'

Razib Al Mamun Over a year ago

your bankbranches table's column name is id, bankbranch, bank. right?

Razib Al Mamun Over a year ago

which data you are saved in bank column?

Razib Al Mamun Over a year ago

Please show your letest getBankBranch function from your model in comment box

ALFIE Over a year ago

Yes, it is like this: bankbranches table's column name is id, bankbranch, bank...the bank column has numbers....like 1, 2, 3, 4, 5, 6 Which are the respective id's for different banks

|

Collectives™ on Stack Overflow

Running a query based on another query using CodeIgniter

2 Answers 2

4 Comments

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related