A bit stuck here with php/database

[norbie]

Hi,

 

I'm not sure if I've posted this in the right section, so apologies if I haven't!

 

I have setup an e-commerce website using PHP/MySQL that has an administrator CMS backend.

 

The administrator adds a new product to the system and puts in the name, description etc. He also chooses what category the item belongs to, but it is possible that the item belongs to 2 categories.

 

The database structure is like so:

 

Category - id, name

Item - id, name, description

CatItem - itemid, catid

 

The 'CatItem' table is used to record which items belong to which category. This is because each item could belong to multiple categories.

 

Upon manually entering data into the database, this works fine. However I am a bit confused for the CMS adding an item. I can create a drop down list to choose a category for the item to belong to, but I need to create a button to "add another category?" which then creates another drop down box like before. This needs to provide the function to add at least say 3 drop down boxes.

 

I can't figure out how to do this so any help is much appreciated! Thanks.

[Adam]

Perhaps create a new table - "cat_products" (prod_id, cat_id), or something - with a row for each category a product belongs to? The SQL will be a bit more complex as you'll need to query multiple tables, but it's the best way.

 

I'd advise against solutions such as separating the categories by a delimiter or creating 5 or so mostly empty fields (i.e. 'cat1', 'cat2', 'cat3', etc).

 

Adam

 

 

[norbie]

Hi Adam,

 

That's exactly what I've done - it says in my first post.

 

I'm jsut figuring out a way to have the GUI enable that. Maybe checkboxes will be best. So he can tick the categories it belongs to?

[Adam]

Ohhh sorry, my mistake!

 

Yeah check boxes would work. Store them in an array like:

 

<input type="checkbox" name="cats[]" value="cat_id" /> cat_name

 

Then for the PHP side of things, something like:

 

foreach ($_POST['cats'] as $cat) {
    $insert = mysql_query("INSERT INTO CatItem (itemid, catid) VALUES('...', '{$cat}')");
}

 

Obviously that's just a basic idea, you'll probably want to build on it, perhaps add some error handling as well?

[norbie]

Excellent! That all works now.

 

I need to setup a page now so the administrator can edit an item. So it will be something like foreach record in the title for that item id, check the relevant checkbox on the list of categories.

 

Could be a bit more challenging..

[9three]

Provide the code so we can help you. We can't do EVERYTHING for you.

[norbie]

All sorted now!

 

I looked up which categories the product belonged to, then added the values to an array.

 

Then when creating the list of checkboxes, I used in_array to see if the checkbox value was in the array. If it was then I added the 'checked' attribute.

 

I now have a related problem. When editing an item, it needs to display the correct selected value for a drop down box. These items are DVDs and the drop down box is for the region the dvd belongs to.

 

At the moment I am using this code, but it's very messy looking and I wondered if there was a nicer way of doing it!

 

switch($regionid) {
			case "0";
			$selected0 = "selected=\"selected\"";
			break;
			case "1";
			$selected1 = "selected=\"selected\"";
			break;
			case "2";
			$selected2 = "selected=\"selected\"";
			break;
			case "3";
			$selected3 = "selected=\"selected\"";
			break;
			case "4";
			$selected4 = "selected=\"selected\"";
			break;
			case "5";
			$selected5 = "selected=\"selected\"";
			break;
			case "6";
			$selected6 = "selected=\"selected\"";
			break;
		};

<select name=\"regionid\">
		<option value=\"0\" " . $selected0 . ">0 - Region Free (Plays everywhere)</option>
		<option value=\"1\" " . $selected1 . ">1 - United States of America, Canada</option>
		<option value=\"2\" " . $selected2 . ">2 - Europe, including France, Greece, Turkey, Egypt, Arabia, Japan and South Africa</option>
		<option value=\"3\" " . $selected3 . ">3 - Korea, Thailand, Vietnam, Borneo and Indonesia</option>
		<option value=\"4\" " . $selected4 . ">4 - Australia and New Zealand, Mexico, the Caribbean, and South America</option>
		<option value=\"5\" " . $selected5 . ">5 - India, Africa, Russia and former USSR countries</option>
		<option value=\"6\" " . $selected6 . ">6 - Peoples Republic of China</option>
		</select>

 

Thanks for all your help so far.