Hi everyone,

I have a form which allows for certain information to be entered into a 'garments' table within my database.

I'm trying to create some php code that will take care of all the different primary key and foreign key relationships. One of the menus in my form allows you to choose a garment category. As can be seen by the comments, the code below establishes the relationship between the category_id (primary key in category table) and the garment_id (the primary key in the garments table). It does this so that these values can then be inserted into a 'garment_to_category' lookup table. The code for this scenario is as follows:

PHP Code:
   // build array of garment_id and category_id pairs, one for each category
      
$values = array();
      foreach (
$_POST['category'] as $category_id) {
        
$values[] = "($garment_id$category_id)";
        }
      
// convert array to comma delimited string
      
$values implode(',',$values);
      
// insert garment_id/category_id pairs into garment to category lookup table
      
$createLookup 'INSERT INTO garment_to_category (garment_id, category_id)
                        VALUES '
.$values;
      
$result $db->query($createLookup);
      
// if successful, redirect to confirmation page
      
if ($result) {
        
$db->close();
        
header('Location: listGarments.php?action=inserted&title='.$_POST['title']);
        } 
The problem is that the form also allows you to choose a colour and a size. I have a colours table and a sizes table within my database also. There is also garment_to_colour and a garment_to_size lookup tables. Therefore I'd need to revise the above code to establish the relationships as it does for categories and also have the code insert the values into the size and colours lookup tables.

I've tried to have a go at this myself with the code below, but it's full of syntax errors:

PHP Code:
   // build array of garment_id/category_id pairs, garment_id/colour_id pairs, and garment_id/size_id pairs one for each category, colour and size
      
$values = array();
      foreach (
$_POST['category'] as $category_id) && ($_POST['colour'] as $colour_id) && ($_POST['size'] as $size_id){
        
$values[] = "($garment_id$category_id)" && "($garment_id$colour_id)" && "($garment_id$size_id)";
        }
      
// convert array to comma delimited string
      
$values implode(',',$values);
      
// insert garment_id/category_id, garment_id/colour_id, and garment_id/size_id pairs into garment to category, garment to colour, and garment to size lookup tables
      
$createLookup 'INSERT INTO garment_to_category (garment_id, category_id)
                         AND
                       INSERT INTO garment_to_colour (garment_id, colour_id)
                       AND
                       INSERT INTO garment_to_size (garment_id, size_id)
                        VALUES '
.$values;
      
$result $db->query($createLookup);
      
// if successful, redirect to confirmation page
      
if ($result) {
        
$db->close();
        
header('Location: listGarments.php?action=inserted&title='.$_POST['title']);
        } 
Can someone tell me how I'd alter the code to include these size and colour tables with the correct syntax?

Really appreciate any help offered.