0
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
5
Contributors
25
Replies
35
Views
4 Years
Discussion Span
Last Post by ismael ahm@d
Featured Replies
  • 2

    // 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 … Read More

  • Have a look at the error mysql reports: if (!$res = mysql_query($query)){ echo mysql_error(); die(); } It should give you the specific problem. Read More

2
// 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 by broj1

0

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

0

This is wrong:

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

It should be:

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

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.

0

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

0

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

0

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.

0

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

0

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

0

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

0

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>

Edited by Bachu

0

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?

0

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
0

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

0

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

Edited by ismael ahm@d: spell mistake

1

Have a look at the error mysql reports:

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

It should give you the specific problem.

0

adam, cheers for reply!!!

after compiling the above statement ... return

Duplicate entry '0' for key 1

0

last cheeers done job...!!!

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

Kind Regards

Edited by ismael ahm@d: mistake

0

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

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

Edited by adam.adamski.96155

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.