Skip to main content
34 votes
Accepted

How to use $wpdb to delete in a custom table

The best WP API solution for this goal is to use the delete() function to remove a row. A small example, to delete the raw ID in the custom table eLearning_progress. $id = 0815; $table = '...
bueltge's user avatar
  • 17.1k
21 votes

Transaction when using WP functions rather than vanilla SQL?

There is no functions in wordpress to handle transactions but you can easily use the $wpdb object to make a simple query to achieve this. The following function will start a transaction and then ...
Cyclonecode's user avatar
  • 1,164
18 votes

WP_Query with "post_title LIKE 'something%'"?

With some vulnerable solution posted here, I come with a bit simplified and sanitized version. First, we create a function for the posts_where filter which allows you to only show posts matching ...
Christine Cooper's user avatar
  • 8,798
15 votes

"#1067 - Invalid default value for 'post_date'" when trying to reset AI after backup

The post_date default value is 0000-00-00 00:00:00. If you check the sql_mode variable like this: show variables like 'sql_mode'; ... it will show you the sql_mode variable, that will be sth like ...
Carlos Faria's user avatar
10 votes

"#1067 - Invalid default value for 'post_date'" when trying to reset AI after backup

You must to add this code at the top of your SQL SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET AUTOCOMMIT = 0; START TRANSACTION; SET time_zone = "+00:00";
Cristian Cartes's user avatar
9 votes

What SQL Query to do a simple find and replace

For this I use WP-CLI because I find it the easiest and it takes care of serialized data. wp search-replace 'http://example.dev' 'http://example.com' --skip-columns=guid There is also an option that ...
Jan Beck's user avatar
  • 1,467
9 votes

Is there a way to list all used/unused WP templates?

Here's a rough function I use to handle this, it should work well for anyone out there wanting to do this quick and easy. Add this this your functions.php file and then visit your site with ?...
Kevinleary.net's user avatar
9 votes
Accepted

Get Post ID by Content

WordPress has a handy function called post_exists() which allows you to find an existing post by its title, content, and/or date. It returns the post ID on success or 0 otherwise. In your case, you ...
swissspidy's user avatar
  • 3,844
8 votes
Accepted

