How to populate one table with values from another table

[hance2105]

i have a table with the following columns

 

pord_name, prod_brand, prod_photo, prod_desc

 

and another table with the following columns

 

prod_name, prod_brand, prod_photo, prod_desc, cat, subcat, prod_price, prod_w_c

 

i have a for add_prod.php to add products

 

the prod name is displayed in a select which when product name is selected, populates the prod brand select. on selecting an option in the prod brand, the prod desc is displayed

 

when i click on the add button, prod_name, prod_brand, etc are added except the prod_desc and prod_photo

 

can i know what is wrong with the code here please?

 

<?php include('db_connect.php'); session_start(); $username = $_SESSION['username']; $sql=mysql_query("select user_id from tbllogin where username = '$username'"); $prod_name=$_POST['prod_name'];$prod_brand=$_POST['prod_brand'];$prod_price=$_POST['prod_price'];$cat=$_POST['cat'];$subcat=$_POST['subcat'];$prod_w_c=$_POST['prod_w_c'];$url='add_prod.php'; $row=mysql_fetch_array($sql);  $sql=mysql_query("INSERT INTO tbl_product(user_id, prod_name, prod_brand, prod_desc, prod_price, cat, subcat, prod_w_c) VALUES('$row[user_id]', '$prod_name', '$prod_brand', '$prod_desc', '$prod_price', '$cat', '$subcat', '$prod_w_c') SELECT FROM tbl_prodstd (prod_photo, prod_desc) WHERE prod_name='$prod_name'"); echo '<META HTTP-EQUIV=Refresh CONTENT="0; URL='.$url.'">';echo "<script>alert('This product has been added successfully.')</script>"; ?> 

[ginerjm]

You need to check if that long query runs ok. I suspect it is wrong. You are combining two separate un-related queries into one execution. After your MySQL_query(); add this:

 

if (!$sql)

{

echo "Query did not run - message is: ".MySQL_error()";

exit();

}

[hance2105]

what is the correct way to write the mysql query?

 

how can it be re-written please?

[Csharp]

You need to run one query at once. First the INSERT statement and later the SELECT one.

[hance2105]

you mean it should be like this

 

$sql = mysql_query("INSERT INTO tbl_product(user_id, prod_name, prod_brand, prod_price, cat, subcat, prod_w_c) VALUES ('$row['user_id']', '$prod_name', '$prod_brand', '$prod_price', '$cat', '$subcat', '$prod_w_c')");

 

$sql = mysql_query("INSERT INTO tbl_product ("SELECT prod_desc, prod_photo from tbl_prodstd")");

[ginerjm]

Do you have multiple  prod_desc and prod_photo values for the ONE record you are Inserting?  Or are there multiples?  If it's just one, then I would do the select query for prod_desc & prod_photo for the prod_name you are inserting and then use the result to include in the INSERT query.

 

Of course in a normalized database you shouldn't be putting those values into the other table as long as you have a foreign key in 'tbl_product' that can always connect you to the photo and desc in 'tbl_prodstd'.

[hance2105]

hmmm ok i can understand...i thought of smthng

 

i am selecting prod_name which displays prod_brand which in turn displays the prod_desc. on clicking on the add button, instead of inserting prod_name, prod_brand, prod_desc and prod_photo, how can i insert only the std_id that is the primary key in the table tbl_prodstd to the table tbl_products?

 

this way i will have only the id (std_id) that is present in the tbl_prodstd added to the tbl_products.

 

when doing queries to display the details, i can query from both tables to display the data in a table view.

 

am i thinking right? if yes, i would like much help in doing that please....

 

if the info is not clear, i can detail it more....

[ginerjm]

Now you got it!  No reason to duplicate data across multiple tables.  Yes - your future queries will 'gather' the data items(fields) from multiple tables to give the user the 'view' they need.

 

So basically - the user selects from a page that has a 'view' of product info that you produced.  When he selects one, you save the product id in a table under his id and the date and whatever else you need to remember the transaction.  When you later want to see what the user selected/purchased you do a query looking for the records with his id and the transaction date and also grab all the product records for each product id you  find.  There will be multiple records with userid and prod ids and dates as well as a record for each prod id which you will gather and produce a combined result record.

 

userid1 prodid1 date1 prodname proddesc prodphotoname......

userid1 prodid2 date1 prodname proddesc prodphotoname......

userid1 prodid2 date2 prodname proddesc prodphotoname......

userid1 prodid3 date1 prodname proddesc prodphotoname......

userid1 prodid4 date1 prodname proddesc prodphotoname......

userid1 prodid3 date4 prodname proddesc prodphotoname......

 

[hance2105]

well here is my whole code

 

addprod.php is displayed above

 

first the add_prod.php code

 

 

