:)
Lang Sharpe wrote:
Having a Standard only, Deluxe only and Both will lead to problems in the
future. (i.e. what if you add in a Basic feature set?) What I would do is
1. Get rid of the "Both" row in feature sets.
2. Have another table called "feature_set_features" or something better. The
table has two columns, The "Feature id" and the "Feature set ID". If a
feature is in one feature set, there in one row in the table. if a Feature
is in Both feature sets, then there are two rows in this table, one for
each feature set. 3. To query this, join features and feature_set_features on feature_id and
use where feature_set_id = 'standard' or whatever if is.
Hopefully I haven't confused you too much.
Lang
Ali Van Doren wrote:
Hello, 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
