Re: project/extract similar items type, inside a table field as if a field itself
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 | magf | | 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_namezip_code JesxxdxOweccc 32501 maerer magf 32501 Liddd Pedfs 32504 Thakns
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 | magf | | 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_namezip_code JesxxdxOweccc 32501 maerer magf 32501 Liddd Pedfs 32504 Thakns
list rows with no recent updates
Hi, I ran a update command on around 2700 rows inside a mysql database table which has around 3000 table rows to change the ( say) price of each item ( with unique ID. unique product code). like: mysql UPDATE tbl_xyz set listprice='9.45' where prod_id='3069' and prod_code='a0071'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 How can I list rows with no recent updates ( or the once where the above updates were not done) or say with no updates in last 2 hours? Thank you.
Re: list rows with no recent updates
Hi, Thank you all for your prompt response. Unfortunately timestamp file isn;t there, so I will find some other way to do it. Seems timestamp is a valuable field ( unless you want to save resource on generating timestamps on a very busy table). Thanks
