NYCPHP Meetup

[Brian Dailey]

This looks like what I'll ultimately be doing. I played around with some 
SQL queries and what I ended up with was similar to this - joining the 
table for each data value that I wanted to look up (and since I expect a 
matching data value, I don't even have to LEFT JOIN everything, which 
speeds it up significantly).

Thanks for all of your help and suggestions!

Mark Armendariz wrote:
>  > -----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
> 
> _______________________________________________
> 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
> 
> 

-- 

Thanks!
- Brian Dailey
Software Developer
New York, NY
www.dailytechnology.net
-------------- next part --------------
A non-text attachment was scrubbed...
Name: support.vcf
Type: text/x-vcard
Size: 264 bytes
Desc: not available
URL: <http://lists.nyphp.org/pipermail/talk/attachments/20070515/8f85dcad/attachment.vcf>