Backticks (`) Instead of Single Quotes (') in an SQL Statement?

Backticks and regular quotes (both single and double) have distinct meanings in SQL. Quotes indicate a literal string, whereas backticks are quoted identifiers. This isn't specific to WordPress, but ...
Ryan McCue's user avatar
7 votes

WPDB Insert or if exists Update

You can try and update the table using $wpdb->update, I am using the id in the following code, but you can use any criteria. $result = $wpdb->update($tableName, $info, array('id' => $info[&...
Darrel K.'s user avatar
  • 171
7 votes
Accepted

wpdb get posts by taxonomy SQL

Sorry guys, I just found the solution: SELECT p.post_name, t.name as clientName FROM $wpdb->posts AS p INNER JOIN $wpdb->term_relationships AS tr ON ( p.ID = tr.object_id) INNER JOIN $wpdb->...
Web-Entwickler's user avatar
7 votes

wpdb->prepare function remove single quote for %s in SQL statment

You can use %1s which excludes single quotes wrapper. By using %1s, you will note that the string won't be having single quotes and you will get the values directly as needed. $query = $wpdb->...
Malav Vasita's user avatar
7 votes
Accepted

Creating 20,000 Posts or Pages using a .csv file?

Import posts from a .csv file with WP-CLI If our import.csv is tab delimited, with two columns: Planet Mars The red planet. Planet Earth Our blue planet. then we can parse it with help of ...
birgire's user avatar
  • 67.4k
7 votes
Accepted

How to make WordPress plugin check for database changes and then do something?

By checking the date columns: To only check for new comments, new posts etc., you may create a cron job & then check the comment_date, post_date etc. to determine if anything new appeared in ...
Fayaz's user avatar
  • 8,937
7 votes

Is there a (better) way to access $wpdb results?

The WPDB Class has quite a few methods which vary what will be returned. Using WPDB::get_results() returns an array of objects whose properties end up being what it expects to be returned. In this ...
Howdy_McGee's user avatar
  • 20.7k
7 votes
Accepted

Get the exact SQL query that get_posts() generated

The get_posts() function creates an instance of WP_Query inside the function and simply returns the array of results. This WP_Query instance doesn't override the global $wp_query and is out of scope ...
Howdy_McGee's user avatar
  • 20.7k
7 votes

Get the exact SQL query that get_posts() generated

There is a way to get the SQL query generated by the get_posts() function by using the 'query' filter (since you are not suppressing the filters in you query, the hook will be fired prior to execution ...
Aurovrata's user avatar
  • 1,336
6 votes
Accepted

register_activation_hook isn't adding table to DB

Note that the $file in register_activation_hook( $file, $callback ), should be equal to path to the main plugin file, but when you have it in a sub file as __FILE__ then it's not the same! That means ...
birgire's user avatar
  • 67.4k
6 votes
Accepted

How to execute mulitple statement SQL queries using $wpdb->query?

TL;DR This is not possible with wpdb. Use dbDelta(). Even better, use something else to run raw queries. Explanation wpdb->query() uses the mysql_query() function. From PHP manual: mysql_query() ...
XedinUnknown's user avatar
6 votes
Accepted

WPDB: how to get the value of a field in a custom database table

OK, so first of all, you should always use proper escaping, when building the queries. This is how your code can look after fixing: global $wpdb; $value = $wpdb->get_var( $wpdb->prepare( " ...
Krzysiek Dróżdż's user avatar
6 votes
Accepted

How to get the list of WooCommerce product image of a certain category from database?

The post thumbnail is not stored as a URL. The post thumbnail is stored as the attachment ID in the _thumbnail_id post meta. The actual file is stored as the _wp_attached_file post meta for that ...
Jacob Peattie's user avatar
5 votes
Accepted

Sorting custom taxonomy causes menus error

Currently your code is modifying all term queries, both in the front-end and in the back-end. Each navigational menu is registered as a term in the nav_menu taxonomy, so when you visit the backend to ...
birgire's user avatar
  • 67.4k
5 votes
Accepted

Sorting search results by taxonomy terms

Unfortunately, although WP_Query supports the 'tax_query' arg, it does not support ordering based on post terms. So you will need to modify the query SQL, as you are doing now. However, you are ...
J.D.'s user avatar
  • 3,915
5 votes
Accepted

Where are image paths to product category images stored in WordPress database

Category images are stored as attachments, which is a post type. You'll find them in wp_posts with a post_type "attachment". The guid field contains the location of the image.
butlerblog's user avatar
  • 5,006
5 votes

register_post_status - show_in_admin_all_list & show_in_admin_status_list does not affect query

TL;DR: It's not a bug (as we generally understand it), rather it's a feature that was never fully implemented in WordPress. Status of register_post_status() register_post_status() function was never ...
Fayaz's user avatar
  • 8,937
5 votes
Accepted

Is there a (better) way to access $wpdb results?

This answer explains what the OP saw with column names and how to work with that, but the real answer is to use get_var() as in Howdy_McGee's answer. The string you're seeing is the column name that ...
Rup's user avatar
  • 4,287
5 votes
Accepted

Block search SQL from happening

It's possible to avoid SQL execution in WP_Query with the posts_pre_query filter. It's e.g. used by plugins to outsource the default search to 3rd party search engines. Here's an example how we can ...
birgire's user avatar
  • 67.4k
4 votes

How to retrieve sticky post in raw sql?

In raw SQL it would look like this: SELECT option_value FROM `wp_options` where option_name = 'sticky_posts' It will return string similar to: a:2:{i:0;i:27935;i:1;i:33322;} Which means that posts ...
jmarceli's user avatar
  • 1,066
4 votes

Get all child comments ids from parent comment id

I found this post because I was looking for a similar solution. As suggested by Captain47, get_comments with parent args, won't work because it's return only 1 level nested comments. To get ...
Des79's user avatar
  • 41
4 votes

Using OR conditions in meta_query for query_posts argument

According to the WordPress codex nested arrays can be used to construct complex queries. In your example your need something like this: $args = array( 'taxonomy' => 'courses', 'term' => ...
Geza Gog's user avatar
  • 151

Only top scored, non community-wiki answers of a minimum length are eligible