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
