WordPress wp_usermeta listing problem

[norus]

In WordPress I need a listing generated from table wp_usermeta which shows following results per row per user_id :
user_id, city, first_name+ " "+lastname


This is an example of the data in table wp_usermeta.

126 city Utrecht
126 last_name Huisman
126 first_name Fred
100 city Eibergen
100 last_name Van der Heide
100 first_name Toon
90 city Hengelo
90 last_name Klaver
90 first_name Gezien

What I need is following result:
126 Utrecht Fred Huisman
100 Eibergen Toon Van der Heide
90 Hengelo Gezien Klaver

Below script is wors. But it give you an idea of the table _ fields:

SELECT wp_users.ID, wp_usermeta.meta_key , wp_usermeta.meta_value
FROM wp_users INNER JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id
WHERE
wp_usermeta.meta_key = "city"
OR
wp_usermeta.meta_key = "first_name" + "last_name"
OR
(wp_usermeta.meta_key = "lastwill_public" AND wp_usermeta.meta_value = "1")
AND wp_usermeta.meta_value is null
ORDER BY wp_users.ID DESC

 

Who can help me  getting the result.
In advance thanks!

[Barand]

Something like this will do it. (It isn't pretty, but then neither is the wp_usermeta table).

SELECT u.id
 , firstname.meta_value
 , lastname.meta_value
 , city.meta_value
FROM wp_users u
INNER JOIN
	(
	SELECT u.id
	  ,	m.meta_value
	FROM wp_users u
	LEFT JOIN wp_usermeta m ON u.ID=m.user_id
		AND m.meta_key = 'firstname'
	) firstname USING (id)
INNER JOIN
	(
	SELECT u.id
	  ,	m.meta_value
	FROM wp_users u
	LEFT JOIN wp_usermeta m ON u.ID=m.user_id
		AND m.meta_key = 'lastname'
	) lastname  USING (id)
INNER JOIN
	(
	SELECT u.id
	  ,	m.meta_value
	FROM wp_users u
	LEFT JOIN wp_usermeta m ON u.ID=m.user_id
		AND m.meta_key = 'city'
	) city USING (id)
ORDER BY u.id;

[norus]

I solved this with creating an extra table for addresses. The WP-usermeta table sucks. The idea of Guru was nice but not functional.

WIth creating new table the insert + update is very easy and to get the data from the table is also better.

Thanks for the reply.