functionality question

[Dillenger]

Ok, I'm working on a database, and I'm a bit stupped for what I should do here... What I have is...

1. A table for general info:
   date
   address
   type
   description
   comments

Then you submit it. and it goes into the database, NOW, the hard part is, with this entry, I have to associate categories, and then details for that category... Each entry will have different categories, and there are about 150 to choose from... so whats the best way for me to setup my tables for this to work...

Right now I have 3 tables

1. For the general Info
2. For the categories (because they are constantly being added to)
3. One for details of a category (because this is also constantly being added to)

[lazzerous]

you could have a field in the general info called categories, and reference id numbers from categories table in a comma-delimited format.

example entry:

date: 05-07-04
address: 123 fakey ct.
type: personal
description: describe this
comments: i've lost my tail
categories: 1,7,18,29,176

[Dillenger]

thanks,

but then, how do i associate the category with a category detail? which is stored in yet another table... and how would I output this info in PHP?

[lazzerous]

link by id

categories table:
id int(11) unique auto_increment
cat_name varchar(50)

category_info table:
cat_id int(11) unique
special_info_one char(50)
special_info_two char(50)
special_info_three char(50)
etc

category_info.cat_id and categories.id are the same number - make sense?

so ifan entry in the general_info table had an entry in categories '1,3,7', you would do a run like this:

$cat_ids = "1,3,7";
$cat_ids = explode(",", $cat_ids);

for ($i=0; $i<sizeof($cat_ids); $i++)
  {
  $get_cat_info = "SELECT categories.name, category_info.special_info_one FROM categories, category_info WHERE categories.id='$cat_ids[$i]'  AND category_info.cat_id = categories.id";
  }

[Dillenger]

this might work, but the thing of it is... there is more than one entry for my general info, there may be thousands...

and for 1 general info I may have the same category as another general info, say like "projects" or something... except the detail for that category will be different for each general info...

So one 1 entry may look like this:

General ->Category "projects" -> Detail "this is detail 1"

then the next one may look like this:

General ->Category "projects" -> Detail "this is detail 2"

And so on... which is why the detail is stored in yet ANOTHER table, that I need to reference somehow... its kinda like a sub referece though, because the general will reference the category, and then the category will reference the detail???

[Dillenger]

OK, I've kinda figured it out, but not really, can you tell me anything i'm doing wrong with my code???

<?
				$sql = "select * from newproject_data WHERE project_address= '$project_address'";
				$result = mysql_query($sql);
				$owners = mysql_fetch_array($result);

			$nprojectID=$owners['nprojectID'];
			$area1=$owners['area1'];
			$issue_date=$owners['issue_date'];
			$updissue_date=$owners['updissue_date'];
			$project_address=$owners['project_address'];
			$project_type=$owners['project_type'];
			$comments=$owners['comments'];
			$project_description=$owners['project_description'];
			$published=$owners['published'];
			$catID=$owners['catID'];
			$detailID=$owners['detailID'];

			echo "Issue Date: $issue_date<br><br>";
			echo "$area1 - $project_address ($project_type)<br>";
			echo "<br><br>";

			?>		<br>				<?


					$new_cats = "newproject_data.catID"; 
					$new_cats = explode(",", $new_cats); 

					for ($i=0; $i<sizeof($new_cats); $i++) 
					{ 
					$sql = "SELECT category.*, newproject_data.*, projectdata_details.* FROM category, newproject_data, projectdata_details WHERE category.catID='$new_cats[$i]' AND projectdata_details.projID=newproject_data.detailID AND newproject_data.nprojectID=newproject_data.nprojectID"; 

					$result = mysql_query($sql);
					if (!$result) {
					echo("ERROR: " . mysql_error() . "\n$sql\n");
					}else{
					$owners = mysql_fetch_array($result);

					$id=$owners['nprojectID'];
					$name=$owners['catSelect'];
					$detail=$owners['description'];

					echo "<span class=headers>$name</span><br>";
					echo "$detail<br>";
					echo "$nprojectID<br>";
					}
					}

					?>

[Dillenger]

nevermid, i fixed it, it kinda works now, but now I have to create another explode for $detailID... do I have to create a second for statement?