how to insert multiple select option in each row of mysql

[ismael ahm@d]

i have three db tables 

  products
  product_id- Name 
  1 | hairoil
  2 | lotion
  3 | cream

  Countries
  country_id- Name
  1 | UAE
  2 | France
  3 | UK

  Now i wanna that when select multiple countries for one product from Html form, then in third db table should insert data like this<br />

<p>countries_product
  c_id | coutry_id | product_id
  1 | 1 | 2
  2 | 3 | 2
  3 | 1 | 3
  4 | 2 | 3
  5 | 3 | 3

  Please let me know from your valuable suggestions<br />
  Thanks

[broj1]

// if values exist in $_POST (or $_GET if you use it)
if(isset($_POST['product_id']) && isset($_POST['country_id'])) {

    // product ID
    $product_id = $_POST['product_id'];

    // begin the query
    $query = 'INSERT INTO countries_product (coutry_id, product_id) VALUES ';

    // add coutry ID's
    foreach($_POST['country_id'] as $country_id) {

        $query = "($country_id, $product_id),";
    }

    // remove trailing comma from the query
    $query = rtrim($query, ',')

    // execute the query
    ...
}

Add a bit of security checks to this. If IDs are integers it is good to cast them to integers to avoid SQL injection attacks.

$product_id = (int) $_POST['product_id'];

or use is_numeric checks

if(isset($_POST['product_id']) && is_numeric($_POST['product_id']) && isset($_POST['country_id']) && is_numeric($_POST['country_id']) ) {
    ...
}

[ismael ahm@d]

So Much Thanks for your quick reply:
i have compile above code but third db table no updating while my html form:

<form name="pro_reg_form" id="form1" method="post" action="">
  <table width="40%"  border="1" align="center" cellpadding="0" cellspacing="0">
    <tr align="center">
      <td colspan="2">Product Regisration Tracker</td>
    </tr>
    <tr>
      <td>Product Name </td>
      <td><input name="product_name" type="text" id="product_name" /></td>
    </tr>
    <tr>
      <td>Product Regsiterd Date </td>
           <td><input name="regstr_date" type="text" id="regstr_date" /></td>
    </tr>
    <tr>
      <td>Product Renewal Date </td>
      <td><input name="renewal_date" type="text" id="renewal_date" /></td>
    </tr>
    <tr>
      <td>Product Shelf Life </td>
      <td><input name="pro_shelf_life" type="text" id="pro_shelf_life" /></td>
    </tr>
    <tr>
      <td valign="top">Country</td>
       <?php $stqry = "select * from country order by country_name"; 
                        $cat_rs = mysql_query($stqry); ?>

      <td><select name="country[]" multiple="multiple" id="country">
      <?php 
                     // while($obcat=@mysql_fetch_object($rscat))
                        while ($cat_row = @mysql_fetch_object($cat_rs)) {?>
        <option value= '1' <?php echo $cat_row->country_id; ?> > <?php echo $cat_row->country_name; ?> </option>
       <!-- <option>UAE</option>
        <option>KSA</option>
        <option>Kwait</option>
        <option>Pakistan</option>
        <option>India</option> -->
         <?php } ?>
      </select> 
      </td>
    </tr>
    <tr>
      <td colspan="2">&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td><input type="submit" name="Submit" value="Register" /></td>
    </tr>
  </table>
</form>

