problem with posting to relational database
Posted: Thu Apr 23, 2009 3:40 pm
i'm having a problem posting multiple color fields with image upload fields to a relational database (many to many). the user can enter multiple colors and upload a corresponding image (the colors and upload fields are added by the user when clicking a button) I have the code here for posting the color and image.
what is should do:
1.if no duplicate color add color and image to ColorTable and ColorSwatches and add primary key to Vendor2Color (table that holds the relation among the vendor, color and image)
2. if there is a duplicate color (ie... if red has been entered before) don't add to ColorTable or ColorSwatches but get the primary key of the color already listed and add it to the Vendor2Color table.
what it does is:
1. looks to see if a color has already been entered into the database (works)
2. if it a color has it just updates the relational table to match it with the vendor (a vendor can have many colors and more than one vendor can have the same colors) (works)
3. if the color has not been added to the database it adds the color and the image and adds primary key to the relational table. (it works for the color but not image)
where the code above goes wrong:
1. for every color it posts images twice
2. posts the swatch even if the color has already been entered.
any help will be greatly appreciated.
what is should do:
1.if no duplicate color add color and image to ColorTable and ColorSwatches and add primary key to Vendor2Color (table that holds the relation among the vendor, color and image)
2. if there is a duplicate color (ie... if red has been entered before) don't add to ColorTable or ColorSwatches but get the primary key of the color already listed and add it to the Vendor2Color table.
what it does is:
1. looks to see if a color has already been entered into the database (works)
2. if it a color has it just updates the relational table to match it with the vendor (a vendor can have many colors and more than one vendor can have the same colors) (works)
3. if the color has not been added to the database it adds the color and the image and adds primary key to the relational table. (it works for the color but not image)
where the code above goes wrong:
1. for every color it posts images twice
2. posts the swatch even if the color has already been entered.
any help will be greatly appreciated.
Code: Select all
foreach ($arr_color as $color) {
$duplicates = "SELECT Color FROM ColorTable WHERE Color = '$color'";
$dup_result = mysql_query($duplicates);
if (mysql_num_rows($dup_result) > 0){
$select_color_id = mysql_query("SELECT ColorID FROM ColorTable WHERE Color = '$color'");
$select_result = mysql_fetch_row($select_color_id);
$already_in = "INSERT INTO Vendor2Color (V2CID, V2CvendorID, V2CColorID, V2CImageID) Values('NOT NULL', '$vendorID','$select_result[0]', '$select_result[0]')";
$already_in_result = mysql_query($already_in);
} else {
$query2 = "INSERT INTO ColorTable (ColorID, Color) VALUES('NOT NULL', '$color')";
$result2 = mysql_query($query2);
$colorID = mysql_insert_id();
foreach ($arr_upload as $image) {
if(is_uploaded_file($image)) {
$imgData = addslashes(file_get_contents($image));
$size = getimagesize($image);
$swatch_query = "INSERT INTO ColorSwatches (ImageID, ImageType, Image, ImageSize, ImageName) VALUES ('NOT NULL', '{$size['mime']}', '{$imgData}', '{$size[3]}', '{$image}')";
$sq_result = mysql_query($swatch_query);
$imageID = mysql_insert_id();
$colorQuery = "INSERT INTO Vendor2Color (V2CID, V2CVendorID, V2CColorID, V2CImageID) VALUES('NOT NULL', '$vendorID', '$colorID', '$imageID')";
$cqresult = mysql_query($colorQuery);
}//if upload
}//foreach swatch
} //close else
}//close color foreach