Revising code

[NIA2]

Hi everyone,

I'm trying to build a form which allows for certain information to be entered into a 'garment' table within my database.

I'm trying to create the php code so that it will take care of all the different primary key and foreign key connections. One of the menus in my form allow you to input a garment category associated with the particular garment record. This would insert the category into the 'categories' table within my database, but it also 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 in a 'garment_to_category' lookup table. The code for this scenario is as follows:

   // 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 insert a colour and size. There is 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.

Can someone tell me how I'd alter the code to include these size and colour tables?

Really appreciate any help offered.

 

[NIA2]

Sorry all,

I wanted to revise the above post but couldn't find an edit button!

Anyway, instead of the following sentences which I explained incorrectly:

<<One of the menus in my form allow you to input a garment category associated with the particular garment record. This would insert the category into the 'categories' table within my database, but it also establishes the relationship between the category_id (primary key in category table) and the garment_id (the primary key in the garments table).>>

I meant to say the following:

One of the menus in my form allows you to choose a garment category associated with the particular garment record. 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).

Just wanted to clarify.

Wish I knew how to edit an existing post though.

 

[NIA2]

Just thought I'd give this a try myself and came up with the following 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']);
        }

Unfortunately, I got the following error:

Parse error: syntax error, unexpected T_BOOLEAN_AND in /Applications/MAMP/htdocs/newsite/admin/insertNewGarment.php on line 104

Line 104 is this line:

