Help inserting data to multiple tables in mysql database

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
matt001
Forum Newbie
Posts: 2
Joined: Tue Jun 28, 2005 3:05 am

Help inserting data to multiple tables in mysql database

Post by matt001 »

Hi

I have a form that currently submits data to 1 mysql db. Now what I want to do is have an include form file that will allow to asociate supplier data for that product which will then add that data to a different mysql table.

Then when I go and and look at a product I need to be able to see what suppliers sell that particluar product.

I can't really post the code on the forum as its a 4 different files but I can either put up a web address that has the files on it that you can download or I can send them via email

If anyone can give me a hand that would be great, Please Private Message Me.

Thanks

Matthew
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

you will need two tables.

one for the product, stored with an auto_incremented 'ID' field (primary key)

then you will need a table for the suppliers with at least two fields, one for the supplier name, and one for the ID that associates to the product['id']

you can then perform your queries like

Code: Select all

$sql = "SELECT name FROM suppliers WHERE id = '".$product['id']."'";
matt001
Forum Newbie
Posts: 2
Joined: Tue Jun 28, 2005 3:05 am

Post by matt001 »

Below are the pages I have got for the coding

Code for populate_suppliers.php

Code: Select all

--------------------------------------------------------------------------------
<? include('mysql_connect.php') ?> 

<?php 

if($_POST['submit_supply']){ 

//do stuff 

} 

?> 
<form action="<?=$_SERVER['PHP_SELF']?>" method="post"> 
# of Suppliers<input type="text" name="no_of_suppliers"> 
<input type="submit" name="submit" value="Submit"> 
</form> 

<?php 
if($_POST['submit']) 
{ 
echo "<form action=\"".$_SERVER['PHP_SELF']."\" method=post><table><tr>"; 
for($i=0; $i<$_POST['no_of_suppliers'];$i++) 
{ 
$var="supplier".$i; 
echo "<td><select name=\"".$var."\">"; //form name will be supplier1 supplier2 etc.. 
$sql="SELECT * FROM suppliers"; 
$result=mysql_query($sql); 
while($row=mysql_fetch_assoc($result)) 
{ 
echo "<option value=\"".$row['supplierName']."\" >".$row['supplierName']."</option>"; 
} 

echo "</select></td>"; 
} 
echo "</tr></table><input type=submit name=submit_supply value=Submit></form>"; 
} 
?>
--------------------------------------------------------------------------------

Code for add.php

Code: Select all

<div id="breadCrumbBlock"><a name="top" id="top"></a>Home >> products >> Add </div> 
<div class="contentBlock"> 
Here you can add the details of your products. 
</div> 
<?php 

