codeigniter - display database query result on same form using input data

Question

I have trawled the net for sometime now trying to find a solution which cold assist me, but have had no luck.

I have a simple sales form in which the user selects a product from a drop down list. on selecting a value, I want the input box value to get passed to a database query, and the query result (price) be displayed on the form. if possible I want the result to populate an input box so the salesman can adjust as needed.

I am using codeigniter which makes finding a good example quite difficult.

Controller

function new_blank_order_lines() 
  {
   $this->load->view('sales/new_blank_order_lines');
  }

Model

 function get_sku_price($q){
    $this->db->select('ProductPrice');
    $this->db->where('ProductCode', $q);
    $query = $this->db->get('ProductList');
    if($query->num_rows > 0){
      foreach ($query->result_array() as $row){
        $row_set[] = htmlentities(stripslashes($row['ProductPrice'])); //build an array
      }
      $this->output->set_content_type('application/json')->set_output(json_encode($row_set));

    }
  }

View

<table>
  <tr><td>Product</td><td>Price</td></tr>
  <tr>
   <td><select name="product">
       <option="sku1">product 1</option>
       <option="sku2">product 2</option>
       <option="sku3">product 3</option>
   <select></td>
   <td><input type="text" id="price" name="price" /></td>
  </tr>
</table>

I have loaded the jquery library, 1.9.1.

I have got autocomplete working but the sytax is just not the same.

So what I am wanting, is that when I select a product code from the product drop down list, the value is passed to the model, the query result(price) is then displayed in the input box price.

Can anyone provide some insight on how to do this, or a good working example?

Thanks a million, this community is awesome!

Fabio

Controller:

function new_blank_order_lines() 
  {
   $this->load->view('sales/new_order');
  }

The view:

<script>
$("#product").change(function () {
    //get the value of the select when it changes
    var value = $("#product").val()

    //make an ajax request posting it to your controller
    $.post('<?=base_url("sales/get_sku_prices")?>', {data:value},function(result) {
      //change the input price with the returned value
      $('#price').value(result);
    });
});
</script>
  <table>
  <tr><td>Product</td><td>Price</td></tr>
  <tr>
   <td><select name="product" id="product">
       <option value="sku1">product 1</option>
       <option value="sku2">product 2</option>
       <option value="sku3">product 3</option>
   </select></td>
   <td><input type="text" id="price" name="price" /></td>
  </tr>
</table>

Controller to fetch database data:

  function get_sku_prices(){
    //check if is an ajax request
    if($this->input->is_ajax_request()){
        //checks if the variable data exists on the posted data
        if($this->input->post('data')){
            $this->load->model('Sales_model');
            //query in your model you should verify if the data passed is legit before querying
            $price = $this->your_model->get_sku_price($this->input->post('data', TRUE));

            echo $price;
        }
    }
}

Model:

  function get_sku_price($q){
    $this->db->select('ProductPrice');
    $this->db->where('ProductCode', $q);
    $query = $this->db->get('ProductList');
    if($query->num_rows > 0){
      foreach ($query->result_array() as $row){
        $row_set[] = htmlentities(stripslashes($row['ProductPrice'])); //build an array
      }
      $this->output->set_content_type('application/json')->set_output(json_encode($row_set));

    }
  }

use jquery to create a listener for the change event on the select, when that event is fired the selected value is passed to that function, that will make an ajax request to one method on your controller responsible to receive the value, pass it to the model then the model will query your database, return the value to the controller and then the controller echo that value, then in the ajax success method you just put that value on the input textbox — Fabio Antunes
– Fabio Antunes, Commented Mar 19, 2013 at 20:26
Thanks Fabio. I am new to codeigniter and ajax / javascript so if you have an example or a good working link to read please let me know. Thanks, — JustStarting
– JustStarting, Commented Mar 19, 2013 at 20:29

Fabio Antunes · Accepted Answer · 2016-02-23 10:12:52Z

1

Your View:

<table>
    <tr>
        <td>Product</td>
         <td>Price</td>
    </tr>
    <tr>
        <td>
            <select name="product" id="product">
                <option value="sku1">product 1</option>
                <option value="sku2">product 2</option>
                <option value="sku3">product 3</option>
            </select>
            </td>
        <td>
            <input type="text" id="price" name="price" />
        </td>
    </tr>
</table>

The javascript

<script>
  $("#product").change(function () {
    //get the value of the select when it changes
    var value = $("#product").val()

    //make an ajax request posting it to your controller
    $.post('<?=site_url("controller/function")?>', {data:value},function(result) {
      //change the input price with the returned value
      $('#price').value(result);
    });
  });
</script>

The controller:

public function your_funtion(){
    //check if is an ajax request
    if($this->input->is_ajax_request()){
        //checks if the variable data exists on the posted data
        if($this->input->post('data')){
            $this->load_model('your_model')
            //query in your model you should verify if the data passed is legit before querying
            $price = $this->your_model->get_price($this->input->post('data', TRUE));

            echo $price;
        }
    }
}

edited Feb 23, 2016 at 10:12

answered Mar 19, 2013 at 20:47

Fabio Antunes

22.9k17 gold badges83 silver badges96 bronze badges

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

16 Comments

JustStarting Over a year ago

Thanks Fabio, you are too generous. I will attempt this now and revert if any issues. thanks again.

JustStarting Over a year ago

Thanks again Fabio. tried to make the changes as per your advice. firebug is displaying error: 400 bad request XHR shows error: The URI you submitted has disallowed characters. I will update the question with my actual code for you to refer to. Thanks again.

JustStarting Over a year ago

Hello Fabio, I updated my question to assist. have I gone wrong somewhere?

Fabio Antunes Over a year ago

put this $this->load->helper('url'); on the beginning get_sku_prices()

Fabio Antunes Over a year ago

sorry it's the new_blank_order_lines(), put this $this->load->helper('url') on the beginning.

|

bipen · Accepted Answer · 2013-03-19 20:32:10Z

0

use jquery's ajax,post or get and change event..using post here

example..

 $('select[name="product"]').change(function(){
      var val=$(this).val();
      $.post('path/to/controller',{data:val},function(result){
          $('#price').val(result.price);
      }, "json");
 });

conroller funciton

$product=$this->input->post('data');  //this will give you the selected value of select
//make query to db in model..get price and
$price = ..//price that you got from db
echo json_encode(array('price'=> $price));

answered Mar 19, 2013 at 20:32

bipen

36.6k9 gold badges51 silver badges62 bronze badges

2 Comments

JustStarting Over a year ago

Thank you bipen. does this display the data without refreshing the page? would it use a separate function in the controller? any example or reading that I can look at that meets my requirement?

bipen Over a year ago

hmmm.. this does it wihtout refreshing the page... you need to create a seperate function in a controller that gets the required price by db query from the selected fields... and send it as response in JSON which i expalind in my answer in controller function part

Collectives™ on Stack Overflow

codeigniter - display database query result on same form using input data

2 Answers 2

16 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

16 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related