sql - Read For Learn

Database Tables Lost Indexes? How to Recover?

In general, you can fix the databases with some work in myPHPAdmin. See this article (among others found via the googles) https://www.a2hosting.com/kb/cpanel/cpanel-database-features/optimizing-and-repairing-mysql-databases-with-phpmyadmin There is also this post https://www.maketecheasier.com/fix-corrupted-wordpress-database-2/ that says you can let WP repair the databases by addjine one line in your wp-config.php file: define (WP_ALLOW_REPAIR, “true”); …placing it at the location as specified … Read more

SQL: What is wrong with the following query (generated by WordPress WP_Query, ordering prices)

Solved. After the edit it was easy. It is allowed to use type => ‘decimal(x,x)’ in meta_query. Maximum precision is decimal(65,30). https://dev.mysql.com/doc/refman/5.7/en/precision-math-decimal-characteristics.html array(6) { [“orderby”]=> array(2) { [“uss_product_price”]=> string(4) “DESC” [“title”]=> string(3) “ASC” } [“meta_query”]=> array(3) { [“relation”]=> string(3) “AND” [“sortprimary_clause”]=> array(3) { [“key”]=> string(17) “price” [“compare”]=> string(6) “EXISTS” [“type”]=> string(13) “decimal(30,5)” } [0]=> array(4) … Read more

How do I get the posts within a certain year/date(with sql query)?

Try this for posts from last 1 year: AND ( post_date_gmt < DATE(NOW()-INTERVAL 1 YEAR) )

SQL query to retrieve all the records that have not indicated category

If I understand the question, you’re looking for the SQL for the equivalent of $args = array ( ‘post_type’ => ‘popular_music’, ‘post_status’ => ‘publish’, ‘tax_query’ => array ( array ( ‘taxonomy’ => ‘category’, ‘operator’ => ‘NOT EXISTS’, ), ), ‘posts_per_page’ => -1, ‘orderby’ => ‘ID’, ‘order’ => ‘DESC’, ) ; $query = new WP_Query ($args) … Read more

how to delete 30 day old data using PHP [closed]

You’re adding an extra wp_ prefix to your table and your using a timestamp instead of a MYSQL datetime. Below is how WP suggests you use $wpdb->query to delete rows, taken from the codex. EDITED global $wpdb; $wpdb->query( “DELETE FROM ” . $wpdb->prefix . “userinfo WHERE timeall < DATE_SUB(CURDATE(),INTERVAL 30 DAY)” ); $wpdb->prefix adds the … Read more

Transferring working local PHP site to wordpress – with database (MySQL)

There are 2 ways you could do this. The first would be by creating a separate template for each of the pages you want. This is if you’re going to change a lot of different aspects of the page for each of the 7 pages. A page template will include the header, footer, and sidebar … Read more

Delete WordPress posts from URL list sql query

Yes you can. To delete posts with inherit post meta, use following code: DELETE p,pm FROM wp_posts p JOIN wp_postmeta pm ON pm.post_id = p.id WHERE p.post_name IN (‘post-1’, ‘post-2’, ‘post-3’) Pass slugs array to WHERE clause. If you want to delete only posts w/out postmeta (for some reason), use this code: DELETE FROM wp_posts … Read more

SQL query for custom taxonomy slugs

global $wpdb; $slugs = $wpdb->get_col( “SELECT slug FROM $wpdb->terms LEFT JOIN $wpdb->term_taxonomy ON $wpdb->terms.term_id = $wpdb->term_taxonomy.term_id WHERE $wpdb->term_taxonomy.taxonomy = ‘course'” ); That will return an array of strings representing the taxonomy term slugs for terms in the course taxonomy.

WordPress and MySQL: how to transfer Meta_key and Meta_Value from one post_id to another

UPDATE wp_postmeta SET post_id = 6219 WHERE post_id = 4319

Get stock by custom meta field on all Woocommerce variable products

The following function will give you an array with the calculated stock quantity from all product variations for each custom meta field “Brand” value, using WPDB Class (a SQL query): global $wpdb; $results = $wpdb->get_results( ” SELECT pm.meta_value as brand, SUM(pm2.meta_value) as stock FROM {$wpdb->prefix}postmeta pm INNER JOIN {$wpdb->prefix}posts p ON pm.post_id = p.post_parent INNER … Read more

+ More