Tag Info

Hot answers tagged

42

Hi @Keith Donegan: If I understand your question correctly I think this is what you are looking for? <?php echo $GLOBALS['wp_query']->request; ?> $wp_query is a global variable that contains the current query run by the loop. If you run the above code anytime while the loop is still active or even right after the loop it should give you the SQL ...


22

Post meta information is automatically cached in memory for a standard WP_Query (and the main query), unless you specifically tell it not to do so by using the update_post_meta_cache parameter. Therefore, you should not be writing your own queries for this. How the meta caching works for normal queries: If the update_post_meta_cache parameter to the ...


16

Here is a modified checklist, based on my current (work-in-progress) settings/data security checklist used for reviewing Themes (the principles should be no different for Plugins than they are for Themes): Plugins should prefix all options, custom functions, custom variables, and custom constants with plugin-slug. Plugins should implement Plugin Options ...


16

I would solve this with a filter on WP_Query. One that detects an extra query variable and uses that as the prefix of the title. add_filter( 'posts_where', 'wpse18703_posts_where', 10, 2 ); function wpse18703_posts_where( $where, &$wp_query ) { global $wpdb; if ( $wpse18703_title = $wp_query->get( 'wpse18703_title' ) ) { $where .= ' ...


14

The $wpdb->esc_like function exists in WordPress because the regular database escaping does not escape % and _ characters. This means you can add them in your arguments to wpdb::prepare() without problem. This is also what I see in the core WordPress code: $wpdb->prepare(" AND $wpdb->usermeta.meta_key = '{$wpdb->prefix}capabilities' AND ...


11

There are two aspects to this: Basic principles. Whatever is written into database should be checked for SQL injects. Whatever is printed to screen should be checked that it doesn't print harmful JavaScript. Whenever someone does something it should be checked that it was his intention to do so and he has appropriate capability. There are many many more ...


10

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'


9

If you ran a query based on WP_Query, it's this: $customPosts = new WP_Query($yourArgs); echo "Last SQL-Query: {$customPosts->request}";


9

Ok, I got there at the end. I couldn't use WP_Query class as I really needed to have my own pretty big and complex SQL. Here is what I ended up having: In functions.php I have my custom SQL and logic for counting the values needed for the WP pagination logic: function vacancies_current( ){ global $wpdb, $paged, $max_num_pages, $current_date; ...


9

The Proper Network Activation meta-plugin was written precisely for cases like this.


7

Best to do options, posts, post content and post meta: UPDATE wp_options SET option_value = replace(option_value, 'http://olddomain.com', 'http://newdomain.com') WHERE option_name = 'home' OR option_name = 'siteurl'; UPDATE wp_posts SET guid = replace(guid, 'http://olddomain.com','http://newdomain.com'); UPDATE wp_posts SET post_content = ...


6

The table where your URL is saved is wp_options. You should do an update on the columns that use the URL for your site: UPDATE TABLE wp_options SET option_value = "new domain" WHERE option_name = "siteurl" UPDATE TABLE wp_options SET option_value = "new domain" WHERE option_name = "home" I might be missing some value, but whenever you do this find/replace ...


6

Rather than constructing query from scratch, it is easier to see what exactly is WordPress querying when API function is used: get_posts(array( 'numberposts' => -1, )); var_dump( $wpdb->last_query ); Gives following SQL: SELECT wp_posts.* FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = ...


6

I would recommend a pivot query. Using your example: SELECT p.ID, p.post_title, MAX(CASE WHEN wp_postmeta.meta_key = 'first_field' then wp_postmeta.meta_value ELSE NULL END) as first_field, MAX(CASE WHEN wp_postmeta.meta_key = 'second_field' then wp_postmeta.meta_value ELSE NULL END) as second_field, MAX(CASE WHEN ...


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


5

You should do defaults at the time of pulling the data out. Never insert default values into the database. Defaults are default. Options in the DB override defaults. How to do defaults for a serialized options array: $defaults = array( 'default1' => '1', 'default2' => '2', ); $options = wp_parse_args(get_option('plugin_options'), $defaults);


5

This is a great drop-in script that I use and it works beautifully with the serialized arrays that WP uses to store options. Just make sure to delete it from your remote server when you're done because it's a HUGE security risk. http://interconnectit.com/124/search-and-replace-for-wordpress-databases/


5

If you are only interested in Loops this is what I usually use: add_filter( 'posts_request', 'dump_request' ); function dump_request( $input ) { var_dump($input); return $input; }


5

See this answer: Best Collection of Code for your functions.php file Then add ?debug=sql to any WP URL, and it'll output the full list of queries that were run. (And yes, it's scary...)


5

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


5

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


4

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


4

Thanks to AmbitiousAmoeba for the answer. The following improved code solves the problem: function filter_where( $where = '' ) { global $wpdb; $where .= " AND (($wpdb->postmeta.meta_key = 'original_date' AND $wpdb->postmeta.meta_value >= '2000-01-01') AND ($wpdb->postmeta.meta_key = 'original_date' AND $wpdb->postmeta.meta_value <= ...


4

All you need to do is modify the SQL query. Using the code you linked as a base: $numposts = $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->posts WHERE post_status = 'publish' AND year(post_date) = 2010"); if (0 < $numposts) $numposts = number_format($numposts); The 'AND' I added basically gives you all the posts of 2010. Change the year ...


4

<?php /** * Plugin Name: Delete Non Authors */ function delete_non_authors() { global $wpdb; $non_authors = $wpdb->get_col( "SELECT DISTINCT $wpdb->users.ID FROM $wpdb->users LEFT JOIN $wpdb->posts ON $wpdb->users.ID = $wpdb->posts.post_author WHERE $wpdb->posts.ID IS NULL" ); foreach ...


4

If it is an older version of WP, you need to find out what version of WP generated the database, as WP upgrades the DB most times the files are upgraded and you should incrementally upgrade to also upgrade the database. Look in the wp_options table for option 711 and see what the version is; option name will be site_transient_update_core and the value will ...


4

I found the answer UPDATE `wp_postmeta` SET `meta_key` = 'ref' WHERE `meta_key` = 'refer' use this part in your SQL Reference


4

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) { ... } }


3

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


3

First of all i wouldn't use phpmyadmin for a database that large. Use something like Sequel Pro its free and easy to use. Second if you dont want the tables that WPML Created and you have removed the plugin from your site you can remove the tables that it creates. Upload the sql to a new database on your local machine and remove all this tables for each ...



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