<?php
session_start();
include('db_connect.php');
include('func.php');
?>
 
<!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>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Add product</title>
 
<link href="CSS/add_prod.css" rel="stylesheet" type="text/css" />
 
<script src="SpryAssets/SpryValidationTextField.js" type="text/javascript"></script>
<script src="SpryAssets/SpryValidationSelect.js" type="text/javascript"></script>
<link href="SpryAssets/SpryValidationTextField.css" rel="stylesheet" type="text/css" />
 
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3/jquery.min.js"></script>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>
<script src="SpryAssets/SpryValidationTextarea.js" type="text/javascript"></script>
 
<script type="text/javascript">
$(document).ready(function() {
$('#wait_1').hide();
$('#prod_name').change(function(){
 $('#wait_1').show();
 $('#result_1').hide();
      $.get("func.php", {
func: "prod_name",
drop_var: $('#prod_name').val()
      }, function(response){
        $('#result_1').fadeOut();
        setTimeout("finishAjax('result_1', '"+escape(response)+"')", 400);
      });
     return false;
});
});
 
function finishAjax(id, response) {
  $('#wait_1').hide();
  $('#'+id).html(unescape(response));
  $('#'+id).fadeIn();
}
function finishAjax_tier_three(id, response) {
  $('#wait_2').hide();
  $('#'+id).html(unescape(response));
  $('#'+id).fadeIn();
}
</script>
 
<script type="text/javascript">
$(document).ready(function()
{
$(".cat").change(function()
{
var id=$(this).val();
var dataString = 'id='+ id;
 
$.ajax
({
type: "POST",
url: "ajax.php",
data: dataString,
cache: false,
success: function(html)
{
$(".subcat").html(html);
} 
});
 
});
 
});
</script>
 
<link href="SpryAssets/SpryValidationTextarea.css" rel="stylesheet" type="text/css" />
<link href="SpryAssets/SpryValidationSelect.css" rel="stylesheet" type="text/css" />
</head>
 
<body>
 
<div id="stylized" class="myform">
 
<form id="form" name="add_prod" method="post" action="addprod.php" enctype="multipart/form-data">
 
<h2 align="center"><b>- Add Product -</b></h2>
 
<h3 align="right"><a href="retailer_home.php"><img src="Images/Main Menu/home_icon.png" width="50" height="50" /></a></h3>
 
<table width="1000" border="0">
    <tr>
    <td><p align="right">Product Name</p></td>
    <td><!--<span id="sprytextfield2">
      <input type="text" name="prod_name" id="prod_name" />
      <span class="textfieldRequiredMsg">A value is required.</span></span>-->
      <select name="prod_name" id="prod_name" class="prod_name">
      <option value="" selected="selected" disabled="disabled">Select Product Name</option>
      <?php
      getprod_name();
 ?>
      </select>
    </tr>
  <tr>
    <td><p align="right">Product Brand</p></td>
    <td><!--<span id="sprytextfield3">
      <input type="text" name="prod_brand" id="prod_brand" />
      <span class="textfieldRequiredMsg">A value is required.</span></span>
      <select name="prod_brand" class="prod_brand">
      </select>-->
      <span id="wait_1" style="display: none;">
      <img alt="Please Wait" src="Images/ajax-loader.gif"/>
      </span>
      <span id="result_1" style="display: none;"></span>
     
      </td>
  </tr>
  <tr>
    <td><p align="right">Product Description</p></td>
    <td><!--<span id="sprytextarea1">
    <textarea name="prod_desc" id="prod_desc" cols="45" rows="5"></textarea>
    <span class="textareaRequiredMsg">A value is required.</span><span class="textareaMaxCharsMsg">Exceeded maximum number of characters.</span></span>-->
    <span id="wait_2" style="display: none;">
    <img alt="Please Wait" src="Images/ajax-loader.gif"/>
    </span>
    <span id="result_2" style="display: none;"></span>
    </td>
  </tr>
  <tr>
    <td><p align="right">Product Price (MRU)</p></td>
    <td><span id="sprytextfield4">
    <input type="text" name="prod_price" id="prod_price" />
    <span class="textfieldRequiredMsg">A value is required.</span><span class="textfieldInvalidFormatMsg">Invalid format.</span></span></td>
  </tr>
  <tr>
    <td><p align="right">Product Category</p></td>
    <td><span id="spryselect1">
      <select name="cat" class="cat">
        <option selected="selected">--Select Category--</option>
        <?php
