NYCPHP Meetup

[Mark Armendariz]

> What if a data record is missing some field values? Or has 
> more than one value stored for some field name?

Interesting points... For missing data, you can make the field nullable...

// set field_data to NULLABLE
ALTER TABLE data_fields CHANGE field_data field_data VARCHAR( 100 ) NULL
DEFAULT NULL;

// remove Steve's state
DELETE FROM data_fields WHERE data_id = 3 AND field_data = 'NJ';

// using People without States
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 ISNULL(fState.field_data) = 1;

// output
+---------+-------------------+-------+-------------+-------+
| data_id | data_user         | Name  | City        | State |
+---------+-------------------+-------+-------------+-------+
|       3 | steve at example.com | Steve | Jersey City | NULL  |
+---------+-------------------+-------+-------------+-------+

// as for people with multi data per field, you end up with something like
this:
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';
			  
+---------+-------------------+-------+-------------+-------+
| 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    |
|       3 | steve at example.com | Steve | Jersey City | NY    |
+---------+-------------------+-------+-------------+-------+

// Which might not be pretty, but could be ok, depending on your needs...
SELECT 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 fState ON d.data_id = fState.data_id AND
fState.field_name = 'state'
WHERE fName.field_data = 'Steve';

// all of steve's states
+-------+
| State |
+-------+
| NJ    |
| NY    |
+-------+

// Or Set a Unique Index on the parent field and field_name for one record
per field per user
ALTER TABLE data_fields ADD UNIQUE data_field (data_id, field_name);

// now try inserting
INSERT INTO data_fields (data_id, field_name, field_data) VALUES (3,
'state', 'NJ'); // works once because we just removed it
INSERT INTO data_fields (data_id, field_name, field_data) VALUES (3,
'state', 'NJ');
ERROR 1062 (00000): Duplicate entry '3-state' for key 2


Mark