I dont know where i am missing something else !!! :(

[simplypixie]

This is wrong:

        <option value= '1' <?php echo $cat_row->country_id; ?> >

It should be:

        <option value= "<?php echo $cat_row->country_id; ?>" >

[broj1]

A problem could lie in the select element in the code below:

while ($cat_row = @mysql_fetch_object($cat_rs)) {?>
<option value= '1' <?php echo $cat_row->country_id; ?> > <?php echo $cat_row->country_name; ?> </option>
<!-- <option>UAE</option>
<option>KSA</option>

where statement <option value= '1' should be only <option value= (omitting '1' which is read from $cat_row->country_id). Please check the generated HTML source in a browser (right button -> view source, or something similar). If there are errors in HTML, they would be hihlighted (at least in Firefox).

Sometimes it is easier if html and php is not mixed too much. I would code it this way (so code for options gets echoed in one statement and you can easily spot errors):

while ($cat_row = @mysql_fetch_object($cat_rs)) {
    echo "<option value='$cat_row->country_id'>$cat_row->country_name</option>";
}

If HTML code is allright the error could be in the processing part of your script which is not in the posted code above. it would be good idea to test for the values of $_POST and the query before it is submitted.

[ismael ahm@d]

Kind Regards for rectifications !!!

Now as you know that i am fetching multiple countries from countries db table as mentioned above, my question is that how i insert / update countries_prodcut db table, when i am going to compile "broj1" code, the countires_products db table not updating accordingly.

Please advise ...!!!

[ismael ahm@d]

Kind Regards for rectificaiton,

Now as you know that i am fetching country from countries db table, my question is that how i insert/update the countries_product db table, because when i am trying to compile "broj1" code, it not showing any error but countires_product table not updating accordingly.

Please advice ... !!!

[broj1]

Put this in the beginning of your script:

<?php
// if values exist in $_POST, insert them in the database
if(isset($_POST['product_id']) && isset($_POST['country_id'])) {

    // uncomment this to inspect the $_POST array
    // it should contain product_id and an array of country_ids
    // die(print_r($_POST, 1))

    // product ID
    $product_id = $_POST['product_id'];
    // begin the query
    $query = 'INSERT INTO countries_product (coutry_id, product_id) VALUES ';
    // add coutry ID's
    foreach($_POST['country_id'] as $country_id) {
        $query = "($country_id, $product_id),";
    }
    // remove trailing comma from the query
    $query = rtrim($query, ',');

    // uncomment this to debug the query
    // you can copy the displayed query in phpmyadmin
    // die($query);

    // execute the query (I presume the db connection is already established)
    mysql_query($query);
}
?>

This is the processing part. It will only when the form has been submitted and will run the update query. You have two die commands in the code. Uncomment the first one to see whether the $_POST array contains valid values. Uncomment the second one to display the generated qury to see if it is OK. You can post the output of both commands here if you have more troubles.

[ismael ahm@d]

broj !!! i know, i am not a hard programmer, but it is my great pleasure that u returns with different solutions , the above die() compiles successfully but the product_countries table not updating.....again same problem, please review the below...

<?php

if (isset($_POST['Submit'])) {

$pro_name = $_POST['product_name'];
$pro_reg_date = $_POST['regstr_date'];
$renewal_date = $_POST['renewal_date'];
$product_shelf = $_POST['pro_shelf_life'];
$countries = $_POST['country'];



$Id=(@mysql_result(@mysql_query("SELECT max(product_id) from products"),0,0)+1);
$c_Id=(@mysql_result(@mysql_query("SELECT max(country_id) from country"),0,0)+1);
$c_w_id = (@mysql_result(@mysql_query("SELECT max(c_w_p_id) from country_wise_products"),0,0)+1);

$sql1 = "INSERT INTO products (product_id, product_name, pro_regt_date, pro_renew_date, product_shelf_life) VALUES 
('$Id', '$pro_name', '$pro_reg_date', '$renewal_date', '$product_shelf')";
$sql1_result = mysql_query($sql1) or die (mysql_error());
/********************************************************************************************/
   // if values exist in $_POST, insert them in the database
if(isset($_POST['product_id']) && isset($_POST['country_id'])) {
// uncomment this to inspect the $_POST array
// it should contain product_id and an array of country_ids
 die(print_r($_POST, 1));
// product ID

$product_id = $_POST['product_id'];
// begin the query
$query = 'INSERT INTO country_wise_products (coutry_id, product_id) VALUES ';
// add coutry ID's
foreach($_POST['country_id'] as $country_id) {
$query = "($country_id, $product_id),";
}
// remove trailing comma from the query
$query = rtrim($query, ',');
// uncomment this to debug the query
// you can copy the displayed query in phpmyadmin
 die($query);
// execute the query (I presume the db connection is already established)
mysql_query($query);


}    

/******************************************************************************************/

}
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
</head>

<body>
<form name="pro_reg_form" id="form1" method="post" action="">
  <table width="40%"  border="1" align="center" cellpadding="0" cellspacing="0">
    <tr align="center">
      <td colspan="2">Product Regisration Tracker</td>
    </tr>
    <tr>
      <td>Product Name </td>
      <td><input name="product_name" type="text" id="product_name" /></td>
    </tr>
    <tr>
      <td>Product Regsiterd Date </td>
           <td><input name="regstr_date" type="text" id="regstr_date" /></td>
    </tr>
    <tr>
      <td>Product Renewal Date </td>
      <td><input name="renewal_date" type="text" id="renewal_date" /></td>
    </tr>
    <tr>
      <td>Product Shelf Life </td>
      <td><input name="pro_shelf_life" type="text" id="pro_shelf_life" /></td>
    </tr>
    <tr>
      <td valign="top">Country</td>
       <?php $stqry = "select * from country order by country_name"; 
                        $cat_rs = mysql_query($stqry); ?>

      <td><select name="country[]" multiple="multiple" id="country">
      <?php 
                     // while($obcat=@mysql_fetch_object($rscat))
                            while ($cat_row = @mysql_fetch_object($cat_rs)) {
    echo "<option value='$cat_row->country_id'>$cat_row->country_name</option>";
    }?>
       <!-- <option>UAE</option>
        <option>KSA</option>
        <option>Kwait</option>
        <option>Pakistan</option>
        <option>India</option> -->
         <?php //} ?>
      </select> 
      </td>
    </tr>
    <tr>
      <td colspan="2">&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td><input type="submit" name="Submit" value="Register" /></td>
    </tr>
  </table>
</form>
</body>
</html>

Thanks again :)

[broj1]

Please post here what is the output of the first die() statement.

[ismael ahm@d]

Dear, die function does not return any thing, even i didn't get any error.

Products tables updated, but countries_product table not updating as usual.

Kind Regards

[broj1]

In the last code you posted there are lines that you never posted before. Now seeing this my code dees not fit into your code since it is duplicating the functionality. If this is your last version then I will have a look at it tonight when I have some time (other post are welcome).

[Bachov Varghese]

Check this

<?php

if (isset($_POST['Submit'])) {

$pro_name = $_POST['product_name'];
$pro_reg_date = $_POST['regstr_date'];
$renewal_date = $_POST['renewal_date'];
$product_shelf = $_POST['pro_shelf_life'];
$countries = $_POST['country'];



$Id=(@mysql_result(@mysql_query("SELECT max(product_id) from products"),0,0)+1);
$c_Id=(@mysql_result(@mysql_query("SELECT max(country_id) from country"),0,0)+1);
$c_w_id = (@mysql_result(@mysql_query("SELECT max(c_w_p_id) from country_wise_products"),0,0)+1);

$sql1 = "INSERT INTO products (product_id, product_name, pro_regt_date, pro_renew_date, product_shelf_life) VALUES 
('$Id', '$pro_name', '$pro_reg_date', '$renewal_date', '$product_shelf')";
$sql1_result = mysql_query($sql1) or die (mysql_error());

if(isset($_POST['country'])) {
$cLists = array();
$cLists = $_POST['country'];
$product_id = $Id;

foreach($cLists as $country_id) {
$query = "INSERT INTO country_wise_products (coutry_id, product_id) VALUES  ($country_id, $product_id)";
mysql_query($query);
}
}

}
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
</head>

<body>
<form name="pro_reg_form" id="form1" method="post" action="">
  <table width="40%"  border="1" align="center" cellpadding="0" cellspacing="0">
    <tr align="center">
      <td colspan="2">Product Regisration Tracker</td>
    </tr>
    <tr>
      <td>Product Name </td>
      <td><input name="product_name" type="text" id="product_name" /></td>
    </tr>
    <tr>
      <td>Product Regsiterd Date </td>
           <td><input name="regstr_date" type="text" id="regstr_date" /></td>
    </tr>
    <tr>
      <td>Product Renewal Date </td>
      <td><input name="renewal_date" type="text" id="renewal_date" /></td>
    </tr>
    <tr>
      <td>Product Shelf Life </td>
      <td><input name="pro_shelf_life" type="text" id="pro_shelf_life" /></td>
    </tr>
    <tr>
      <td valign="top">Country</td>
       <?php $stqry = "select * from country order by country_name"; 
                        $cat_rs = mysql_query($stqry); ?>

      <td><select name="country[]" multiple="multiple" id="country">
      <?php 
                            while ($cat_row = @mysql_fetch_object($cat_rs)) {
    echo "<option value='".$cat_row->country_id."'>".$cat_row->country_name."</option>";
    }?>
      </select> 
      </td>
    </tr>
    <tr>
      <td colspan="2">&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td><input type="submit" name="Submit" value="Register" /></td>
    </tr>
  </table>
</form>
</body>
</html>

[adam.adamski.96155]

Line 30:

$query = 'INSERT INTO country_wise_products (coutry_id, product_id) VALUES ';

should be country_id (the 'n' is missing from country). But why no error to screen (unknown column)? Is there a file error_log in the folder you are running the scripts?

[ismael ahm@d]

Okay, broj... will waiting for ur solution!!!

Bachov and Adam, bundle of thanks for reply,

Bachov, i have indclude die code as below mentioned

foreach($cLists as $country_id) {
$query = "INSERT INTO country_wise_products (country_id, product_id) VALUES ($country_id, $product_id)";
mysql_query($query) or die ("country_wise_product not updated!!! ");

Now after compiling i got die error .... 

Please advise

[Bachov Varghese]

Make sure that , field names country_id and product_id are same as country_wise_products table fields..

[ismael ahm@d]

if i m not doing wrong ... below my country_wise_prodcuts table fields which will get data from other two tables (mentioned above).

c_w_p_id | country_id | product_id

[adam.adamski.96155]

Have a look at the error mysql reports:

if (!$res = mysql_query($query)){
    echo mysql_error(); die();
    }

It should give you the specific problem.

[ismael ahm@d]

adam, cheers for reply!!!

after compiling the above statement ... return

Duplicate entry '0' for key 1

[ismael ahm@d]

last cheeers done job...!!!

Great Adam and Bachov......Problem Solved

Kind Regards