title: codeigniter - display database query result on same form using input data
body: <p>I have trawled the net for sometime now trying to find a solution which cold assist me, but have had no luck.</p>
  
  <p>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.</p>
  
  <p>I am using codeigniter which makes finding a good example quite difficult.</p>
  
  <p><strong>Controller</strong></p>
  
  <pre><code>function new_blank_order_lines() 
    {
     $this-&gt;load-&gt;view('sales/new_blank_order_lines');
    }
  </code></pre>
  
  <p><strong>Model</strong></p>
  
  <pre><code> function get_sku_price($q){
      $this-&gt;db-&gt;select('ProductPrice');
      $this-&gt;db-&gt;where('ProductCode', $q);
      $query = $this-&gt;db-&gt;get('ProductList');
      if($query-&gt;num_rows &gt; 0){
        foreach ($query-&gt;result_array() as $row){
          $row_set[] = htmlentities(stripslashes($row['ProductPrice'])); //build an array
        }
        $this-&gt;output-&gt;set_content_type('application/json')-&gt;set_output(json_encode($row_set));
  
      }
    }
  </code></pre>
  
  <p><strong>View</strong></p>
  
  <pre><code>&lt;table&gt;
    &lt;tr&gt;&lt;td&gt;Product&lt;/td&gt;&lt;td&gt;Price&lt;/td&gt;&lt;/tr&gt;
    &lt;tr&gt;
     &lt;td&gt;&lt;select name="product"&gt;
         &lt;option="sku1"&gt;product 1&lt;/option&gt;
         &lt;option="sku2"&gt;product 2&lt;/option&gt;
         &lt;option="sku3"&gt;product 3&lt;/option&gt;
     &lt;select&gt;&lt;/td&gt;
     &lt;td&gt;&lt;input type="text" id="price" name="price" /&gt;&lt;/td&gt;
    &lt;/tr&gt;
  &lt;/table&gt;
  </code></pre>
  
  <p>I have loaded the jquery library, 1.9.1.</p>
  
  <p>I have got autocomplete working but the sytax is just not the same.</p>
  
  <p>So what I am wanting, is that when I select a product code from the <code>product</code> drop down list, the value is passed to the model, the query result(price) is then displayed in the input box <code>price</code>.</p>
  
  <p>Can anyone provide some insight on how to do this, or a good working example?</p>
  
  <p>Thanks a million, this community is awesome!</p>
  
  <p><strong>Fabio</strong></p>
  
  <p>Controller:</p>
  
  <pre><code>function new_blank_order_lines() 
    {
     $this-&gt;load-&gt;view('sales/new_order');
    }
  </code></pre>
  
  <p>The view:</p>
  
  <pre><code>&lt;script&gt;
  $("#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('&lt;?=base_url("sales/get_sku_prices")?&gt;', {data:value},function(result) {
        //change the input price with the returned value
        $('#price').value(result);
      });
  });
  &lt;/script&gt;
    &lt;table&gt;
    &lt;tr&gt;&lt;td&gt;Product&lt;/td&gt;&lt;td&gt;Price&lt;/td&gt;&lt;/tr&gt;
    &lt;tr&gt;
     &lt;td&gt;&lt;select name="product" id="product"&gt;
         &lt;option value="sku1"&gt;product 1&lt;/option&gt;
         &lt;option value="sku2"&gt;product 2&lt;/option&gt;
         &lt;option value="sku3"&gt;product 3&lt;/option&gt;
     &lt;/select&gt;&lt;/td&gt;
     &lt;td&gt;&lt;input type="text" id="price" name="price" /&gt;&lt;/td&gt;
    &lt;/tr&gt;
  &lt;/table&gt;
  </code></pre>
  
  <p>Controller to fetch database data:</p>
  
  <pre><code>  function get_sku_prices(){
      //check if is an ajax request
      if($this-&gt;input-&gt;is_ajax_request()){
          //checks if the variable data exists on the posted data
          if($this-&gt;input-&gt;post('data')){
              $this-&gt;load-&gt;model('Sales_model');
              //query in your model you should verify if the data passed is legit before querying
              $price = $this-&gt;your_model-&gt;get_sku_price($this-&gt;input-&gt;post('data', TRUE));
  
              echo $price;
          }
      }
  }
  </code></pre>
  
  <p>Model:</p>
  
  <pre><code>  function get_sku_price($q){
      $this-&gt;db-&gt;select('ProductPrice');
      $this-&gt;db-&gt;where('ProductCode', $q);
      $query = $this-&gt;db-&gt;get('ProductList');
      if($query-&gt;num_rows &gt; 0){
        foreach ($query-&gt;result_array() as $row){
          $row_set[] = htmlentities(stripslashes($row['ProductPrice'])); //build an array
        }
        $this-&gt;output-&gt;set_content_type('application/json')-&gt;set_output(json_encode($row_set));
  
      }
    }
  </code></pre>