Hot answers tagged

27

Have you tried $wpdb->replace. According to WP Codex: Replace a row in a table if it exists or insert a new row in a table if the row did not already exist. I have tried myself in some plugins and it does the work when trying to avoid unique IDs duplication errors, etc. More info in the codex


25

First, you are using prepare incorrectly. You seem to have $wpdb->update's arguments wrapped in $wpdb->prepare like that. That won't work. In effect, you are passing update a single argument-- the output of prepare. Try something simple like the following and you will see why that won't work: $post_id = 123; $item_stock = 567; var_dump( $wpdb->...


21

Simplified: function title_filter( $where, &$wp_query ) { global $wpdb; // 2. pull the custom query in here: if ( $search_term = $wp_query->get( 'search_prod_title' ) ) { $where .= ' AND ' . $wpdb->posts . '.post_title LIKE \'%' . esc_sql( like_escape( $search_term ) ) . '%\''; } return $where; } $args = array( '...


20

It would be best if you used the delete() function to remove a row. A small example to delete the raw 'ID' in the custom table 'eLearning_progress.' $id = 0815; $table = 'eLearning_progress'; $wpdb->delete( $table, array( 'id' => $id ) ); But I can't see which raw you will delete in your table 'eLearning_progress'? Maybe you enhance the question to ...


19

Wanted to update this code you guys worked on for the wordpress 4.0 and above as esc_sql() is deprecated in 4.0 higher. function title_filter($where, &$wp_query){ global $wpdb; if($search_term = $wp_query->get( 'search_prod_title' )){ /*using the esc_like() in here instead of other esc_sql()*/ $search_term = $wpdb->...


16

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 specific conditions: function cc_post_title_filter($where, &$wp_query) { global $wpdb; if ( $search_term = $wp_query->get( 'cc_search_post_title' ) )...


12

The whole bunch of code is to replicate the process, not to give the actual code. You have to figure out how your process can be meet up with this scenario. I commented on every possible line, please see the inline comments to get proper understanding. Step #1 First of all, the first portion of your code makes a Custom Meta Box, so use the portion to make ...


9

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 can use $post_id = post_exists( '', '{"member_id":33}' ) to find the post you're looking for. Note: That function doesn't check the post type so you'd need to ...


8

Building on other answers before me, to provide flexibility in the situation where you want to search a post that contains a word in a meta field OR in the title of the post, I give that option via the argument "title_filter_relation." In this implementation, I only allow for "OR" or "AND" inputs with a default of "AND." function title_filter($where, &$...


8

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 writes your changes into an SQL file instead of manipulating the actual database: wp search-replace foo bar --export=database.sql


8

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 insert a new user into the database and finally rollback the current transaction. In order to make the transaction permanent you would instead use $wpdb->query('...


8

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 ?template_report added onto the URL to display a report for every custom theme template. It's rough, I'd suggest commenting/uncommenting the add_action call when you ...


7

You can't use prepare for column names, and you can't really use it for the sort order either. prepare will always quote the string. You will need to swap in the values yourself. Rather than try to "sanitize" the data, I'd use a white-list approach. $orderby = array( 'date' => 'post_date', // etc ); $sortorder = array( 'asc' => 'ASC', 'desc' =&...


7

I had the exact same issue. The problem is not one that can be fixed without modifying some code that you probably shouldn't (or perhaps writing a filter or a 'drop-in'). The issue is the CAST directive in the SQL statement. It CASTS the entire table before it does anything, with the amount of records you have, its going to take a while. Capture the query, ...


7

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 rather a general way in SQL of quoting columns or tables. For example, imagine you're running a query comparing two columns: SELECT * FROM wp_posts WHERE ...


7

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 this: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION You have to set up ...


7

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 case may be best to alias your subquery. For example, if I wanted to check if user ID 1 exists I could say: $results = $wpdb->get_results( "SELECT EXISTS( ...


6

I found myself in a situation that I needed to do this task to ultimately create a CSV document from, I ended up working directly with mysql to do this. My code joins the post and meta tables to retrieve woocommerce pricing information, the previously posted solution required that I use table aliases in the sql to work properly. SELECT p.ID, p.post_title, ...


6

I can't use $wpdb->prepare, since I want to be able to add variables to my query string that look something like: $var = "AND pm.meta_value = '%$_POST['val']%'"; To get a literal % to pass through $wpdb->prepare just double it. You don't need to be avoiding $wpdb->prepare. Proof of concept: var_dump($wpdb->prepare('SELECT * FROM {$wpdb->...


6

Use mysqli_real_escape_string(). The core uses still the deprecated mysql_real_escape_string() or add_slashes() in wpdb::_real_escape() … /** * Real escape, using mysql_real_escape_string() or addslashes() * * @see mysql_real_escape_string() * @see addslashes() * @since 2.8.0 * @access private * * @param string $string to escape * @return ...


6

A simple user meta row can handle that for you (the second issue), you can store the post id and the vote (up/down) in an array and that is just the same as post meta ex /** * update user vote per post * @param int $user_id * @param int $post_id * @param mixed $vote can be an integer 1 / -1 and can also be a string "up"/"down" * @return void */ ...


6

Just to clarify the get_var() method of $wpdb does work just fine in this context: global $wpdb; $helloworld_id = $wpdb->get_var("SELECT ID FROM wp_posts WHERE post_name = 'hello-world'"); echo $helloworld_id; Actually it is more practical in this context, because a single variable is returned, which is what is actually wanted.


6

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 your callback is never called. I would also recommend prefixing the function's name to avoid possible name collision or use namespaces. More in the Codex ...


6

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 this answer, where we use IFS=$'\t' instead as tab delimiter: while IFS=$'\t', read col1 col2; \ do \ echo "$col1|$col2"; done < import.csv Note ...


6

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 those tables. Save a last checked timestamp from the cron job, so any row with date greater than the last checked timestamp is a new insert. Using WordPress API ...


5

Use 'relation' => 'OR' as in the Codex example below: $args = array( 'post_type' => 'product', 'meta_query' => array( 'relation' => 'OR', /* <-- here */ array( 'key' => 'color', 'value' => 'blue', 'compare' => 'NOT LIKE' ), array( 'key' => '...


5

The only generic semi-automatic procedure I can think of would be to delete post meta where the post_id is invalid. SELECT * FROM {$wpdb->postmeta} as pm LEFT JOIN {$wpdb->posts} as p ON pm.post_id = p.ID WHERE p.ID IS NULL I wrote a SELECT and not a DELETE because I don't trust that to work in all cases. I have seen "dummy" or "placeholder" ...


5

Note that here you're overriding the paging of the main query, with the posts_limits filter, by using hardcoded values: 'LIMIT 0, 25' where 0 is the offset and 25 is the number of posts to display. So in this case I would just use pre_get_posts with $query->set( 'posts_per_page', 25 ); and we don't have to worry about the paging. If i wanted to ...


5

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->term_taxonomy AS tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id) INNER JOIN $wpdb->terms AS t ON (t.term_id = tt.term_id) WHERE p.post_status = '...


5

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 work on the menus, those queries have been modified too by your code snippet. For example, I don't see any ! is_admin() check in your code. You should ...


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