James Carrier wrote:
>
> Hi Amer
>
> Thanks for replying - I wonder if you could clear up a point for me.
> I'm going to have many, many possible multi values - too many for a SET or
> ENUM column as some other people have suggested.
> I'm happy using the following tables:
>
> parts (partid int, partname varchar(255))
> cats (catid int, catname varchar(255))
> parts_cats (id int, partid int, catid int)
>
> To track which categories are assigned to which parts. I can use the
> following query to return parts and associated categories:
>
> SELECT partid,catid,partname,catname FROM parts_cats
> LEFT JOIN parts ON parts_cats.partid=parts.partid
> LEFT JOIN cats ON parts_cats.catid=cats.catid
>
> However, as I won't know how many categories are assigned to a given part,
> how can I:
So from this are you saying that a given part may have multiple
categories? Can a category contain multiple parts? (many to many). That
is usually an indicator to split up the data into another table.
I suggest you try something like this:
1) add another field to parts (catid int) which reflects cats.catid
2) move the cats.catid to the new column in parts
3) delete parts_cats - it's now redundant
>
> a) Form a query that only returns a list of parts that match a specifc set
> of categories (e.g. 1=>CatA' AND 5=>'CatE' AND 26=>'CatZ' only)
>
> b) Know how many unique records I'm dealing with. Using the SQL above, if a
> part has, say, 3 categories then 3 rows would be returned. 4 categories and
> 4 rows are returned, etc. - how can I tell how big my result set is so that
> I can page through it using LIMIT?
Here's a dummy db I set up to play with this:
mysql> show tables;
+-------------------+
| Tables_in_carrier |
+-------------------+
| cats |
| parts |
+-------------------+
2 rows in set (0.00 sec)
mysql> describe cats;
+----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| cat_id | int(10) unsigned | YES | | NULL | |
| cat_name | varchar(255) | YES | | NULL | |
+----------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> describe parts;
+-----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| part_id | int(10) unsigned | YES | | NULL | |
| part_name | varchar(255) | YES | | NULL | |
| cat_id | int(10) unsigned | YES | | NULL | |
+-----------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select * from cats;
+--------+------------+
| cat_id | cat_name |
+--------+------------+
| 101 | oneohone |
| 102 | oneohtwo |
| 103 | oneohthree |
| 104 | oneohfour |
+--------+------------+
4 rows in set (0.00 sec)
mysql> select * from parts order by part_id;
+---------+-----------+--------+
| part_id | part_name | cat_id |
+---------+-----------+--------+
| 1 | one | 101 |
| 1 | one | 102 |
| 2 | two | 102 |
| 2 | two | 104 |
| 3 | three | 103 |
| 3 | three | 104 |
| 4 | four | 103 |
| 5 | five | 101 |
| 6 | six | 102 |
+---------+-----------+--------+
9 rows in set (0.00 sec)
mysql> select * from parts
-> where cat_id=101 or cat_id=104
-> order by part_id;
+---------+-----------+--------+
| part_id | part_name | cat_id |
+---------+-----------+--------+
| 1 | one | 101 |
| 2 | two | 104 |
| 3 | three | 104 |
| 5 | five | 101 |
+---------+-----------+--------+
4 rows in set (0.00 sec)
mysql> select * from parts
-> where cat_id=101 or cat_id=102;
+---------+-----------+--------+
| part_id | part_name | cat_id |
+---------+-----------+--------+
| 1 | one | 101 |
| 5 | five | 101 |
| 2 | two | 102 |
| 6 | six | 102 |
| 1 | one | 102 |
+---------+-----------+--------+
5 rows in set (0.00 sec)
mysql> select * from parts
-> where part_id=3;
+---------+-----------+--------+
| part_id | part_name | cat_id |
+---------+-----------+--------+
| 3 | three | 103 |
| 3 | three | 104 |
+---------+-----------+--------+
2 rows in set (0.00 sec)
mysql> select * from parts
-> where part_id=1;
+---------+-----------+--------+
| part_id | part_name | cat_id |
+---------+-----------+--------+
| 1 | one | 101 |
| 1 | one | 102 |
+---------+-----------+--------+
2 rows in set (0.00 sec)
mysql> select * from parts
-> where part_id=4;
+---------+-----------+--------+
| part_id | part_name | cat_id |
+---------+-----------+--------+
| 4 | four | 103 |
+---------+-----------+--------+
1 row in set (0.00 sec)
In PHP, to get the number of rows affected you can do something like
this ...
$query=("SELECT Artist
FROM Artists
WHERE Artist LIKE \"%$InArtist%\" ");
$result=mysql_query($query);
if (!$result)
{
die ("query failed.");
}
$ArtistCount=mysql_num_rows($result);
Hope this helps somewhat.
--
Amer Neely, Softouch Information Services
W: www.softouch.on.ca
E: [EMAIL PROTECTED]
V: 519.438.5887
Perl | PHP | MySQL | CGI programming for shopping carts, data entry
forms.
"We make web sites work!"
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
