Hot answers tagged

24

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( '...


21

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


18

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->...


15

The meta_value is not of an integer type for max to return proper values. You can use mysql cast method to convert into integers as follows: SELECT max(cast(meta_value as unsigned)) FROM wp_postmeta WHERE meta_key='price'


15

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' ) )...


13

You should 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 = '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 understand it ...


11

I've come across a case where I want also want to quickly retrieve lots of posts with their associated meta information. I need to retrieve O(2000) posts. I tried it using Otto's suggestion - running WP_Query::query for all posts, and then looping through and running get_post_custom for each post. This took, on average, about 3 seconds to complete. I ...


11

I figured out a solution to my question. First of all, in my original query, I should have specified OR instead of AND for searching between group names and group descriptions. (It was skewing the results.) And I needed to double escape my '%'s in the LIKE statements. Here is the updated query which works correctly: SELECT * FROM {$wpdb->prefix}...


9

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

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('...


7

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, &$...


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

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 ...


6

Yes. Use WP_HOME and WP_SITEURL in your wp-config.php, so the URLs in the database won't mess (a lot) with local site development. define ('WP_HOME', 'http://local/site/url'); define ('WP_SITEURL', 'http://local/site/url'); Also, some other good practices: Put in your .gitignore things like: wp-config.php wp-content/uploads wp-content/cache wp-content ......


6

$wpdb doesn't suit for fetching huge amount of data from database. Why? In your case: $wpdb->get_results( ... ) - fetches all results into your RAM at once. It means if you have 4mb, 10mb, or 50mb of data in db, everything will be stored in memory (what is limited as you know). $wpdb->get_results( ..., ARRAY_A ) - $wpdb fetches everything as object by ...


6

The WordPress database is already indexed. See this codex article for a detailed list of indexes per table: http://codex.wordpress.org/Database_Description And even if it weren't, you'd need to know what queries are being run in order to effectively add indexes. Meaning, there would be no quick fix--you'd have to learn how indexing works, figure out what ...


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

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

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

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' =&...


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

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 ...


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 ...


6

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( ...


5

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


5

As Ben suggested, you need to pass the connection details when creating the wpdb class: $newdb = new wpdb( 'user', 'password', 'database', 'hostname' ); You should also test that the query actually returned something before using the result in a foreach loop: if ($rows) { foreach ($rows as $obj) { ... } }


5

$wpdb->prepare() use the same syntax for formatting like php's printf(). SSo you will need something like ... WHERE post_id IN (%d,%d,%d) ... This will fit for three ids. But what if we got more or less than three ids? We will use the php tools to create the needed formatting string (assuming $ids is an array with the ids): Count the IDs count( $ids ) ...


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' => '...


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