if(isset($_POST['Submit'])){//START IS SUBMITTED 

//Form POST variables 
$productSubcategory = clean($_POST['productSubcategory']); 
$productName = clean($_POST['productName']); 
$productNumber = clean($_POST['productNumber']); 
$vendorCode = clean($_POST['vendorCode']); 
$vendorName = clean($_POST['vendorName']); 
$productPrice = clean($_POST['productPrice']); 
$productOverview = clean($_POST['productOverview']); 
$productManLink = clean($_POST['productManLink']); 
$productBroLink = clean($_POST['productBroLink']); 
$productSupplier = clean($_POST['productSupplier']); 
$productSupplier1 = clean($_POST['productSupplier1']); 
$productSupplier2 = clean($_POST['productSupplier2']); 
$productSupplier3 = clean($_POST['productSupplier3']); 

$ex_subcategory = $productSubcategory; 

//Form FILES variables 
$filename = $_FILES['productImage']['name']; 
$filetype = $_FILES['productImage']['type']; 
$filesize = $_FILES['productImage']['size']; 
$error_code = $_FILES['productImage']['error']; 

//Check if file submitted, if no file then substitute no_image.jpg file 
if($error_code == "4"){//START CHECK IF FILE UPLOADED 

$productImage = $no_image; 

} else {//ELSE CHECK IF FILE UPLOADED 

$random = rand(0, 1000); 

$productImage = $random."_".$vendorCode.".jpg"; 

}//END CHECK IF FILE UPLOADED 

//Check input 
include('../includes/checkinput.php'); 

$check[] = checkInput('required','alphaNumeric',$productSubca
tegory,'Subcategory'); 
$check[] = checkInput('','price',$productPrice,'product price'); 
$check[] = checkInput('','file',$filetype,'image'); 


//Get number of values in the returned $check array 
$checkArray = array_count_values($check); 

//If returned $check array is not empty list errors 
if(!empty($checkArray)) {//START ERRORS EXIST 

?> 
<div class="contentBlock"> 
<strong>The following errors occurred -</strong> 
<ul id="errorList"> 
<?php 

//If present print each error message in the $check array 
foreach($check as $result) {//START LIST ERRORS 

if(!empty($result)) {//START DISPLAY RESULT 

echo "<li>".$result."</li>"; 

}//END DISPLAY RESULT 

}//END LIST ERRORS 

?> 
</ul> 
<p>Please try again.</p> 
</div> 
<?php 
$fill = TRUE; 
include('add_form.php'); 
?> 
<?php 

} else {//ELSE ERRORS EXIST 


//Check if item number already exists 
$query=mysql_query("SELECT * FROM products WHERE productNumber='$productNumber' LIMIT 1") or die (mysql_error()); 

if(mysql_num_rows($query) > 0) {//START CHECK ITEM NAME EXISTS 

?> 
<div class="contentBlock"> 
<p>The product number "<?php echo $productNumber; ?>" <a href="products.php?product=<?php echo $productNumber; ?>" target="_self">already exists</a>, please choose a unique number.</p> 
</div> 
<?php 
$fill = TRUE; 
include('add_form.php'); 
?> 
<?php 

} else {//ELSE CHECK ITEM NAME EXISTS 


if(!move_uploaded_file($_FILES['productImage'][
'tmp_name'],$product_image_dir . $productImage) && $error_code != '4'){//START CHECK IMAGE UPLOAD 

?> 
<div class="contentBlock"> 
<p>Your image did not upload correctly, please try again <?php echo $error_code;?></p> 
</div> 
<?php 

} else {//ELSE CHECK IMAGE UPLOAD 

//Get category from subcategory 
$query=mysql_query("SELECT category FROM subcategories WHERE subcategoryId='$productSubcategory' LIMIT 1") or die (mysql_error()); 

$data=mysql_fetch_assoc($query); 

$productCategory = $data['category']; 

//$query=mysql_query ("SELECT VendorName FROM vendors WHERE VendorID = VendorID") or die (mysql_error()); 

$query=mysql_query("INSERT INTO products SET productNumber='$productNumber', productCategory='$productCategory', productSubcategory='$productSubcategory', productName='$productName', productOverview='$productOverview', vendorCode='$vendorCode', vendorName='$vendorName', productManLink='$productManLink', productBroLink='$productBroLink', productPrice='$productPrice', productImage='$productImage',modified = NOW()") or die (mysql_error()); 

if(!$query) {//START CHECK ADD TO SUBCATEGORY QUERY 

?> 
<div class="contentBlock"> 
<p>There was an error adding the product into the database, please try again.</p> 
</div> 
<?php 
$fill = TRUE; 
include('add_form.php'); 
?> 
<?php 

} else {//ELSE CHECK ADD TO SUBCATEGORY QUERY 

?> 
<div class="contentBlock"> 
<p>The product was added successfully. You can add another product below.</p> 
</div> 
<?php 

$query=mysql_query("SELECT * FROM products WHERE productNumber='$productNumber' LIMIT 1") or die (mysql_error()); 

while($data=mysql_fetch_assoc($query)){//START DISPLAY DATA 

$productId =$data['productId']; 
$productCategory =$data['productCategory']; 
$productSubcategory =$data['productSubcategory']; 
$productName =$data['productName']; 
$productNumber =$data['productNumber']; 
$vendorCode =$data['vendorCode']; 
$vendorName =$data['vendorName']; 
$productSpecs =$data['productSpecs']; 
$productOverview =$data['productOverview']; 
$productManLink = $data['productManLink']; 
$productBroLink = $data['productBroLink']; 
$productPrice =$data['productPrice']; 
$productImage =$data['productImage']; 
?> 
<?php 
include('product_details.php'); 
?> 
<?php 
} 
?> 
<div class="contentBlock"> 
<p>Add another product.</p> 
</div> 
<?php 
include('add_form.php'); 
?> 
<?php 

}//END CHECK ADD TO SUBCATEGORY QUERY 

}//END CHECK IMAGE UPLOAD 

}//END CHECK ITEM NAME EXISTS 

}//END ERRORS EXIST 

} else {//ELSE IS SUBMITTED 

//check if get 
if(isset($_GET['subcatId'])){//START IS GET 

$ex_subcatId = $_GET['subcatId']; 

$query=mysql_query("SELECT * FROM subcategories WHERE subcategoryId='$ex_subcatId' LIMIT 1") or die (mysql_error()); 
$data=mysql_fetch_assoc($query); 
$ex_subcategory=$data['subcategory']; 

if(mysql_num_rows($query) < 1) {//START CHECK EXISTS 

//If not submitted or get, display add form 
?> 
<div class="contentBlock"> 
<p>The subcategory "<?php echo $ex_subcategory; ?>" does not exist, you must <a href="../items/subcategories.php?do=add" target="_self">create the subcategory</a> before adding products to it.</p> 
</div> 
<?php 
unset($ex_subcatId); 
include('add_form.php'); 
?> 
<?php 

} else {//ELSE CHECK EXISTS 

//Display add form 
?> 
<div class="contentBlock"> 
<p>Add a new product to the subcategory "<?php echo $ex_subcategory; ?>".</p> 
</div> 
<?php 
include('add_form.php'); 
?> 
<?php 

}//END CHECK EXISTS 

} else {//ELSE IS GET 

//If not submitted or get, display add form 
?> 
<div class="contentBlock"> 
<p>Type in the details for the new product.</p> 
</div> 
<?php 
include('add_form.php'); 
?> 
<?php 

}//END IS GET 

}//END IS SUBMITTED 
?>
--------------------------------------------------------------------------------

