> 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 | [EMAIL PROTECTED] | 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 | [EMAIL PROTECTED] | Mark | Brooklyn | NY |
| 2 | [EMAIL PROTECTED] | Brian | New York | NY |
| 3 | [EMAIL PROTECTED] | Steve | Jersey City | NJ |
| 3 | [EMAIL PROTECTED] | 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
_______________________________________________
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
