Multi field selection in php

I have a product table with product code, description,selling price, cost,category etc. I have a drop down to select the product which also shows the description and the product price.
I would like to capture the product price and show/update it in a field when ever the user selects a product. on change the field should also change with the new value. How can I achieve this. I have attached my code for your kind perusal.
include('function/db_connect.php');
//session_start();
$price = Null;
//$items = Null;
if($_POST)
{
$var1 = $_POST['country'];
$var2 = $_POST['type'];
 if ($var1 != '') {        
       echo "<label>Item<span style='color:red'>*</span></label><span style='color:red'></span></label><span class='address'>";
       echo "<select id='items' name='items' style = 'width: 546px;' onChange='get_price(this.value)'>";          
       $sql = "SELECT * FROM tbl_product WHERE country_id = '$var1' AND type_id = '$var2'";  
       $db  = new DB_CONNECT();    
       $result = mysql_query($sql);
       $myarray = array();
       echo "<option value=''>Select</option>";
       while ($row = mysql_fetch_array($result)) {
            $idp = $row["product_id"];
            $iddes = $row["product_desc"];
            $selp = $row["product_sell"];
            $costp = $row["product_cost"];


       echo "<option value='" . $idp . "'>" . $iddes . "==>".$selp ."</option>";

       }
       echo "</select><label>Item</label></span><span class='address'>";

    }  
       echo "</div>";        
       echo "<br>";    



       echo "<div><label></label><span class='name'><button name = 'data'  type ='button' onclick= 'valprice('items')'>Validate value</button></span></div>";      

    }
?>
kamal_gAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
Hell, @kamal_g and welcome to E-E.

To the instant question, it might be helpful to see your table schema.  It looks like that code queries a database and creates an HTML document with a <select> control.  This would allow a client to choose one of the <option> values.  It looks like there is a JavaScript function named get_price() that will be invoked when the <select> changes.  This function would need to have some knowledge of the price information that is in the database.  So the complete HTML document, and the JavaScript are useful things, like your database schema, that we need to see.

If you're new to PHP and want to get a foundation in how the language works, this article can help you find good learning resources and more importantly, avoid badly written examples of obsolete or dangerous PHP code.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html

In a related matter, the code here uses the MySQL extension.  PHP is doing away with the MySQL extension, so you want to change to MySQLi or PDO as soon as possible.  This article explains why PHP is making this change and what you must do to keep your scripts running in the future.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
Eddie ShipmanAll-around developerCommented:
I would write a separate script to retrieve the price and call it via Ajax on the select change. In the success handler, change the display.

Code to retrieve the price:
<?php
    include('function/db_connect.php');
    $sql    = "SELECT product_price FROM tbl_product WHERE product_id = ".$_GET["product_id"];
    $db     = new DB_CONNECT();
    $result = mysql_query($sql);     
    $row    = mysql_fetch_array($result);
    $price  = $row["product_price"];
    mysql_close($db);
    echo $price;
?>

Open in new window


Code to get the price when the select is changed, I'm using jQuery because you didn't show if you were or not; you didn't even show your getprice() function code. I also do not know the id of the "field" where you want to put the price when it is retrieved.
<script type="text/javascript">
    $(document).ready(function() {
        $("#items").bind("change", ItemsChangeHandler);
    });
     
    function ItemsChangeHandler(event) {
        $.ajax({async: false
               , type:'GET'
               , url: "getPrice.php?product_id = "+ $(this).val();
               , dataType: 'html'
               , success: function(data) {
                    $("#id_of_field_to_put_price).val(data);
               }
        });
    }
    
</script>

Open in new window

Make this change to your PHP that writes our the select:
echo "<select id='items' name='items' style = 'width: 546px;'>";

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Julian HansenCommented:
I think I understand what you want in  general but need more info on the specifics.

What you can do is put the price as a data attribute in the <option> and then retrieve it on the change event. The sample below (online version here  is a modified version of your code specifically
1. Uses MySQLi instead of MySQL
2. Uses HEREDOC output instead of quoted echo's
3. Removes some orphan tags and closing tags
4. Adds a data-price custom attribute to the select options.
5. Adds a jQuery function that fires when the select is changed to populate another field with the value that was in the data-price attribute of the <option>
The sample contains in page source but it is repeated here.
HTML Source
<!-- SOURCE LISTING ACTIVE -->
<!doctype html>
<html>
<head>
<title>Test</title>
<link href="css/bootstrap.css" rel="stylesheet" />
<link href="css/custom.css" rel="stylesheet" />
<style type="text/css">
</style>
</head>
<body>
<div class="wrapper">
  <header>
    <div class="container">
    <h1 class="col-lg-9">Linking select change to another input</h1>
    </div>
  </header>
  <div class="container">
    <h5>Author: Julian Hansen</h5>
<?php
  require_once('t1056-select.php');
?>
    Price <input type="text" id="price" />
    <script src="js/psource.js"></script>
  </div>
</div>
<footer>
  <div class="container">
    Copyright Julian Hansen &copy; 2015
  </div>
</footer>

<script src="http://code.jquery.com/jquery.js"></script>

<!-- Latest compiled and minified JavaScript -->
<script src="js/bootstrap.js"></script>
<!-- START "jQuery Source" -->
<script>
$(function() {
  $('select').change(function() {
    var selected = $(this).find('option:selected');
    $('#price').val(selected.data('price'));
  });
});
function getPrice()
{
}
</script>
<!-- END -->
<!-- INCLUDE "t1056-selecta.php:PHP Source" -->

<script src="js/ga.js"></script>
</body>
</html>

Open in new window

PHP Source
<?php
$host     = 'localhost';
$user     = 'user';
$password = 'password';
$database = 'database';
$db = new mysqli($host, $user, $password, $database);
if ($db->connect_error) {
  die('Connect Error (' . $db->connect_errno . ') ' . $db->connect_error);
}      

$price = Null;
//$items = Null;
$_POST['country'] = '1';
$_POST['type'] = '1';
if(1==1)
{
  $var1 = $_POST['country'];
  $var2 = $_POST['type'];
  if ($var1 != '') {        
    echo <<< HTML
      <label>Item<span style="color:red">*</span></label>
      <span style="color:red"></span>
      <span class="address">
        <select id="items" name="items"  style = "width: 546px;" onChange="get_price(this.value)">
          <option value="">Select</option>
        
HTML;
    $sql = "SELECT * FROM tbl_product WHERE country_id = '{$var1}' AND type_id = '{$var2}'";  
    $result = $db->query($sql);
    $myarray = array();
    while ($row = $result->fetch_array()) {
      $idp = $row["product_id"];
      $iddes = $row["product_desc"];
      $selp = $row["product_sell"];
      $costp = $row["product_cost"];
      
      echo <<< HTML
          <option value="{$idp}" data-price="{$costp}">{$iddes}==>{$selp}</option>
        
HTML;

    }
    echo <<< HTML
        </select>
        <label>Item</label>
      </span>
HTML;
  }  
  echo <<< HTML
    <div>
      <label></label>
      <span class="name">
        <button name ="data" type ="button" onclick="valprice('items')">Validate value</button>
      </span>
    </div>
    
HTML;
}

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.