Hi Devs
I have a tricky question and I want to know if its possible to accomplish it
on mysql and how.
The following query:
SELECT
fields.id AS field_id,
data.user_id,
fields.type,
fields.name,
data.value,
entries.ip,
entries.date,
entries.user_agent
FROM
data,
fields,
entries,
forms
WHERE
data.field_id = fields.id AND
data.user_id = entries.id AND
forms.id = fields.form_id AND
forms.id = entries.form_id AND
forms.id = 1
ORDER BY
user_id, field_order
Returns this:
+----------+---------+------+--------+-----------+-----------+---------------------+------------------+
| field_id | user_id | type | name | value | ip |
date | user_agent |
+----------+---------+------+--------+-----------+-----------+---------------------+------------------+
| 2 | 1 | text | RUT | 17881 | 127.0.0.1 | 2009-03-08
14:27:51 | Unit Check |
| 1 | 2 | text | Name | felipe | 127.0.0.2 | 2009-03-08
14:28:35 | Mozilla |
| 2 | 2 | text | RUT | 456465789 | 127.0.0.2 | 2009-03-08
14:28:35 | Mozilla |
+----------+---------+------+--------+-----------+-----------+---------------------+------------------+
And I want to display this by field_id and user_id like this:
user_id RUT Name
1 17881
2 456465789 felipe
I know that if would be just one reference would be easy to do (by doing
IF(field_id=X,value,'')), I've also found the solution by application side
(by parsing columns first, rows later),
but it comes to troubles when I add a new fieldID or when a fieldID for that
userID its not found.
Any idea?
Thanks,
Rod
