I think I acompplish it!
Merge that query into a view called report_1 and then:
(1) SELECT user_id, MAX(IF(name='RUT',value,NULL)) AS 'Rut',
MAX(IF(name='Name',value,NULL)) AS 'Name' FROM report_1 GROUP BY user_id;
+-+---++
| user_id | Rut | Name |
+-+---++
| 1 | 170332881 | NULL |
| 2 | 456465789 | felipe |
+-+---++
Then:
INSERT INTO data(user_id,field_id,value) values (1, 1, 'Rodrigo');
And then (1):
+-+---+-+
| user_id | Rut | Name|
+-+---+-+
| 1 | 170332881 | Rodrigo |
| 2 | 456465789 | felipe |
+-+---+-+
:)
2009/4/5 Rodrigo Aliste P. rali...@gmail.com
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_idRUT 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
--
Rodrigo
