Hi Travis, Super
Thanks a ton On Fri, Aug 13, 2010 at 5:40 AM, Travis Ard <travis_...@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.feedb...@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 > >
