NYCPHP Meetup

[Mark Armendariz]

 > -----Original Message-----
> [mailto:talk-bounces at lists.nyphp.org] 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 ('mark at example.com');
INSERT INTO data (data_user) VALUES ('brian at example.com');
INSERT INTO data (data_user) VALUES ('steve at example.com');

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 | mark at example.com  | Mark  | Brooklyn    | NY    |
|       2 | brian at example.com | Brian | New York    | NY    |
|       3 | steve at example.com | 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 | mark at example.com  | Mark  | Brooklyn | NY    |
|       2 | brian at example.com | 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 | mark at example.com | 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 | mark at example.com | 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