two tables being used to display results

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
horstuff
Forum Newbie
Posts: 2
Joined: Thu Feb 27, 2003 3:48 am

two tables being used to display results

Post by 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.

Code: Select all

$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>";

          }
Rob the R
Forum Contributor
Posts: 128
Joined: Wed Nov 06, 2002 2:25 pm
Location: Houston

Post by Rob the R »

First thing you'll want to do is to store the AW_ID, and not the warranty comment, in the PRODUCT_STATUS table. You can change your building of the warranty drop-down list so that it displays the comment but returns the ID number:

Code: Select all

$post1 = mysql_query ("SELECT warranty_comment,aw_id 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>"; 
}
This will mean that $swarranty will have the AW_ID number when it is selected in the drop-down list.

Once the AW_ID is stored in the PRODUCT_STATUS table, the query to pull the combined data is not complicated:

Code: Select all

select
   ps.product_id, ps.product_name, ps.product_price,
   w.warranty_comment, w.aw_tag
from
   product_status ps,
   warranty w
where
   ps.aw_id = w.aw_id ;
Post Reply