project/extract similar items type, inside a table field as if afield itself

--001485ebea30973b6a048da79a26
Content-Type: text/plain; charset=ISO-8859-1

Hi,

There is a mysql table ( wordpress) as following, called wp_usermeta, where
field meta_key holds zip_code , first_name, last_name inside it ( should
have been separate fields to extract data easily)


mysql> desc wp_usermeta;
+------------+---------------------+------+-----+---------+- ---------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+- ---------------+
| umeta_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| user_id | bigint(20) unsigned | NO | MUL | 0 | |
| meta_key | varchar(255) | YES | MUL | NULL | |
| meta_value | longtext | YES | | NULL | |
+------------+---------------------+------+-----+---------+- ---------------+
4 rows in set (0.00 sec)



mysql> SELECT * FROM `wp_usermeta` WHERE `meta_key` LIKE 'zip_code' ORDER BY
user_id limit 1,3;
+----------+---------+----------+------------+
| umeta_id | user_id | meta_key | meta_value |
+----------+---------+----------+------------+
| 278 | 15 | zip_code | 32501 |
| 297 | 16 | zip_code | 32501 |
| 316 | 17 | zip_code | 32504 |
+----------+---------+----------+------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM `wp_usermeta` WHERE `meta_key` LIKE 'first_name' ORDER
BY user_id limit 1,3;
+----------+---------+------------+------------+
| umeta_id | user_id | meta_key | meta_value |
+----------+---------+------------+------------+
| 280 | 16 | first_name | Jesxxdx |
| 299 | 17 | first_name | maerer |
| 318 | 18 | first_name | Liddd |
+----------+---------+------------+------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM `wp_usermeta` WHERE `meta_key` LIKE 'last_name' ORDER
BY user_id limit 1,3;
+----------+---------+-----------+------------+
| umeta_id | user_id | meta_key | meta_value |
+----------+---------+-----------+------------+
| 281 | 16 | last_name | Oweccc |
| 300 | 17 | last_name | magfffff |
| 319 | 18 | last_name | Pedfs |
+----------+---------+-----------+------------+
3 rows in set (0.01 sec)

mysql>







Is it possible to exctract each items, zip_code , first_name, last_name
inside the field meta_key separately and list them as if each item is a
field through a single mysql query. Else, it seems we will have to extract
each file and then import that to a new table with a each of the field
created inside that table.




Result something like:



first_name last_name zip_code

Jesxxdx Oweccc 32501
maerer magfffff 32501
Liddd Pedfs 32504






Thakns

--001485ebea30973b6a048da79a26--

RE: project/extract similar items type, inside a table field as if a field itself

Maybe something like this:

select
user_id
,max(if(meta_key = 'zip_code', meta_value, null)) as zip_code
,max(if(meta_key = 'first_name', meta_value, null)) as first_name
,max(if(meta_key = 'last_name', meta_value, null)) as last_name
from wp_usermeta
group by user_id;

-Travis

-----Original Message-----
From: MadTh [mailto:madan.feedback@gmail.com]
Sent: Thursday, August 12, 2010 4:08 PM
To: mysql@lists.mysql.com
Subject: project/extract similar items type, inside a table field as if a
field itself

Hi,

There is a mysql table ( wordpress) as following, called wp_usermeta, where
field meta_key holds zip_code , first_name, last_name inside it ( should
have been separate fields to extract data easily)


mysql> desc wp_usermeta;
+------------+---------------------+------+-----+---------+- ---------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+- ---------------+
| umeta_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| user_id | bigint(20) unsigned | NO | MUL | 0 | |
| meta_key | varchar(255) | YES | MUL | NULL | |
| meta_value | longtext | YES | | NULL | |
+------------+---------------------+------+-----+---------+- ---------------+
4 rows in set (0.00 sec)



mysql> SELECT * FROM `wp_usermeta` WHERE `meta_key` LIKE 'zip_code' ORDER BY
user_id limit 1,3;
+----------+---------+----------+------------+
| umeta_id | user_id | meta_key | meta_value |
+----------+---------+----------+------------+
| 278 | 15 | zip_code | 32501 |
| 297 | 16 | zip_code | 32501 |
| 316 | 17 | zip_code | 32504 |
+----------+---------+----------+------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM `wp_usermeta` WHERE `meta_key` LIKE 'first_name' ORDER
BY user_id limit 1,3;
+----------+---------+------------+------------+
| umeta_id | user_id | meta_key | meta_value |
+----------+---------+------------+------------+
| 280 | 16 | first_name | Jesxxdx |
| 299 | 17 | first_name | maerer |
| 318 | 18 | first_name | Liddd |
+----------+---------+------------+------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM `wp_usermeta` WHERE `meta_key` LIKE 'last_name' ORDER
BY user_id limit 1,3;
+----------+---------+-----------+------------+
| umeta_id | user_id | meta_key | meta_value |
+----------+---------+-----------+------------+
| 281 | 16 | last_name | Oweccc |
| 300 | 17 | last_name | magfffff |
| 319 | 18 | last_name | Pedfs |
+----------+---------+-----------+------------+
3 rows in set (0.01 sec)

