Common SQL Scripts when dealing with MySql and WordPress

While working with WordPress, I often find that people want you to use WordPress and php to add or update data.  It makes sense, use the API so that you don't mess anything up, but sometimes, just writing a sql script in MySql is just faster.  Here are a few mysql scripts and tips that I use a lot:

IMPORTING DATA FROM FILES

You can import data in a few methods.  You can import via a csv with the following:

LOAD DATA INFILE 'datasheet.csv' 
INTO TABLE mytable 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Copy

On Windows, I had to put the file in: C:\ProgramData\MySQL\MySQL Server 5.7\Uploads
You may have to use this command to figure out where your server is configured to have the import file placed:

SHOW VARIABLES LIKE "secure_file_priv";

Copy

You can also use the command line to import a large sql file.  I use this often when I have a file that is too large for MySql Workbench to handle:

cd "c:\program files\mysql\mysql server 5.7\bin"
mysql -u root -p database_name < d:\path\to\sql\file.sql

Copy

And now for some common queries:

-- get count of posts
select count(*) as num from wp_posts where post_type = 'post';

-- get unique post types and counts
select count(*) as num, post_type from wp_posts group by post_type order by count(*) desc;

-- get distinct post types
select distinct post_type from wp_posts order by post_type;

-- get all meta for a given post
select * from wp_postmeta where post_id = 1;

-- bulk insert into postmeta conditionally from wp_post
insert into wp_postmeta (post_id, meta_key, meta_value)
select id, '_thumbnail_id', '12177' from wp_posts where post_type = 'my-post-type'
and post_title like '%some-query%';

-- get all terms for a particular category/taxonomy
select * from wp_terms t inner join wp_term_taxonomy x on t.term_id = x.term_id where taxonomy = 'category';

-- get posts with specific pivoted meta data
SELECT DISTINCT p.id, p.post_name,
    MAX(CASE WHEN pm.meta_key = '_thumbnail' THEN pm.meta_value ELSE NULL END) as thumbnail,
    MAX(CASE WHEN pm.meta_key = 'other_field' THEN pm.meta_value ELSE NULL END) as other_field,
    MAX(CASE WHEN pm.meta_key = 'other_field2' THEN pm.meta_value ELSE NULL END) as other_field2
FROM
wp_term_taxonomy x INNER JOIN
   wp_terms t on x.term_id = t.term_id INNER JOIN
   wp_term_relationships r ON x.term_taxonomy_id = r.term_taxonomy_id INNER JOIN
wp_posts p ON r.object_id = p.id LEFT JOIN 
wp_postmeta pm ON p.id = pm.post_id
WHERE
x.taxonomy = 'my-taxonomy'
AND p.post_type = 'my-post-type'
GROUP BY p.post_name
ORDER BY thumbnail, other_field, other_field2;

-- update counts on term tax (requires adding a 'count' field on wp_term_taxonomy table)
update wp_term_taxonomy x inner join
(select count(*) as thecount, t.term_taxonomy_id
 from wp_term_taxonomy t inner join
  wp_term_relationships r on t.term_taxonomy_id = r.term_taxonomy_id
           group by t.term_taxonomy_id) a on x.term_taxonomy_id = a.term_taxonomy_id
set x.count = a.thecount;

-- Import users from a temp table
INSERT IGNORE INTO wp_users (
    ID,
    user_login,
    user_pass,
    user_nicename,
    user_email,
    user_registered,
    user_activation_key,
    user_status,
    display_name,
   user_url
)
SELECT DISTINCT
u.authorid,
lower(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(u.displayname), ' ', '_'), ',', ''), '.', ''), '''', '')),    
md5('password'),
lower(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(u.displayname), ' ', '_'), ',', ''), '.', ''), '''', '')),
concat(lower(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(u.displayname), ' ', '_'), ',', ''), '.', ''), '''', '')), '@fake.com'),
ifnull(createdon, now()), -- FROM_UNIXTIME(created),
'',
0,
u.displayname,
   ifnull(website, '')
FROM authors u
ORDER BY authorid;

-- bulk add a field into the user meta from a temp table
INSERT IGNORE INTO wp_usermeta (
    user_id,
    meta_key,
    meta_value)
SELECT DISTINCT
    u.id,
    'nickname',
    user_nicename
FROM wp_users u
WHERE u.id IN (SELECT authorid FROM authors);

-- globally replace bad characters
update wp_posts set post_excerpt = replace(post_excerpt, 'ÔÇÖ', '''') where post_excerpt like '%ÔÇÖ%';
update wp_posts set post_content = replace(post_content, 'ÔÇÖ', '''') where post_content like '%ÔÇÖ%';
update wp_posts set post_title = replace(post_title, 'ÔÇÖ', '''') where post_title like '%ÔÇÖ%';
update wp_postmeta set meta_value = replace(meta_value, 'ÔÇÖ', '''') where meta_value like '%ÔÇÖ%';