At 17:03, 19990827, Wayne Spivak wrote:
>Have a database that allows records to be placed into four different
>categories of a possible 100 catagores. This is done by haveing four
>fields, Area0, 1, 2 & 3.
>I want to be able to tell my users how many in total, whether the info is
>stored in Area1 or Area3, how many listing there are per category.
>Tried summing Count(area0+area1....) and that didn't work.
>Want to set variables and manually count, but couldn't figure out how you
>can do this in MySQL.
You could normalize your data, and it would become trivial (but it might
not be the best solution for you).
CREATE TABLE barzolphats (id INT UNSIGNED NOT NULL AUTO_INCREMENT,
title CHAR(20) NOT NULL,
KEY title_k (title),
PRIMARY KEY (id));
CREATE TABLE barzo_areas (barzo_id INT UNSIGNED NOT NULL,
area CHAR(5) NOT NULL,
/* these could be made a primary key if you like */
KEY barzo_id_k (barzo_id),
KEY area_k (area));
Now you just insert rows into barzo_areas for each area that a barzolphat
is in. You can have 4 areas, or more. If you must have only 4, you can
check that in your app.
Other ideas: instead of using a CHAR... for the area, you could:
- use an integral ID that points to an areas table which has 100 rows
(one for each area)
- use an ENUM that has 100 values
- axe the barzo_areas table altogether and use a SET inside your