> -----Original Message-----
> [mailto:[EMAIL PROTECTED] On Behalf Of Brian Dailey
> Sent: Tuesday, May 15, 2007 10:45 AM
> Another way I've seen it handled is to have a
> header table and a detail table that works something like this:
>
> table: documents (id, date, etc)
> table: documentdetails (documentid, fieldname, fieldvalue)
>
> All of the form values were stored in a fieldname=fieldvalue
> format inside the table. This worked nicely until you
> attempted to run reports on it - you couldn't easily combine
> data since it all existed in different table rows.
Reports aren't too difficult. It depends on how in-depth your reports get.
Essentially you end up joining the data table for every field. I haven't
done this in quite some time, but here's the idea of how you run reports
when using field-value tables (tested in mysql 4.0.23)
CREATE TABLE data (
data_id TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
data_user VARCHAR(100)
)
CREATE TABLE data_fields (
field_id MEDIUMINT(6) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
data_id TINYINT(3) UNSIGNED NOT NULL,
field_name VARCHAR(20),
field_data VARCHAR(100)
)
INSERT INTO data (data_user) VALUES ('[EMAIL PROTECTED]');
INSERT INTO data (data_user) VALUES ('[EMAIL PROTECTED]');
INSERT INTO data (data_user) VALUES ('[EMAIL PROTECTED]');
INSERT INTO data_fields (data_id, field_name, field_data) VALUES (1, 'name',
'Mark');
INSERT INTO data_fields (data_id, field_name, field_data) VALUES (1, 'city',
'Brooklyn');
INSERT INTO data_fields (data_id, field_name, field_data) VALUES (1,
'state', 'NY');
INSERT INTO data_fields (data_id, field_name, field_data) VALUES (2, 'name',
'Brian');
INSERT INTO data_fields (data_id, field_name, field_data) VALUES (2, 'city',
'New York');
INSERT INTO data_fields (data_id, field_name, field_data) VALUES (2,
'state', 'NY');
INSERT INTO data_fields (data_id, field_name, field_data) VALUES (3, 'name',
'Steve');
INSERT INTO data_fields (data_id, field_name, field_data) VALUES (3, 'city',
'Jersey City');
INSERT INTO data_fields (data_id, field_name, field_data) VALUES (3,
'state', 'NJ');
// All data with Name, City and State
SELECT d.data_id,
d.data_user,
fName.field_data AS Name,
fCity.field_data AS City,
fState.field_data AS State
FROM data d LEFT JOIN data_fields fName ON d.data_id = fName.data_id AND
fName.field_name = 'name'
LEFT JOIN data_fields fCity ON d.data_id = fCity.data_id AND
fCity.field_name = 'city'
LEFT JOIN data_fields fState ON d.data_id = fState.data_id AND
fState.field_name = 'state';
// output
+---------+-------------------+-------+-------------+-------+
| data_id | data_user | Name | City | State |
+---------+-------------------+-------+-------------+-------+
| 1 | [EMAIL PROTECTED] | Mark | Brooklyn | NY |
| 2 | [EMAIL PROTECTED] | Brian | New York | NY |
| 3 | [EMAIL PROTECTED] | Steve | Jersey City | NJ |
+---------+-------------------+-------+-------------+-------+
// data with Name, City, State in NY
SELECT d.data_id,
d.data_user,
fName.field_data AS Name,
fCity.field_data AS City,
fState.field_data AS State
FROM data d LEFT JOIN data_fields fName ON d.data_id = fName.data_id AND
fName.field_name = 'name'
LEFT JOIN data_fields fCity ON d.data_id = fCity.data_id AND
fCity.field_name = 'city'
LEFT JOIN data_fields fState ON d.data_id = fState.data_id AND
fState.field_name = 'state';
HAVING(State = 'NY');
// output
+---------+-------------------+-------+----------+-------+
| data_id | data_user | Name | City | State |
+---------+-------------------+-------+----------+-------+
| 1 | [EMAIL PROTECTED] | Mark | Brooklyn | NY |
| 2 | [EMAIL PROTECTED] | Brian | New York | NY |
+---------+-------------------+-------+----------+-------+
// data with Name, City, State in Brooklyn
SELECT d.data_id,
d.data_user,
fName.field_data AS Name,
fCity.field_data AS City,
fState.field_data AS State
FROM data d LEFT JOIN data_fields fName ON d.data_id = fName.data_id AND
fName.field_name = 'name'
LEFT JOIN data_fields fCity ON d.data_id = fCity.data_id AND
fCity.field_name = 'city'
LEFT JOIN data_fields fState ON d.data_id = fState.data_id AND
fState.field_name = 'state';
HAVING(City = 'Brooklyn');
// output
+---------+------------------+------+----------+-------+
| data_id | data_user | Name | City | State |
+---------+------------------+------+----------+-------+
| 1 | [EMAIL PROTECTED] | Mark | Brooklyn | NY |
+---------+------------------+------+----------+-------+
// using WHERE instead of HAVING
SELECT d.data_id,
d.data_user,
fName.field_data AS Name,
fCity.field_data AS City,
fState.field_data AS State
FROM data d LEFT JOIN data_fields fName ON d.data_id = fName.data_id AND
fName.field_name = 'name'
LEFT JOIN data_fields fCity ON d.data_id = fCity.data_id AND
fCity.field_name = 'city'
LEFT JOIN data_fields fState ON d.data_id = fState.data_id AND
fState.field_name = 'state';
WHERE fCity.field_data = 'Brooklyn'
AND fState.field_data = 'NY';
// output
+---------+------------------+------+----------+-------+
| data_id | data_user | Name | City | State |
+---------+------------------+------+----------+-------+
| 1 | [EMAIL PROTECTED] | Mark | Brooklyn | NY |
+---------+------------------+------+----------+-------+
// EXPLAIN output of the last statement ('having' is a bit less efficient)
+--------+--------+---------------+------------+---------+---------------+--
----+-------------+
| table | type | possible_keys | key | key_len | ref |
rows | Extra |
+--------+--------+---------------+------------+---------+---------------+--
----+-------------+
| fCity | ref | field_name | field_name | 120 | const,const |
1 | Using where |
| d | eq_ref | PRIMARY | PRIMARY | 1 | fCity.data_id |
1 | |
| fName | ref | field_name | field_name | 20 | const |
2 | Using where |
| fState | ref | field_name | field_name | 120 | const,const |
2 | Using where |
+--------+--------+---------------+------------+---------+---------------+--
----+-------------+
Hope that helps.
Good luck!!
Mark Armendariz
_______________________________________________
New York PHP Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk
NYPHPCon 2006 Presentations Online
http://www.nyphpcon.com
Show Your Participation in New York PHP
http://www.nyphp.org/show_participation.php