foreach ($_POST['category'] as $category_id) && ($_POST['colour'] as $colour_id) && ($_POST['size'] as $size_id){

I'm sure the rest of the code block has syntax errors also - can someone help me correct these?

 

[jpadie]

foreach ($_POST['category'] as $category_id) && ($_POST['colour'] as $colour_id) && ($_POST['size'] as $size_id){

you cannot do this. have a look at the manual for foreach.

i have not responded to your original question because i could not really understand what you were looking for. At a guess, what you would have to do is one of:

* create two forms. form 1 takes the garment and on submission outputs form 2 with sizes etc properly tailored (wahey) for the garment.
* use some javascript interaction to change the values of the size/colour selects based on the value of the garment selection. this involves loading the different permutations at runtime.
* use method 2 with some ajax interaction.

 

[NIA2]

Thanks for the reply.

I thought perhaps if I posted the full page code (below) you may understand a little better what I was after?

The first block of code that I displayed in my first post actually works. I was explaining that the purpose of the code was to find out which garment_id (primary key in garments table) belonged to which category_id (primary key in categories table). Once this was known, the information was entered into the gament_to_category lookup table. This lookup table contains only two columns - one is the garment_id column and the other is the category_id column (both are foreign keys). Having gotten the code to work with the categories, I just wanted to extend it to work with the colours and sizes because these two tables also have lookup tables, so the foreign keys would also need to be entered into these.

I'm still really struggling with php syntax - that's why my attempt at the code in my previous post was so messed up. I just thought someone could help me revise it so the code would take into account the size and colours.

If anyone understands or is interested in helping further, I'd be really grateful.

...and sorry if my explanations aren't that clear - most of the time I confuse myself!!

<?php
require_once('classes/database.php');
// this code always runs, and gets lists of suppliers and garment categories
$db=new Database('localhost','root','password','catalogue',0);
$getSuppliers = 'SELECT * FROM suppliers ORDER BY supplier';
$suppliers = $db->query($getSuppliers);
$getGarment_types = 'SELECT * FROM garment_types ORDER BY garment_type';
$garment_types = $db->query($getGarment_types);
$getCategories = 'SELECT * FROM categories ORDER BY category';
$categories = $db->query($getCategories);
$getColours = 'SELECT * FROM colours ORDER BY colour';
$colours = $db->query($getColours);
$getSizes = 'SELECT * FROM sizes ORDER BY size';
$sizes = $db->query($getSizes); 
// this first block runs only if the form has been submitted
if ($_POST) {
  // check for empty fields
  foreach($_POST as $key=>$value) {
    // authors is a sub-array, so skip
    if (is_array($value)) continue;
    $value = trim($value);
    if (empty($value)) {
      if ($key == 'code') {
        $error[] = 'You must select a code';
        }
      // if no supplier selected, value is 0, considered empty by PHP
      elseif ($key == 'supplier') {
        $error[] = 'You must select a supplier';
        }
      elseif ($key == 'colour') {
        $error[] = 'You must select a colour';
        }
      elseif ($key == 'size') {
        $error[] = 'You must select a size';
        }
      elseif ($key == 'garment_type') {
        $error[] = 'You must select a garment type';
        }
      else {
        $error[] = ucfirst($key).' is required';
        }
      }
    }
  // check that a garment category has been chosen
  if ($_POST['category'][0] == 'choose' && count($_POST['category']) < 2) {
    $error[] = 'Select at least one category, or choose "Not listed"';
    }
  // if all fields correctly filled, prepare to insert in database
  if (!isset($error)) {
    // final preparations for insertion
	// escape quotes and apostrophes if magic_quotes_gpc off
    if (!get_magic_quotes_gpc()) {
      foreach($_POST as $key=>$value) {
        // skip author sub-array
        if (is_array($value)) continue;
        $temp = addslashes($value);
        $_POST[$key] = $temp;
        }
      }
    // create a Database instance, and set error reporting to plain text
    $db = new Database('localhost','root','password','catalogue',0);
    // first check that the same garment code and supplier combination doesn't already exist
	$code = $_POST['code'];
	$supplier_id = $_POST['supplier_id'];
	$checkCode = "SELECT code FROM garments WHERE code = '$code' AND supplier_id = '$supplier_id'";
        $result = $db->query($checkCode);
    if ($result->num_rows > 0) {
      $error[] = 'A garment with that code and supplier already exists in the database';
      }
    else {
      // if code and supplier combination unique, insert garment in to garments table
	  	  
	  if (($_POST['supplier'] AND $_POST['garment_type']  AND $_POST['category'] AND $_POST['colour'] AND 					$_POST['size']) == 'other') 
{
$_POST['supplier'] = 0; 
$_POST['garment_type'] = 0; 
$_POST['category'] = 0; 
$_POST['colour'] = 0; 
$_POST['size'] = 0; 
}
	  $insert = 'INSERT INTO garments (supplier_id, garment_type_id, title, code, description, extra_info, image, swatch_image)
      VALUES ("'.$_POST['supplier'].'","'.$_POST['garment_id'].'",'.
      $_POST['title'].',"'.$_POST['code'].',"'.$_POST['description'].',"'.$_POST['extra_info'].',"'.$_POST['image'].'","'.$_POST['swatch_image'].'")';
      $result = $db->query($insert);
      // get the primary key of the record just inserted
	  $getGarment_id = 'SELECT garment_id FROM garments
                     WHERE code = "'.$_POST['code'].'"';
      $result = $db->query($getGarment_id);
      $row = $result->fetch_assoc();
      $garment_id = $row['garment_id'];
	  
	  // if "Select category(s)" and "Select colour(s) still selected, remove from the array
	if ($_POST['category'][0] == 'choose' || $_POST['colour'][0] == 'choose')
	array_shift(
	$_POST['category'],	$_POST['colour']
	);
if (in_array('other', $_POST['category']) && in_array('other', $_POST['colour'])) {
	$i = array_search('other', $_POST['category'] && $_POST['colour']);
	$_POST['category'][$i] = 0 AND $_POST['colour'][$i] = 0;
}
      // 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']);
        }
      }
	}
  }
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "[URL unfurl="true"]http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">[/URL]
<html xmlns="[URL unfurl="true"]http://www.w3.org/1999/xhtml"><!--[/URL] InstanceBegin template="/Templates/AdminTemplate.dwt" codeOutsideHTMLIsLocked="false" -->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<!-- InstanceBeginEditable name="doctitle" -->
<title>Insert new garment</title>
<!-- InstanceEndEditable -->
<link href="../main.css" rel="stylesheet" type="text/css" />
<link href="admin.css" rel="stylesheet" type="text/css" />

<script type="text/javascript" src="../p7pm/p7popmenu.js"></script>
<style type="text/css" media="screen">
<!--
@import url("../p7pm/p7pmh2.css");
-->
</style>
<!--[if lt IE 7]>
<link href="../win_ie.css" rel="stylesheet" type="text/css" />
<![endif]-->
<style type="text/css"></style>
<script language="JavaScript" type="text/JavaScript">
<!--
function Lvl_openWin(u,n,w,h,l,t,c,f) { //v2.2 4LevelWebs
  var x=((screen.width-w)/2);if(c==1){l=x;t=(screen.height-h)/2;}if(c==2){l=x}
	f+=',top='+t+',left='+l;LvlWin=window.open(u,n,f);LvlWin.focus();
}
//-->
</script>
<!-- InstanceBeginEditable name="head" --><!-- InstanceEndEditable -->
</head>
<body onload="P7_initPM(1,2,1,-20,10)">
<div id="topstrip"></div>
<div id="navcorp">
  <ul id="p7PMnav">
    <li><a href="#" class="p7PMtrg" id="height">List garments</a>
        <ul>
          <li><a href="#">List suppliers</a></li>
          <li><a href="#">List categories</a></li>
          <li><a href="#">List garment types</a></li>
          <li><a href="#">List colours</a></li>
          <li><a href="#">List sizes</a></li>
        </ul>
    </li>
    <li><a href="#" class="p7PMtrg" id="height">Add new garment</a>
        <ul>
          <li><a href="#">Add new supplier</a></li>
          <li><a href="#">Add new category</a></li>
          <li><a href="#" id="unique">Add new garment type</a></li>
          <li><a href="#">Add new colour</a></li>
          <li><a href="#">Add new size</a></li>
        </ul>
    </li>
    <li><a href="#" class="p7PMtrg" id="height">Register new user</a>
        <ul>
          <li><a href="#">List registered users</a></li>
        </ul>
    </li>
    <li><a href="#" id="height">View Catalogue</a></li>
    <li><a href="#">Logout</a></li>
    <!--[if lte IE 6]><style>#p7PMnav a{height:1em;}#p7PMnav li{height:1em;}#p7PMnav ul li{float:left;clear:both;width:100%}</style><![endif]-->
    <!--[if IE 6]><style>#p7PMnav ul li{clear:none;}</style><![endif]-->
  </ul>
</div>

<!-- InstanceBeginEditable name="EditRegion3" -->
<div id="background">
  <div id="wrapper">
    <div id="innerlayer">
      <h1>Insert new garment</h1>
	  <?php
if (isset($error)) {
  echo '<div id="alert"><p>Please correct the following:</p><ul>';
  foreach ($error as $item) {
    echo "<li>$item</li>";
	}
  echo '</ul></div>';
  }
?>
      <form id="garmentDets" name="garmentDets" method="post" action="<?php $_SERVER['PHP_SELF']; ?>">
        <table id="garmentInsert" cellpadding="0">
		
          <tr>
            <th width="180" class="labelLeft">Supplier</th>
            <td colspan="2"><select name="supplier" id="supplier">
                <option value="0" selected="selected">Select supplier</option>
                <option value="other">Not listed</option>
			      <?php
      while ($row = $suppliers->fetch_assoc()) {
        echo '<option value="'.$row['supplier_id'].'">'; 
        echo $row['supplier'].'</option>';
        }
      ?>
      </select>
            </td>
          </tr>
		  
          <tr>
            <th class="labelLeft">Garment type</th>
            <td colspan="2"><select name="garment_type" id="garment_type">
                <option value="0" selected="selected">Select garment type</option>
                <option value="other">Not listed</option>
							  			      <?php
      while ($row = $garment_types->fetch_assoc()) {
        echo '<option value="'.$row['garment_type_id'].'">'; 
        echo $row['garment_type'].'</option>';
        }
	  ?>
              </select>
            </td>
          </tr>
		 
		  
          <tr>
            <th class="labelLeft">Garment category</th>
            <td colspan="2"><select name="garment_cat[]" size="6" multiple="multiple" id="garment_cat">
                <option value="choose" selected="selected">Select garment category(s)</option>
                <option value="other">Not listed</option>
	   <?php
       while ($row = $categories->fetch_assoc()) {
         echo '<option value="'.$row['category_id'].'">'; 
         echo $row['category'].'</option>';
         }
       ?>
              </select>
            </td>
          </tr>
          <tr>
            <th class="labelLeft">Title</th>
            <td colspan="2"><input name="title" type="text" class="widebox" id="title" /></td>
          </tr>
          <tr>
            <th class="labelLeft" scope="row">Style Number </th>
            <td colspan="2"><input name="stylenum" type="text" class="narrowbox" id="stylenum" /></td>
          </tr>
          <tr>
            <th class="labelLeft" scope="row">Description</th>
            <td colspan="2"><textarea name="description" id="description"></textarea></td>
          </tr>
          <tr>
            <th class="labelLeft" scope="row">Extra info </th>
            <td colspan="2"><input name="extraInfo" type="text" class="widebox" id="extraInfo" /></td>
          </tr>
          <tr>
            <th class="labelLeft" scope="row">Colours</th>
            <td colspan="2"><select name="colour[]" size="6" multiple="multiple" id="colour">
                <option value="choose" selected="selected">Select colour(s)</option>
                <option value="other">Not listed</option>
							  			      <?php
      while ($row = $colours->fetch_assoc()) {
        echo '<option value="'.$row['colour_id'].'">'; 
        echo $row['colour'].'</option>';
        }
      ?>
              </select>

            </td>
          </tr>
          <tr>
            <th class="labelLeft" scope="row">Swatch image </th>
            <td colspan="2">Yes
              <input name="swatch" type="radio" value="y" />
              No
              <input name="swatch" type="radio" value="n" /></td>
          </tr>
          <tr>
            <th class="labelLeft" scope="row">Sizes</th>
            <td colspan="2"><select name="sizes" id="sizes">
                <option value="0" selected="selected">Select sizes</option>
                <option value="other">Not listed</option>
              			  			      <?php
      while ($row = $sizes->fetch_assoc()) {
        echo '<option value="'.$row['size_id'].'">'; 
        echo $row['size'].'</option>';
        }
          // close database connection
      $db->close();
	  ?>
			  </select>
            </td>
          </tr>
          <tr>
            <th class="labelLeft" scope="row">Image</th>
            <td width="190">Yes
              <input name="image" type="radio" value="y" />
              No
              <input name="image" type="radio" value="n" /></td>
            <td id="garmentSubmit"><input type="submit" name="Submit" value="Insert new garment" /></td>
          </tr>
        </table>
      </form>
    </div>
  </div>
</div>
<!-- InstanceEndEditable --><img src="../Images/transparent.gif" width="1" height="1"/>
<div id="footer">&copy; Copyright 2007. All rights reserved. </div>
</body>
<!-- InstanceEnd --></html>