From: Philip Mak <[EMAIL PROTECTED]>
> Is there a good way that I can store an array in a single row using MySQL?
You can simply delimit the data some how. Perhaps with a pipe (|).
> I was taught that under a clean relational database design, arrays should
> be stored one element per row.
Good teaching. Skipping this misses the point of a "relational" database.
> However, this makes retrieving the entire
> array less practical, especially when I have an array of arrays.
That's OK, all you need is a JOIN or two. Keep reading...
> I've thought about storing an array of words as a VARCHAR() string, and
> using a FULLTEXT index to search through it, but I'm concerned about
> efficiency.
Good point, quite inefficient.
> What should I do?
Let's say that you've got a table with purchase orders. Each purchase order will have
one or more products. We want to see all of the purchase orders and all of the
products in each order.
Some setup:
Table: purchase_order
Coumns: id, cust_id
Table: po_item
Columns: po_id, product_id
If you run a join like this:
SELECT po.*, prod.*
FROM purchase_order po, po_item prod
WHERE po.id = prod.po_id
You would get results something like this:
+----+---------+-------+------------+
| id | cust_id | po_id | product_id |
+----+---------+-------+------------+
| 1 | 1 | 1 | 5 |
| 1 | 1 | 1 | 6 |
| 2 | 1 | 2 | 8 |
| 3 | 2 | 3 | 8 |
| 3 | 2 | 3 | 9 |
| 3 | 2 | 3 | 10 |
+----+---------+-------+------------+
Now just itterate over the records returned and load them into whatever you need.
If you wanted to do some type of searching, say on the product ID, you could do this:
SELECT po.*, prod.*
FROM purchase_order po, po_item prod
WHERE po.id = prod.po_id
AND product_id = 8
Does this help?
---
Rodney Broom
Programmer: Desert.Net
---------------------------------------------------------------------
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
