Create another table with an id column and a code column, and for each code
insert a column with the id of the row in the original database and the
code. Then do an SQL join to search. For example:
mysql> create table example (id int(11) NOT NULL auto_increment, blah
varchar(100), PRIMARY KEY(id));
Query OK, 0 rows affected (0.02 sec)
mysql> create table codes (id int(11) NOT NULL, code int(11) NOT NULL, KEY
code(code));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into example (blah) values ('hello');
Query OK, 1 row affected (0.01 sec)
mysql> insert into codes values (last_insert_id(), 1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into codes values (last_insert_id(), 3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into example (blah) values ('hello world');
Query OK, 1 row affected (0.00 sec)
mysql> insert into codes values (last_insert_id(), 2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into codes values (last_insert_id(), 4);
Query OK, 1 row affected (0.00 sec)
mysql> select * from codes;
+----+------+
| id | code |
+----+------+
| 1 | 1 |
| 1 | 3 |
| 2 | 2 |
| 2 | 4 |
+----+------+
4 rows in set (0.00 sec)
mysql> select * from example;
+----+-------------+
| id | blah |
+----+-------------+
| 1 | hello |
| 2 | hello world |
+----+-------------+
2 rows in set (0.00 sec)
mysql> select example.* from example, codes where example.id = codes.id and
code
s.code = 3;
+----+-------+
| id | blah |
+----+-------+
| 1 | hello |
+----+-------+
1 row in set (0.00 sec)
mysql> select example.* from example, codes where example.id = codes.id and
code
s.code = 4;
+----+-------------+
| id | blah |
+----+-------------+
| 2 | hello world |
+----+-------------+
1 row in set (0.00 sec)
> Perhaps this is a common problem:
> I would like to have a database field that contains multiple code numbers
> and later search for the presence of one of the codes. What I have come up
> with so far is using a "&" delimter between the code numbers to
> end up with
> a field like:
> 12&5&34
>
> When searching I'm using:
> REGEXP
> '^$search_category$|^$search_category&|&$search_category&|&$search
> _category$
> '
>
> This seems to work fine, however I was wondering if there is a
> better way...
>
> Any advice would be greatly appreciated.
>
> - anatole
---------------------------------------------------------------------
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
