PHP + mySQL table joins, lookups, and parsing. Oh My!

programming
1

Hello all! First time poster, long time troller.

I feel like this little project I’m working on is taking me from the minors into the pros. Or at least the semi-pros.

I have a table (res_table) that is about 200 columns wide. One of these columns is named “feature_lk”, and it consists of a string of numbers which are “|” delimited. The numbers stand for feature catagories which reside in another table named “features”

Thanks to this thread: http://www.kirupa.com/forum/showthread.php?t=224203 I figured out how to parse the features out!

No my problem is how to look them up? I feel like I either need to join my two tables, but I’m not sure how, or I need to do a another select query for each of the features that I parse… This is what I have to far (removed connection strings for posting purposes)

<?php
$sql = ("SELECT * FROM res_table");
$result = mysql_query($sql);

while($row = mysql_fetch_array($result))
{
	$feature_string = $row['features_lk'];
	$features = explode( '|', $feature_string );

	foreach( $features as $feature ) {
	    $feature = trim( $feature );
	    echo $feature.': ';

	    $sql2 = "SELECT * from features where features.feature_id like $feature";
	    $result2 = mysql_query($sql2);
	    while ($row2 = mysql_fetch_array($result2))
	    {
	    	$feat_desc = $row2['feature_description']; //this is another column in the features table
	    	echo $feat_desc . '<br>';
	    }
	}
	echo '<br>';
}
?>

SO that works OK because when I run it, i’ll get about results that look like this:

13: None
62: Water Softener - Rented
71: Full
168: Barn
222: Storage Shed
226: Walkout
309: Detached
347: 2 Story
384: Attic Storage
439: Laundry Hook Up
466: Rural
476: Trees
512: School Bus
562: Mud Room
563: Pantry
2273: Septic Tank
643: Private Well

My question is: is there a better way to do this? There are about 10k rows in the main res_table with only a couple hundred hits, you can see that the number select statements performed grow LARGE in no time at all.

I’m sure this is PHP + MySQL 101 stuff, but I’m just a beginner so any ideas? Thanks in advance.