Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

WWWXXXAPC, docmd.close 2585, WWWXXXDOCO, nu vot, dhcpd lease file "binding state", WWWXXXDOCO, how to setup procmail to process html2text, how to setup procmail html2text, WWWXXXAPC., XXXCNZZZ

Links

XODOX
Impressum

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

Posted on 2010-08-13 00:08:19 by Madan Thapa

--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--

Report this message

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

Posted on 2010-08-13 05:40:44 by Travis Ard

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

Report this message

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

Posted on 2010-08-13 08:37:07 by Madan Thapa

--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 <travis_ard@hotmail.com> 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--

Report this message