referencing other tables

Dillenger

Ok,

I need to reference the ID of one table from another, but i'm unsure how to do it.

I have one table called newproject_data, and there is a row called categoryID ... which will hold id numbers, several in one row, seperated by commas, referencing another table called category, which has catID and catSelect...

So
category ID will have like 1, 5, 21 in a row...

then each one of those numbers links to an ID in category, and displays the information in catSelect that is associated with that ID... here is a code that someone put together for me, but I can't figure it out, can anyone help?:

$catID2 = $catID; 
						$catID2 = explode(",", $catID2); 

					for ($i=0; $i<sizeof($catID2); $i++) 
					 { 
					 $get_cat_info = "SELECT catSelect FROM category WHERE catID ='$catID2[$i]'  AND catID = newproject_data.catID";
					 $result = mysql_query($get_cat_info);
					 }

nemonoman

which will hold id numbers, several in one row, seperated by commas, referencing another table called category, which has catID and catSelect...

This a completely wrong way to handle the data.

What you want is 2 tables and a resolving table:

newproject
category

newproject_category
this table will likely have only 2 columns:
newproject_id
and category_id

That's basic data design.

vaaaska

I think what you are trying to do is perfectly ok. But I think you might be in over your head so I would suggest that you do some reading.

However, this might be a better way dot what you want without so many queries in the loop:

$q = mysql_query ("SELECT catSelect,and,the,other,fields,you,need
FROM category,newproject_data WHERE
(category.catID IN (" .implode(',', $catID2). "))
AND category.catID=newproject_data.catID")
or die ('Error!');
$rs = mysql_fetch_array($q);

Then, you can loop out your data from here. I haven't tested this or anything so somebody may come along and make a correction but this is basically how I use field information to access more than one category or group or whatever.

Voia!

Dillenger

how would i loop out the data in an array? not sure how to do that?