mysql>







Is it possible to exctract each items, zip_code , first_name, last_name
inside the field meta_key separately and list them as if each item is a
field through a single mysql query. Else, it seems we will have to extract
each file and then import that to a new table with a each of the field
created inside that table.




Result something like:



first_name last_name zip_code

Jesxxdx Oweccc 32501
maerer magfffff 32501
Liddd Pedfs 32504






Thakns


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: project/extract similar items type, inside a table field as if afield itself

--001636920b8b2e8152048daeb694
Content-Type: text/plain; charset=ISO-8859-1

Hi Travis,

Super



Thanks a ton





On Fri, Aug 13, 2010 at 5:40 AM, Travis Ard wrote:

> Maybe something like this:
>
> select
> user_id
> ,max(if(meta_key = 'zip_code', meta_value, null)) as zip_code
> ,max(if(meta_key = 'first_name', meta_value, null)) as first_name
> ,max(if(meta_key = 'last_name', meta_value, null)) as last_name
> from wp_usermeta
> group by user_id;
>
> -Travis
>
> -----Original Message-----
> From: MadTh [mailto:madan.feedback@gmail.com]
> Sent: Thursday, August 12, 2010 4:08 PM
> To: mysql@lists.mysql.com
> Subject: project/extract similar items type, inside a table field as if a
> field itself
>
> Hi,
>
> There is a mysql table ( wordpress) as following, called wp_usermeta, where
> field meta_key holds zip_code , first_name, last_name inside it ( should
> have been separate fields to extract data easily)
>
>
> mysql> desc wp_usermeta;
>
> +------------+---------------------+------+-----+---------+- ---------------+
> | Field | Type | Null | Key | Default | Extra
> |
>
> +------------+---------------------+------+-----+---------+- ---------------+
> | umeta_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment
> |
> | user_id | bigint(20) unsigned | NO | MUL | 0 |
> |
> | meta_key | varchar(255) | YES | MUL | NULL |
> |
> | meta_value | longtext | YES | | NULL |
> |
>
> +------------+---------------------+------+-----+---------+- ---------------+
> 4 rows in set (0.00 sec)
>
>
>
> mysql> SELECT * FROM `wp_usermeta` WHERE `meta_key` LIKE 'zip_code' ORDER
> BY
> user_id limit 1,3;
> +----------+---------+----------+------------+
> | umeta_id | user_id | meta_key | meta_value |
> +----------+---------+----------+------------+
> | 278 | 15 | zip_code | 32501 |
> | 297 | 16 | zip_code | 32501 |
> | 316 | 17 | zip_code | 32504 |
> +----------+---------+----------+------------+
> 3 rows in set (0.00 sec)
>
> mysql> SELECT * FROM `wp_usermeta` WHERE `meta_key` LIKE 'first_name' ORDER
> BY user_id limit 1,3;
> +----------+---------+------------+------------+
> | umeta_id | user_id | meta_key | meta_value |
> +----------+---------+------------+------------+
> | 280 | 16 | first_name | Jesxxdx |
> | 299 | 17 | first_name | maerer |
> | 318 | 18 | first_name | Liddd |
> +----------+---------+------------+------------+
> 3 rows in set (0.00 sec)
>
> mysql> SELECT * FROM `wp_usermeta` WHERE `meta_key` LIKE 'last_name' ORDER
> BY user_id limit 1,3;
> +----------+---------+-----------+------------+
> | umeta_id | user_id | meta_key | meta_value |
> +----------+---------+-----------+------------+
> | 281 | 16 | last_name | Oweccc |
> | 300 | 17 | last_name | magfffff |
> | 319 | 18 | last_name | Pedfs |
> +----------+---------+-----------+------------+
> 3 rows in set (0.01 sec)
>
> mysql>
>
>
>
>
>
>
>
> Is it possible to exctract each items, zip_code , first_name, last_name
> inside the field meta_key separately and list them as if each item is a
> field through a single mysql query. Else, it seems we will have to extract
> each file and then import that to a new table with a each of the field
> created inside that table.
>
>
>
>
> Result something like:
>
>
>
> first_name last_name zip_code
>
> Jesxxdx Oweccc 32501
> maerer magfffff 32501
> Liddd Pedfs 32504
>
>
>
>
>
>
> Thakns
>
>

--001636920b8b2e8152048daeb694--