two tables being used to display results

horstuff

Hi. I have two tables (warranty and product_status). Table warranty has three fields - aw_id, warranty_comment, and aw_tag. Table product_status also has three fields - product_id, product-name, and product_price.

This is part of an admin panel for our main office to use, so the boss can enter products into the database. He must manually input a product_id, because it is tied to our cart. The product_id field is NOT auto integer. In table warranty, the field aw_id IS auto integer. Currently I am not using aw_id, I just set it up because I know that I will need it to do what I am trying to do.

The way this will work is that the user (boss) goes in to the admin to add a product. He first types in a product id, then a name, sku, etc. Then he selects from a dropdown list that is made up of data from one field in table warranty (warranty_comment, which is just a short description of the warranty. The field aw_tag holds detailed information about that warranty). So now the all the product info is entered into table product_status. I need to somehow get the data from table warranty into table product_status as aw_id (the auto integer field in warranty) so if the data in table warranty changes, it changes all the records from product_status which use it. In other words, I think I am trying to set up some sort of query that pulls data from two tables. This is confusing, and I'm sure I'm not explaining it very well. Thanks in advance for any help.


$post1 = mysql_query ("SELECT warranty_comment,aw_tag FROM warranty ORDER BY warranty_comment ASC"); 

while ($row=mysql_fetch_array($post1) ) {
$fname1 = $row["warranty_comment"];
$trimmed = trim($fname1);
        $option_value1 .= "<OPTION value=\"$fname1\">$fname1</option>";

}

         print "<table width='760' border='1' cellpadding='3' cellspacing='0'>\n";
         print "<tr bgcolor='#000066'><td width='110'><font face='Verdana' size='1' color='#FFFFFF'>ID</font></td><td width='650'><input type=text name=pid size='20'></td></tr>";
         print "<tr bgcolor='#000066'><td width='110'><font face='Verdana' size='1' color='#FFFFFF'>Product Name</font></td><td width='650'><input type=text name=pname size='50'></td></tr>";
         print "<tr bgcolor='#000066'><td width='110'><font face='Verdana' size='1' color='#FFFFFF'>Product Price</font></td><td width='650'><input type=text name=pprice size='20'></td></tr>";
         print "<tr bgcolor='#000066'><td width='110'><font face='Verdana' size='1' color='#FFFFFF'>Warranty<br><font color='#cccccc'>(View Warranties Below)</font></font></td><td width='650'><select name='swarranty'><? echo $option_value1; ?></select></td></tr>";
         }
         print "</table>";
         print "<input type='hidden' name='action' value='add'>";
         print "<input type='hidden' name='doadd' value=1>";
         print "<br><br><input type='submit' name='sub' value='Add product'></form>";


}
 print "</tr></table>";

}else if (isset($doadd) and ($doadd == 1)) {
      $query = "SELECT stock_status FROM product_status WHERE product_id=$pid";
      $result = mysql_query($query) or die("Query failed");

      $line = mysql_fetch_array($result);

      if ($line[0] <> "") {
          print "That product ID is already in use, go back and change ID";
          die;
      }else{


              $pname = str_replace("'","`",$pname);
              $pprice = str_replace(",","",$pprice);
              $pprice = str_replace("'","`",$pprice);
              $scomment = str_replace("'","`",$scomment);
      $query = "INSERT INTO product_status (product_id, manufacturer, product_name, product_price, product_sku, shipping, stock_status, stock_comment, warranty, warranty_comment, return, return_comment) VALUES ('$pid','$pmanufacturer','$pname','$pprice','$psku','$pshipping','$sstatus','$scomment','$swarranty')";
      $result = mysql_query($query) or die("Query failed11");

      print "<br><font face='Verdana' size='2'>New product added! (Product's ID: $pid)</font>";

      }

swapnamaria

I can't say I fully understand your problem but if it is establish a relationship between the two tables, you can do it like this :

Add a field in the product_status table for entering the warranty id
Pass the warranty id in the drop down box instead of the comment

$post1 = mysql_query ("SELECT aw_id,warranty_comment,aw_tag FROM warranty ORDER BY warranty_comment ASC");

while ($row=mysql_fetch_array($post1) ) {
$fid = $row["aw_id"];
$fname1 = $row["warranty_comment"];
$trimmed = trim($fname1);
$option_value1 .= "<OPTION value=\"$fid\">$fname1</option>";

}

Insert this value in to the product_status table when you update the database.

$query = "INSERT INTO product_status (product_id, manufacturer, product_name, product_price, product_sku, shipping, stock_status, stock_comment, warranty_id, return, return_comment) VALUES ('$pid','$pmanufacturer','$pname','$pprice','$psku ','$pshipping','$sstatus','$scomment','$swarranty')";
$result = mysql_query($query)

You can then pull out the data from the database using a join query
Example: select * from product_status,warranty where product_status.warranty_id = warranty.aw_id

Hope this helps.. If it is not clear, pls do tell me..

horstuff

Hey, that works so far. Very good. But how do I finish #4, the join? How would I print the results in the that dropdown? Thanks very much.

swapnamaria

hi,

Join query will be something like this -

$strSql = "select * from product_status,warranty where product_status.warranty_id = warranty.aw_id and product_id = $id"

The recordset will conatin information about the product and also the warranty details.You can then retrieve it using

$result = mysql_query($strSql)

while ($a_row = mysql_fetch_array($result)){

$warranty_comment = $a_row['warranty_comment'];
$product_name      = $a_row['product-name'];

... and so on

}

I hope this is clear. The above code will be used when you need to retrieve data.

You can populate a dropdown menu using the following code

<?php
$post1 = mysql_query ("SELECT aw_id,warranty_comment,aw_tag FROM warranty ORDER BY warranty_comment ASC");

print ("<select name='name' >");
while ($row=mysql_fetch_array($post1) ) {
$fid = $row["aw_id"];
$fname1 = $row["warranty_comment"];
$trimmed = trim($fname1);
?>
<option value="<?=$fid?>"><?=$fname1?></value>
<?php
print ("</select>");
}
?>

Hope this is clear..