MySQL

Weird characters displayed when importing WordPress MySQL db

Can you try setting the DB Charset within wp-config.php? define(‘DB_CHARSET’, ‘utf8_general_ci’); define(‘DB_COLLATE’, ‘utf8_general_ci’); Have a read of this first though: https://codex.wordpress.org/Editing_wp-config.php It can cause problems to an already existing blog to add those lines into wp-config.php, so use with caution. Are you importing this through WordPress or manually through something like PhpMyAdmin? Also, are all … Read more

Query does not filter duplicate _sku numbers

I haven’t tested this, but this should give you the count of skus for each post_id: SELECT post_id, COUNT(`post_id`) AS sku_count FROM `wp_postmeta` WHERE `meta_key` LIKE ‘%sku%’ GROUP BY `post_id` To have it return only rows where the count is greater than one is trickier. Off the top of my head I’d probably use the … Read more

Use custom query if main search query returns zero results in wordpress

Exactly right about resetting it! You should use wp_reset_query(). Try this: $num_res = $wp_query->post_count; $get_search_term = get_search_query(); // No more work required for the query, so… wp_reset_query(); if($num_res == 0) { $args = array( ‘post_type’ => ‘resources’, ‘meta_key’ => ‘resource_txt’, ‘meta_value’ => $get_search_term, ‘meta_compare’ => ‘LIKE’ ); $custom_query = new WP_Query( $args ); // Do … Read more

CONCAT_WS in custom sql query

‘CONCAT_WS(‘,’, wp_postmeta.meta_key, wp_postmeta.meta_value)’ was the name of the column. Everything is working as it should. To change the name of the column, add AS name after the CONCAT_WS statement, like this: ‘CONCAT_WS(‘,’, wp_postmeta.meta_key, wp_postmeta.meta_value)’ AS name (I didn’t test this specifically). Now the new column will be called name.

Select latest 2 articles, but only from given category

Try the below code- SELECT p.*, t.term_id FROM wp_posts p LEFT JOIN wp_term_relationships rel ON rel.object_id = p.ID LEFT JOIN wp_term_taxonomy tax ON tax.term_taxonomy_id = rel.term_taxonomy_id LEFT JOIN wp_terms t ON t.term_id = tax.term_id WHERE tax.taxonomy = ‘category’ AND post_type = “post” AND post_status = “publish” AND t.term_id != 56 ORDER BY ID DESC LIMIT … Read more

$wpdb post type and term query only works when there are no dashes or spaces in the term slug and title

In your code you are using “AND terms.name=”shorelineorwaterway” where I don’t think shorelineorwaterway is your term name, it could be your terms slug. So you can modify this by “AND terms.slug = ‘shorelineorwaterway’ and here inside inverted commas you can use hyphen. If you need to use term name then “AND terms.name=”Your Term Name” would … Read more

mysql query on wp_user and user_meta problem

In usermeta table WordPress stores many serialized data. So direct meta_value would not work I think. So try the below query- SELECT a.id, a.user_email, a.user_registered, a.user_login, b1.meta_value AS first_name, b2.meta_value AS last_name, b3.meta_value AS qualified, b4.meta_value AS referrer, b5.meta_value AS view_type, b6.meta_value AS ref_by, b7.meta_value AS wp_optimizemember_custom_fields FROM wp_users AS a INNER JOIN wp_usermeta AS … Read more

+ More