Joining 3 tables, making the rows columns?

[tomandhannah]

I have three tables that I am trying to join to get the correct output:

users:
id|name|email
20|tom|[e-mail]

fields:
id|name
45|Address
46|City
47|State
48|Zip

field_values:
id|user_id|field_id|value
1|20|45|123 Spring Lane
2|20|46|New York
3|20|47|NY
4|20|48|55555

I need to get the data from the database like this:
userid|name|email|address|city|state|zip

So far I can get the data I need with this query:
SELECT user.id, user.name, user.email, field.name AS col, value.value FROM fields AS field LEFT JOIN field_values AS value ON field.id=value.field_id AND value.user_id='20' LEFT JOIN users AS user ON user.id=value.user_id

Which results in:
id|name|email|col|value
20|tom|[e-mail]|address|123 Spring Lane
20|tom|[e-mail]|city|New York
20|tom|[e-mail]|state|NY
20|tom|[e-mail]|zip|55555

But I need:
20|tom|[e-mail]|123 Spring Lane|New York|NY|55555

So now I need to get those rows to become columns! But I am at the end of my rope trying different things. Does anyone know how to do this?

[CoderDan]

using your existing data structures, this will work:

<?php
$query =
	"SELECT".
	"users.id,".
	"users.name,".
	"users.email,".
	"fields.name,".
	"field_values.value".
	"FROM field_values".
	"left join users on users.id = field_values.user_id".
	"left join fields on fields.id = field_values.field_id".
	"where users.id = 20 "; // optional
$result = mysql_query( $query ) or die( mysql_error( ) );
$users = array();
while ( $data = mysql_fetch_assoc( $result ) )
{
	// next 2 lines only need to be run once, but with the data structure we just keep going over them: no harm, no foul.
	$users[$data['id']]['id'] = $data['id'];
	$users[$data['id']]['email'] = $data['email'];
	$users[$data['id']][$data['name']] = $data['value'];
}

$header = array();
$header_set = false;
$user_data = array();

foreach ( $users as $user_id => $data )
{
	foreach ( $data as $field => $value )
	{
		if ( !$header_set )
		{
			$header[] = $field; // again, a redundant run, only needs to be set once.
		}
		$user_data[$user_id][] = $value;
	}
	$header_set = true;
}

echo join( '|', $header )."\n";
foreach ( $user_data as $data )
{
	echo join( '|', $data )."\n";
}
?>

I'd reccomend using a different data structure, however.
If you are collecting a predefined set of data (name, address, city, state, zip) then why not just add those fields to the users table?

[djjjozsi]

id, id, id
its a bad idea in your field structure. In your results they will overwrite each other. Use aliases for these primary and foreign keys, or be creative with their names.

there is no need to collect the results in a new array.
Just watch the $data["id"], compare the previous value(if its available) with the actually row, if its changing add a new row.

[tomandhannah]

Thanks for your responses. Unfortunately, I cannot change the data structure. But they are dynamic anyway, so they cannot be simply added to the user table.

Yes, I know how to get the data the way I want it using PHP. What I am looking for is an SQL query that will give me the data in this format without using PHP. I know it is possible, as I have seen similar (but simpler) examples.

Does anyone know an SQL query solution?

This one is similar, but it only uses one table:
http://www.artfulsoftware.com/infotree/queries.php?&bw=1213#79

[djjjozsi]

a normal sql query makes this structure, but each details get into a new row. All you need to watch is the main table's primary key. if its chenging, lets draw a new table row. in other case build columns.

[Roger_Ramjet]

There is a way to do this in sql, its ugly but it will work.

To get the address as 1 row:

SELECT user_id, GROUP_CONCAT(a) AS Address, GROUP_CONCAT(c) AS City, GROUP_CONCAT(s) AS State, GROUP_CONCAT(z) AS ZIP
FROM
(SELECT user_id, value AS a, NULL AS c, NULL AS s, NULL AS z
FROM field_values WHERE field_id = 45
UNION
SELECT user_id, NULL, value, NULL, NULL
FROM field_values WHERE field_id = 46
UNION
SELECT user_id, NULL, NULL, value, NULL
FROM field_values WHERE field_id = 47
UNION
SELECT user_id, NULL, NULL, NULL, value
FROM field_values WHERE field_id = 48)
AS q
GROUP BY user_id

That will return the address for each user_id, integrate it with your other tables for the other data.