I'm building a CMS for an ecommerce site (php/msql). I want to allow the products to be assigned to multiple categories and am doing this through a lookup table, in other words the db table setup is: - products, - categories, - lookup - has cat_id and prod_id
I'm building the product update page which needs to allow editing of the data in the product table and the categories the product is assigned to.
The INSERT part of this I've got working fine - it uses a foreach loop to build the values from an array of selected checkboxes - tested and working.
Its the displaying the checkboxes as selected part which has stumped me. Basically I'm pulling out the categories as a list of checkboxes, and then trying to find all those cat_id in the lookup (seperate sql query) that match to echo checked in the checkbox. At the moment it is just selecting the first. I think I need to pull the values from the lookup table into an array and check for a match in the array, but I'm not sure if this is the correct method, or on the syntax for this.
any help would be useful - I realise the code is messy, I've been chopping it up trying to find a solution!
<?php
if (isset($_POST['submit']) )
{
//UPDATE THE PRODUCT DETAIL
$ID = $_POST['ID'];
$title = $_POST['title'];
$keywords = $_POST['keywords'];
$desc = $_POST['desc'];
$text = $_POST['text'];
$added = $_POST['added'];
$thumb = $_POST['thumb'];
$image = $_POST['image'];
$price = $_POST['price'];
$category = $_POST['category'];
$sql="UPDATE products SET prod_title='$title', prod_keywords='$keywords', prod_description='$desc', prod_text='$text', prod_thumb='$thumb', prod_image='$image', prod_price='$price' WHERE prod_id = $ID";
mysql_query($sql) ;
if ( is_array($_POST['checkbox']) ) {
// begin SQL statement
$catsql= 'INSERT INTO lookup (prod_id,cat_id) VALUES';
// foreach loop creates the data input section
foreach( $_POST['checkbox'] AS $key => $value ) {
// something like (1,1),(1,2),(1,3)
// should all be numeric data, no quotes required
$catsql.= '('. $_GET['ID'] .','. $value .')';
// if this is any value but the last value, use a comma
// to separate records input
if ( $key != ( count($_POST['checkbox']) -1 ) )
$catsql.= ',';
mysql_query($catsql);
}
}
echo " <p><b>Product updated successfully</p>" ;
}
else {
//GET THE PRODUCT TO UPDATE
$ID = $_GET['ID'];
$sql = "SELECT * FROM products WHERE products.prod_id = '$ID' ";
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result)) {
?>
<br/>
<form name='add' method='post'>
<input type="hidden" name="ID" id="ID" value="<?php echo $row['prod_id']; ?>" />
<div id="categories">
<label for='category'>Category:</label>
<br clear="all"/>
<?
$sql3 = "SELECT * FROM categories LEFT JOIN lookup ON lookup.cat_id = categories.cat_id WHERE prod_id = '$ID' ";
$result3 = mysql_query($sql3);
$rec = mysql_fetch_assoc($result3)
$sql2 = "SELECT * FROM categories";
$result2 = mysql_query($sql2);
while ($catrow = mysql_fetch_assoc($result2)) {
?>
<?php echo $rec['cat_id']; ?>
<p class="check"><input type="checkbox" name="checkbox[]" id="checkbox" value="<?php echo $catrow['cat_id']; ?>" <?php if(($catrow['cat_id']) == ($match)) { echo 'CHECKED'; } ?> /><label><?php echo $catrow['cat_title']; ?></label></p>
<?
}
?>
<br />
<br />
</div>
<div id="default">
<label for='title'>Title:</label>
<input type='text' name='title' id="title" class='textinput' value="<?php echo $row['prod_title']; ?>" /><br clear="all"/>
<label for='title'>Meta Keywords:</label>
<input type='text' name='keywords' id="keywords" class='textinput' value="<?php echo $row['prod_keywords']; ?>" /><br clear="all"/>
<label for='desc'>Meta Description:</label>
<input type='text' name='desc' id="desc" class='textinput' value="<?php echo $row['prod_description']; ?>" /><br clear="all"/>
<label for="text">Description:</label>
<textarea id="summary" name="summary"><?php echo $row['prod_text']; ?></textarea><br />
<label for='thumb'>Thumbnail:</label>
<input type='text' name='thumb' id="thumb" class='textinput' value="<?php echo $row['prod_thumb']; ?>" /><br clear="all"/>
<label for='image'>Image:</label>
<input type='text' name='image' id="image" class='textinput' value="<?php echo $row['prod_image']; ?>" /><br clear="all"/>
<label for='price'>Price:</label>
<input type='text' name='price' id="price" class='textinput' value="<?php echo $row['prod_price']; ?>" /><br clear="all"/>
<input type='submit' value='submit' name='submit' class='publishbutton' />
<br />
</form>
<?
}
}
?>