include('db_connect.php');
$sql=mysql_query("select id,cat_name from tblprod_cat ORDER BY id ASC");
while($row=mysql_fetch_array($sql))
{
$id=$row['id'];
$data=$row['cat_name'];
echo '<option value="'.$id.'">'.$data.'</option>';
} 
?>
      </select>
      <span class="selectRequiredMsg">Please select an item.</span></span></tr>
  <tr>
    <td><p align="right">Product Subcategory</p></td>
    <td><span id="spryselect2">
      <select name="subcat" class="subcat">
        <option>--Select Subcategory--</option>
      </select>
      <span class="selectRequiredMsg">Please select an item.</span></span></td>
  </tr>
  <tr>
    <td><p align="right">Product Weight/Capacity</p></td>
    <td><span id="sprytextfield5">
      <input type="text" name="prod_w_c" id="prod_w_c" />
      <span class="textfieldRequiredMsg">A value is required.</span></span></td>
  </tr>
 <!-- <tr>
    <td><p align="right">Please choose a file:</p></td>
    <td><input name="uploaded" type="file" /></td>
  </tr>-->
</table>
<p align="center">
  <input type="submit" class="button" name="button" id="button" value="<-- Add product -->"/>
</p>
 
</form>
 
</div>
<script type="text/javascript">
var sprytextfield4 = new Spry.Widget.ValidationTextField("sprytextfield4", "currency", {validateOn:["blur"]});
var sprytextfield5 = new Spry.Widget.ValidationTextField("sprytextfield5", "none", {validateOn:["blur"]});
var spryselect1 = new Spry.Widget.ValidationSelect("spryselect1", {validateOn:["blur"]});
var spryselect2 = new Spry.Widget.ValidationSelect("spryselect2", {validateOn:["blur"]});
</script>
</body>
</html>

 

this one is the func.php to display the prod_name, prod_brand and prod_desc

 

 

<?php
//**************************************
//     Page load dropdown results     //
//**************************************
function getprod_name()
{
$result = mysql_query("SELECT DISTINCT prod_name FROM tbl_prodstd") 
or die(mysql_error());
 
 while($tier = mysql_fetch_array( $result )) 
  
{
  echo '<option value="'.$tier['prod_name'].'">'.$tier['prod_name'].'</option>';
}
 
}
 
//**************************************
//     First selection results     //
//**************************************
if(isset($_GET['func']) && $_GET['func'] == "prod_name") {  
   prod_name($_GET['drop_var']); 
}
 
function prod_name($drop_var)
{  
    include_once('db_connect.php');
$result = mysql_query("SELECT * FROM tbl_prodstd WHERE prod_name='$drop_var'") 
or die(mysql_error());
 
echo '<select name="prod_brand" id="prod_brand">
     <option value=" " disabled="disabled" selected="selected">Choose one</option>';
 
  while($drop_2 = mysql_fetch_array( $result )) 
{
 echo '<option value="'.$drop_2['prod_brand'].'">'.$drop_2['prod_brand'].'</option>';
}
 
echo '</select> ';
    //echo '<input type="submit" name="submit" value="Submit" />';
echo "<script type=\"text/javascript\">
$('#wait_2').hide();
$('#prod_brand').change(function(){
 $('#wait_2').show();
 $('#result_2').hide();
      $.get(\"func.php\", {
func: \"prod_brand\",
drop_var: $('#prod_brand').val()
      }, function(response){
        $('#result_2').fadeOut();
        setTimeout(\"finishAjax_tier_three('result_2', '\"+escape(response)+\"')\", 400);
      });
     return false;
});
</script>";
}
 
//**************************************
//     Second selection results     //
//**************************************
if(isset($_GET['func']) && $_GET['func'] == "prod_brand") {
   drop_2($_GET['drop_var']); 
}
 
function drop_2($drop_var)
{  
    include_once('db_connect.php');
$result = mysql_query("SELECT * FROM tbl_prodstd WHERE prod_brand='$drop_var'") 
or die(mysql_error());
 
/*echo '<select name="prod_desc" id="prod_desc">
     <option value=" " disabled="disabled" selected="selected">Choose one</option>';*/
 
  while($drop_3 = mysql_fetch_array( $result )) 
{
 /*echo '<option value="'.$drop_3['prod_desc'].'">'.$drop_3['prod_desc'].'</option>';*/
 echo "<textarea>".$drop_3['prod_desc']."</textarea>";
}
 
/*echo '</select> ';*/
    
}
 
?>

 

my friend, my question now is how i add the std_id?

 

and thanks for the help you are providing me

[ginerjm]

Sorry - but you will have to explain to me what you want to do.  Are you asking how to add a value to a table, or add a new field to a table?  What is std_id - an input value?  Is it the key to a record on another table?  I'm confused.  Remember this is not my appl so I'm not keeping track of all your details.

[hance2105]

well the std_id is an auto increment integer in the tbl_prodstd

 

when i add products to that table, prod_name, prod_brand, prod_desc and prod_photo are populated

 

