SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',1),':',-1) AS fieldname1,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',2),':',-1) AS fieldvalue1,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',3),':',-1) AS fieldname2,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',4),':',-1) AS fieldvalue2,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',5),':',-1) AS fieldname3,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',6),':',-1) AS fieldvalue3,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',7),':',-1) AS fieldname4,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',8),':',-1) AS fieldvalue4,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',9),':',-1) AS fieldname5,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',10),':',-1) AS fieldvalue5
FROM table;
//##remove double quote
REPLACE( SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT meta_value FROM `wp_usermeta` WHERE meta_key = 'gender' AND user_id = wp_users.`ID` LIMIT 1 ),';',2),':',-1),'"','') AS fieldname2
// ###### Another sample
SELECT wp_users.`ID`,
wp_users.`user_login` AS ic,
wp_users.`user_email`,
(SELECT meta_value FROM `wp_usermeta` WHERE meta_key = 'first_name' AND user_id = wp_users.`ID` LIMIT 1 ) AS nama_penuh,
(SELECT meta_value FROM `wp_usermeta` WHERE meta_key = 'gender' AND user_id = wp_users.`ID` LIMIT 1 ) AS jantina,
SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT meta_value FROM `wp_usermeta` WHERE meta_key = 'gender' AND user_id = wp_users.`ID` LIMIT 1 ),';',2),':',-1) AS fieldname1,
REPLACE( SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT meta_value FROM `wp_usermeta` WHERE meta_key = 'gender' AND user_id = wp_users.`ID` LIMIT 1 ),';',2),':',-1),'"','') AS fieldname2
FROM wp_users WHERE user_nicename != '0'
//example 3
SELECT
posts_mengaji.ID AS mengaji_ID,
posts_mengaji.post_date AS mengaji_post_date,
posts_mengaji.post_title AS mengaji_post_title,
(SELECT meta_value FROM `wp_postmeta` WHERE meta_key = 'mengaji_email' AND post_id = posts_mengaji.ID LIMIT 1 ) AS mengaji_email,
(SELECT meta_value FROM `wp_postmeta` WHERE meta_key = 'mengaji_tel' AND post_id = posts_mengaji.ID LIMIT 1 ) AS mengaji_tel,
(SELECT meta_value FROM `wp_postmeta` WHERE meta_key = 'mengaji_alamat' AND post_id = posts_mengaji.ID LIMIT 1 ) AS mengaji_alamat,
(SELECT meta_value FROM `wp_postmeta` WHERE meta_key = 'mengaji_penjaga' AND post_id = posts_mengaji.ID LIMIT 1 ) AS mengaji_penjaga,
SUBSTRING_INDEX(
SUBSTRING_INDEX( (SELECT meta_value FROM `wp_postmeta` WHERE meta_key = 'mengaji_senarai_anak' AND post_id = posts_mengaji.ID LIMIT 1 ), '";}' , 1 ),
'"',-1) AS test,
concat('aaaa','\n',(SELECT meta_value FROM `wp_postmeta` WHERE meta_key = 'mengaji_senarai_anak' AND post_id = posts_mengaji.ID LIMIT 1 )) AS mengaji_senarai_anak
FROM wp_posts AS posts_mengaji WHERE posts_mengaji.post_type = 'mengaji' AND posts_mengaji.post_status = 'publish'
//best example
SELECT
posts_mengaji.ID AS mengaji_ID,
posts_mengaji.post_date AS mengaji_post_date,
posts_mengaji.post_title AS mengaji_post_title,
(SELECT meta_value FROM `wp_postmeta` WHERE meta_key = 'mengaji_email' AND post_id = posts_mengaji.ID LIMIT 1 ) AS mengaji_email,
(SELECT meta_value FROM `wp_postmeta` WHERE meta_key = 'mengaji_tel' AND post_id = posts_mengaji.ID LIMIT 1 ) AS mengaji_tel,
(SELECT meta_value FROM `wp_postmeta` WHERE meta_key = 'mengaji_alamat' AND post_id = posts_mengaji.ID LIMIT 1 ) AS mengaji_alamat,
(SELECT meta_value FROM `wp_postmeta` WHERE meta_key = 'mengaji_penjaga' AND post_id = posts_mengaji.ID LIMIT 1 ) AS mengaji_penjaga,
concat(
'Nama Anak:',
REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT meta_value FROM `wp_postmeta` WHERE meta_key = 'mengaji_senarai_anak' AND post_id = posts_mengaji.ID LIMIT 1),';',3),':',-1) , '"', '' ) ,
'\n','Tahap Mengaji:',
REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT meta_value FROM `wp_postmeta` WHERE meta_key = 'mengaji_senarai_anak' AND post_id = posts_mengaji.ID LIMIT 1),';',5),':',-1) , '"', '' ) ,
'\n','Nama Guru:',
REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT meta_value FROM `wp_postmeta` WHERE meta_key = 'mengaji_senarai_anak' AND post_id = posts_mengaji.ID LIMIT 1),';',7),':',-1) , '"', '' )
) AS mengaji_senarai_anak
FROM wp_posts AS posts_mengaji WHERE posts_mengaji.post_type = 'mengaji' AND posts_mengaji.post_status = 'publish'
Leave a reply