sql

Change permalinks in posts via SQL

If you are using MySQL 8 and above you can use REGEXP_REPLACE, e.i. UPDATE `wp_posts` SET post_content=REGEXP_REPLACE(post_content, ‘domain.tld\/[0-9]{4}\/[0-9]{2}\/[0-9]{2}\/’, ‘domain.tld/’) WHERE post_content REGEXP ‘domain.tld\/[0-9]{4}\/[0-9]{2}\/[0-9]{2}\/’; this would look for exact match domain.tld/{4 digit int}/{2 digit int}/{2 digit int}/ then replace it with domain.tld/ so it will replace something like domain.tld/2000/01/01/my-cool-post/ to domain.tld/my-cool-post/ Another option if you cannot … Read more

Firebase with WordPress instead of SQL?

No you can’t use Firebase as your WordPress database out of the box, and the chances there is a plugin to implement this are astronomically low. Your best hope is that you can find a plugin that lets you embed Firebase data or interact with Firebase, but that would still need to use MySQL/MariaDB for … Read more

Group By query based on Custom Field

Dont do SELECT post.* when you do grouping, you need to handle column values that belongs to a group properly like counting or combining them or whatever, Then do INNER JOIN individually for each meta key to have each own column. Paste the SQL below in your phpMyAdmin and confirm you get the result you … Read more

Weird WP -Cli Error Connection Refused

For anyone else who comes across this post… the above issue was caused by the w3-total-cache plugin being migrated between Test and Staging servers. To fix the issue, I deactivate and exclude w3-total-cache tables from the Test server’s db export, then I do the upload and import to Staging server, which has its own “local” … Read more

Merge meta_value’s into a single row and then loop

It would appear simply un-serialising works: $results = $wpdb->get_results( $sql_str ); foreach($results as &$result) { $result-> my_custom_meta_values = unserialize($result-> my_custom_meta_values); } If anyone can suggest a more efficient way to do this, I’d be interested.

Get count of rows based if column exists in two different tables

You need to use a join in your mysql query. Something like this might get you closer… global $wpdb; $rsvp_table = $wpdb->prefix . ‘rsvp’; $invites_table = $wpdb->prefix . ‘invites’; $sql=”SELECT COUNT(*) as count FROM $invites_table i1 JOIN $rsvp_table r1 ON (r1.reference = i1.reference)”; $yet_to_respond = $wpdb->get_results( $sql ); echo $yet_to_respond->count;

+ More