Code for add_form.php


Code: Select all

<div class="contentBlock"> 
<form action="<?php $PHP_SELF;?>" method="post" enctype="multipart/form-data" name="form1" id="form1" class="formWrapper"> 
<table width="550" border="0" cellpadding="0" cellspacing="0" class="formTable"> 
<!--DWLayoutTable--> 
<tr> 
<td width="220" valign="top" class="formRight">Select subcategory - </td> 
<td width="330" valign="top" class="formLeft"> 
<select name="productSubcategory" id="productSubcategory"> 
<?php 
include ('includes/populate_subcategories.php'); 
?> 
</select> 
</td> 
</tr> 
<tr> 
<td valign="top" class="formRight">Select image to upload - </td> 
<td valign="top" class="formLeft"><input name="productImage" type="file" id="productImage" /></td> 
</tr> 
<tr> 
<td valign="top" class="formRight">Recharge Product Code - </td> 
<td valign="top" class="formLeft"><input name="productNumber" type="text" id="productNumber" value="<?php if(isset($fill)){ echo stripslashes($_POST['productNumber']);} ?>" /></td> 
</tr> 
<tr> 
<td valign="top" class="formRight">Product name - </td> 
<td valign="top" class="formLeft"><input name="productName" type="text" id="productName" value="<?php if(isset($fill)){ echo stripslashes($_POST['productName']);} ?>" /></td> 
</tr> 
<tr> 
<td valign="top" class="formRight">Vendor- </td> 
<td valign="top" class="formLeft"> 
<select name="vendorName" id="vendorName"> 
<?php 
include ('includes/populate_vendors.php'); 
?> 
</select> 
</tr> 
<tr> 
<td valign="top" class="formRight">Vendor Product Code - </td> 
<td valign="top" class="formLeft"><input name="vendorCode" type="text" id="vendorCode" value="<?php if(isset($fill)){ echo stripslashes($_POST['vendorCode']);} ?>" /></td> 
</tr> 
<tr> 
<td valign="top" class="formRight">Product price - $ </td> 
<td valign="top" class="formLeft"><input name="productPrice" type="text" id="productPrice" value="<?php if(isset($fill)){ echo stripslashes($_POST['productPrice']);} ?>" /></td> 
</tr> 
<tr> 
<td valign="top" class="formRight">Manufacturer link - </td> 
<td valign="top" class="formLeft"><input name="productManLink" type="text" id="productManLink" value="<?php if(isset($fill)){ echo stripslashes($_POST['productManLink']);} ?>" /> 
eg. <a href="http://www.example.com" target="_blank">http://www.example.com</a></td> 
</tr> 
<tr> 
<td valign="top" class="formRight">Brochure link - </td> 
<td valign="top" class="formLeft"><input name="productBroLink" type="text" id="productBroLink" value="<?php if(isset($fill)){ echo stripslashes($_POST['productBroLink']);} ?>" /> 
eg. <a href="http://www.example.com" target="_blank">http://www.example.com</a></td> 
</tr> 
<tr> 
<td valign="top" class="formRight">Suppliers: - </td> 
<td valign="top" class="formLeft"> <?php 
include ('includes/populate_suppliers.php'); 
?>&nbsp;</td> 
</tr> 
<tr> 
<td valign="top" class="formRight"><!--DWLayoutEmptyCell-->&nbsp;</td> 
<td valign="top" class="formLeft"><!--DWLayoutEmptyCell-->&nbsp;</td> 
</tr> 
<tr> 
<td valign="top" class="formRight"><!--DWLayoutEmptyCell-->&nbsp;</td> 
<td valign="top" class="formLeft"><!--DWLayoutEmptyCell-->&nbsp;</td> 
</tr> 
<tr> 
<td colspan="2" valign="top" class="formLeft">Product Overview - </td> 
</tr> 
<tr> 
<td colspan="2" valign="top" class="formCenter"><textarea name="productOverview" cols="50" rows="5" id="productOverview"><?php if(isset($fill)){ echo stripslashes($_POST['productOverview']);} ?></textarea></td> 
</tr> 
<tr> 
<td colspan="2" valign="top" class="formCenter"><input type="submit" name="Submit" value="Add product" onClick='this.value="Adding product Please Wait!";window.status="Adding product, if you are uploading an image this may take a while.";return true' class="formButton" />&nbsp;&nbsp;<input type="submit" name="Submit" value="Reset" class="formButton" /></td> 
</tr> 
</table> 
</form> 
</div>
--------------------------------------------------------------------------------
SQL Dump

CREATE TABLE `suppliers2products` (
`productID` varchar(20) NOT NULL default '',
`supplierID` varchar(20) NOT NULL default '',
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

---------------------------------------------------------------------------------

What I want is when I go and add a new product I enter in all the details. My Problem that I have is after I have typed in all the product info then I choose how many suppliers sell that product, then I choose the suppliers, from the drop down boxes. This is what I need help with I don't know how I add the Suppliers to the suppliers2products table.

Matthew
Post Reply