I am pretty new to PHP and MySQL, and am struggling with this particular page I would like to create.
I am building a page which lists categories of house features (e.g. concrete, framing, foundation, etc.) and then the particular features that fall into those categories (e.g. the concrete items are driveway, garage floor, sidewalk, basement; the framing items include manufactured floor joist, 1/2" OSB on roof, etc.) The tricky part is that there are two lists of features I need to produce: standard and deluxe. Some features span both types, some are particular to only one feature list.
I have created 3 tables to handle this:
- the feature_sets table has three items: both(1), standard(2) only and deluxe(3) only
- the feature_categories table holds the 19 categories of features (concrete, framing, etc.)
- the features table holds all of the features (63 total.)
All tables have primary keys, and the features table has 2 foreign keys corresponding to featureset and the featurecategory.
What I would like to be able to do is to have the category appear, and then list the corresponding features under it, depending on whether it's the deluxe of standard feature list. Here's my code:
<?php
require_once ('/usr/home/fahomes/mysql_connect.php');
$query = "SELECT category, feature_description FROM features as f, feature_categories as fc WHERE f.feature_category_id=fc.feature_category_id AND f.featureset_id = '1' OR f.featureset_id = '3' ORDER BY f.feature_category_id ASC";
$result = @mysql_query ($query);
if ($result) {
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
echo "<tr>
<td align=\"left\">$row[0]</td>
<td align=\"left\"><ul><li>$row[1]</ul></td>
</tr>\n";
}
mysql_free_result ($result);
} else {
echo '<p>The features could not be displayed due to a system error. Please contact the <a href=\"mailto:webster@xxxxxxxxxxxxxxxxxxxxxx\";>webmaster</a>.</p><p>' . mysql_error() . '</p>';
}
mysql_close();
?>
What I am getting is initially it's creating a row for each category/feature pair, so the categories that have more than one feature listed appear in more than one row. It works fine until record 34 (id 33) when it starts puking out that feature for 10 rows then the next feature for 1 row, then it jumps back to the feature 33 for another 12 rows, this time cycling through the categories. It's quite bizarre:
http://roku.pair.com/fahomes/test/homes_dlx_features.php
So, I actually have two questions:
1) Does anyone know why my query is misbehaving so badly? When I copy and paste the query into MyPHPAdmin, it produces the same results, so I know it's not the php. On thing I do notice is if I change the query slightly (so that it looks for records "WHERE f.feature_category_id=fc.feature_category_id AND f.featureset_id = '1' OR '3'" instead of "f.featureset_id = '1' OR f.featureset_id = '3'" it produces equally messed up, but different results.
2) Is there a way that I can display the data the way I want to display it (list the category once, then corresponding features underneath, before going to the next category)? I would imagine it's a WHILE loop that's inside another WHILE loop -- I am just not sure how to go about it, and haven't been able to track down anything in my books.
Thanks for any advice!
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
