how to insert multiple select option in each row of mysql

Question

ismael ahm@d 0 Newbie Poster

12 Years Ago

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&lt;br /&gt;

<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

mysql php sql

5 Contributors
25 Replies
9K Views
1 Day Discussion Span
Latest Post 12 Years Ago Latest Post by ismael ahm@d

broj1 356 Humble servant

12 Years Ago

// 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']) ) {
    ...
}

Edited 12 Years Ago by broj1

simplypixie 123 Posting Pro in Training

12 Years Ago

This is wrong:

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

It should be:

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

broj1 356 Humble servant

12 Years Ago

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.

broj1 356 Humble servant

12 Years Ago

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.

adam.adamski.96155 43 Junior Poster

12 Years Ago

Have a look at the error mysql reports:

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

It should give you the specific problem.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

ismael ahm@d 0 Newbie Poster · Answer 1 · 2012-10-08T15:28:15+00:00

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 !!! :(

ismael ahm@d 0 Newbie Poster · Answer 2 · 2012-10-08T18:35:48+00:00

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 0 Newbie Poster · Answer 3 · 2012-10-08T18:43:28+00:00

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 ... !!!

ismael ahm@d 0 Newbie Poster · Answer 4 · 2012-10-08T20:30:35+00:00

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 356 Humble servant Featured Poster · Answer 5 · 2012-10-09T07:44:10+00:00

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

ismael ahm@d 0 Newbie Poster · Answer 6 · 2012-10-09T09:11:19+00:00

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 356 Humble servant Featured Poster · Answer 7 · 2012-10-09T09:22:31+00:00

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).

Bachu 45 Newbie Poster · Answer 8 · 2012-10-09T09:40:49+00:00

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 43 Junior Poster · Answer 9 · 2012-10-09T09:44:55+00:00

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 0 Newbie Poster · Answer 10 · 2012-10-09T10:05:04+00:00

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

Bachu 45 Newbie Poster · Answer 11 · 2012-10-09T10:20:09+00:00

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

ismael ahm@d 0 Newbie Poster · Answer 12 · 2012-10-09T10:28:11+00:00

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

ismael ahm@d 0 Newbie Poster · Answer 13 · 2012-10-09T11:12:07+00:00

adam, cheers for reply!!!

after compiling the above statement ... return

Duplicate entry '0' for key 1

ismael ahm@d 0 Newbie Poster · Answer 14 · 2012-10-09T11:17:51+00:00

last cheeers done job...!!!

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

Kind Regards

adam.adamski.96155 43 Junior Poster · Answer 15 · 2012-10-09T11:36:06+00:00

Hey, glad you solved your problem, kudos to broj1 also, don't forget to mark thread as solved :D

ismael ahm@d 0 Newbie Poster · Answer 16 · 2012-10-09T14:57:22+00:00

One request to you again,

If i add country_name in countries_wise_products table, so how i will update countries_wise_products table with both coutnry_id and country_name fields.

Thank you!!!

adam.adamski.96155 43 Junior Poster · Answer 17 · 2012-10-09T16:44:00+00:00

adam.adamski.96155 43 Junior Poster

12 Years Ago

Can you post your code up till now?

ismael ahm@d 0 Newbie Poster · Answer 18 · 2012-10-09T22:53:18+00:00

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);


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

if(isset($_POST['country'])) {
//$cListS = array();
$cLists = $_POST['country'];

$product_id = $Id;

foreach($cLists as $countryid) {

$query = "INSERT INTO country_wise_products (country_id, product_id, product_name) VALUES ('$countryid', '$product_id', '$pro_name')";
//mysql_query($query) or die ("country_wise_product not updated!!! ");
    if (!$res = mysql_query($query)){
    echo mysql_error(); die();
    }
}

}

/******************************************************************************************/
$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());


}


?>

<!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>

adam.adamski.96155 43 Junior Poster · Answer 19 · 2012-10-10T09:02:11+00:00

foreach($cLists as $countryid) {
    $innerSQL = "SELECT country_name FROM country WHERE country_id=$countryid"; //get country name for each new entry.
    $query = "INSERT INTO country_wise_products (country_id, product_id, product_name, country_name) VALUES ('$countryid', '$product_id', '$pro_name', ($innerSQL))";

It doesn't make sense to me to have the country name in that table, as you already have a look-up table for countries and ID's, but if you want to do it, I think the above code is what you need. Backup your database before you let my code near your server and this will not work until you have added a new column to your DB profiled for text entry.

ismael ahm@d 0 Newbie Poster · Answer 20 · 2012-10-10T10:21:32+00:00

ismael ahm@d 0 Newbie Poster

12 Years Ago

Okay

Thanks and Regards