each st_id can have the same product name but different product brand

 

each brand have their own product description and photo

 

i want to know how to add the std_id to the tbl_product so that i can do queries later

 

if possible when the prod name itself is selected the std_id is selected as well so that when i click on add product button in add_prod.php the std id is sent to tbl_product along with cat, subcat, prod_price, prod_w_c

[ginerjm]

You initially said:

 

i have a table with the following columns

 

pord_name, prod_brand, prod_photo, prod_desc

 

and another table with the following columns

 

prod_name, prod_brand, prod_photo, prod_desc, cat, subcat, prod_price, prod_w_c

 

So where is 'std_id'?  Are you asking about creating a new column?  Well - with all that duplication of prod_ columns, this db is not fully normalized.  You need to figure that out first before trying to assign std_id.  Is std_id one-to-one with anything above?

[hance2105]

sorry i forgot to add the std_id when i wrote that...am not creating a new column the column is present in both tables

 

auto increment in tbl_prodstd

 

has to be inserted in tbl_product when products are added

 

lets set it like this

 

in tbl_prodstd

 

std id - prod_name - prod_brand - prod_desc - prod_photo

1 - prod_name1 - prod_brand1 - prod_desc - prod_photo

2- prod_name1 - prod_brand2 - prod_desc - prod_photo

3 - prod_name2 - prod_brand(for prod_name2) - prod_desc - prod_photo

 

in this table, i store products with their brands, descriptions and photos.

 

the prod_name can be the same but prod_brand, description and photo different

 

in tbl_product

 

i willl store

 

prod_id - auto increment

user_id - from table login

std_id - from tbl_prodstd

cat - category

subcat - subcategory

prod_price

 

can this help you understand?

[ginerjm]

I dont' know what prod_id buys you in the tbl_product table

 

std_id is the key to all of your products, whether they are the same (with different vendors) or different.  So that table is pretty much good right now.

 

Your table tbl_product is confusing.  What does the user have to do with the product-cat-subcat & price data?  Also - are cat/subcat/price something that pertains to the buyer (/user?) or to the product?  If the product, then I suggest that cat/subcat be stored in the other table.

[hance2105]

retailers add products and set their own prices that's why i created the tbl_product

 

so my tbl_prodstd table will containt std_id, prod_name, prod_brand, prod_desc, prod_photo, cat and subcat right?

 

my tbl_product will contain id (prod_id out), std_id, price that's all?

 

hw the tables should be according to u?

[hance2105]

ok just guide me on this one my friend...forget abt wat was above

 

i create a table product (tbl_product) with fields

 

prod_id, prod_name, prod_brand, prod_desc, prod_photo, cat, subcat

 

i create another table - tbl_retprod where retailers can set their own prices. each retailer will have their user ids beside each product they add and set their prices

 

id (am not sure i need this), user_id, prod_id, normal_price - later i will add another column called promo_price

 

retailers after logging in can see all the products in the list displayed in a table with a column price where they can set their prices

 

beside the price will be an add button

 

tbl_retprod will be populated then

 

the retailer can query the products added and same is displayed in a table with delete and update buttons

 

when the delete button is clicked the product is disabled (not deleted - think will have to flag it and create a column delete in the tbl_retprod, 0 = not disabled and 1 = disabled)

 

when the update button is clicked the retailer should be able to update the product price

 

what do you think of this one? 

 

if it's good can i get some help in completing it please?

[ginerjm]

Tell me who you deal with.  Are you creating a catalog of products from suppliers and then gathering data on who buys them from you?

[hance2105]

well i am creating a price comparison website. it's my project. retailers set their owns prices on the products. customers can compare the prices to see which retailer is offering the best prices. customers can add some products in their shopping list. they can add 4 retailers as their favourites. based on the products and retailers set as favourites, prices for each product by retailer is displayed with a total product cost at the bottom of each retailer.

[ginerjm]

Product table: I would think that the retailers are responsible for ALL of the product details - their retailer_id, name, desc, photo, brand AND price. This table would also have a unique id that you assign (std_id?) to each and every product record. Think about a status code to mark products as active or not, cause you never want to delete any.

 

Order_table: Your customers would create records here by ordering something. These records would have their customer id (assigned by you when they sign up), the order date, the std_id of the product they want and the price at the time of the order (from the above table).

 

Customer_table: You then have a table to track customer data such as customer_id (assigned by you), name, address, etc.

 

Vendor_table: a table to hold the retailer's info such retailer_id (assigned by you), name, address and such. The retailers can modify all this data except for the retailer_id.

 

This would be a proper normalized structure where nothing is duplicated except for the keys that tie the tables together.

 

Hope this clarifies things and helps you to re-organize your structures to make your updating easier.

Edited June 28, 2013 by ginerjm