Multiple Row Select and Input from Checkboxes

[aphomic]

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>

		<?
		}

	}

		?>[code]

[/code]

[cr-ispinternet]

HI there,

 

not sure if this will help, am i right in saying your looking to check a check box based on values from the database?

if thats the case this is the best way i found to do this, its a little long winded but its working for me works for input

boxes aswell....

 

if ($customer_one == "customer" or $business_two == "partner" or $partner_three == "business"){
        $status_business_customer = "checked";
        }

 

and then use this as the check box code

 

<input type="checkbox" name="relationship_customer" value="customer" style="float: left" $status_relationship_customer><font face="Tahoma" style="font-size: 8pt">Customer</font></td>
                <td height="24" width="86">
<input type="checkbox" name="relationship_partner" value="partner" style="float: left"$status_relationship_partner><font face="Tahoma" style="font-size: 8pt">Partner</font></td>
                <td height="24" width="81">
<input type="checkbox" name="relationship_business" value="business" style="float: left"$status_relationship_business><font face="Tahoma" style="font-size: 8pt">Business</font></td>

 

this works for me and base don whats in the database it will select the check boxs accordingly

 

Alan

[aphomic]

Hi Alan,

 

Thanks for replying, that sounds interesting I hadn't thought of setting a checked value like that.  My issue, and I'm not sure if this would work with your method, is that I'm dynamically creating the checkbox list from one table, and then trying to 'checked' them based on whether the cat_id from that table - categories - matches the cat_id from my lookup table. So I'm running two seperate sql statements.

 

the user can select multiple checkboxes, so each product in effect should display more than one checkbox as checked, but at the moment my codes seems to be only matching one row.

 

what do you think?

 

thanks

Michael

[cr-ispinternet]

Hi Again,

 

right so you basically running 2 queries, the only thing i can think of doing now is

a $num_result and then telling it to check somehting possibly like this...

 

<?

if($cat_id1 == "$cat_id2") {
$num_result_catid = "1"
echo ("Cat Id Matches");
} else {
$num_result_catid = "0"
echo ("Catid doesnt match");
}

?>

 

I guess what im trying to say is basically do an if compare,

compare the 2 cat ids from your 2 queries and if you get a 1

then basically you should be able to then set $num_result_catid as

checked in the checkbox properties...

 

sorry i cant be of more help im a noivce really and have only recently started

posting answers, scared ill look stipud :-)

 

ill keep thinking about it and let you know of anythig i can think of

 

Alan