SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    313
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Code syntax problems on revising code

    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.

  2. #2
    SitePoint Evangelist
    Join Date
    Aug 2005
    Posts
    453
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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();
    $i 0;

    foreach (
    $_POST['category'] as $category_id) {
        
    $values[$garment_id]['cat'] = $category_id;
        
    $values[$garment_id]['color'] =  $colour_id[$i];
        
    $values[$garment_id]['size'] = $size_id[$i];
        
    $i++;
        }

          
    // insert garment_id/category_id, garment_id/color_id, and garment_id/size_id pairs into garment to category, garment to color, and garment to size lookup tables

    foreach( $values as $k ) {
        
    $cat_sql "INSERT INTO garment_to_category SET garment_id = '$k', category_id = '$values[$k]['cat']'";

        
    $color_sql "INSERT INTO garment_to_colour SET garment_id = '$k', colour_id = '$values[$k]['color']'";

        
    $size_sql "INSERT INTO garment_to_size SET garment_id = '$k', size_id = '$values[$k]['size']'";

        
    $result $db->query($cat_sql);
        
    $result $db->query($color_sql);
        
    $result $db->query($size_sql);
    }
          
    // if successful, redirect to confirmation page
    if ($result) {
        
    $db->close();
        
    header('Location: listGarments.php?action=inserted&title='.$_POST['title']);
        } 
    I think that should work, it is untested but give it a try.
    You will have multiple queries instead of one, you might want to check for errors and exit if there is a problem with a query.
    Computers and Fire ...
    In the hands of the inexperienced or uneducated,
    the results can be disastrous.
    While the professional can tame, master even conquer.

  3. #3
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    313
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks so much for that code. I'll give it a try.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •