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


20

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


12

The like_escape() 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

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


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


8

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


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

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

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

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

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


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

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


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

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


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

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

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


3

Edited again to demonstrate adding and removing filters: Include these general functions in your functions.php. These are the filters you will invoke for the loops where you want to limit your query by these two meta values: function date_check_join( $join ) { global $wpdb; $join .= " JOIN ".$wpdb->postmeta." AS startdate ON ...


3

Your Query is "SELECT ID, display_name from $wpdb->users ORDER BY display_name **LIMIT 10**"; This Sql statement requests the first 10 authors


3

First add define('WP_SITEURL', 'http://example.com'); define('WP_HOME', 'http://example.com'); To your wp-config.php file (obviously change example.com to your domain) then login and once you see the admin panel navigate to http://yoursite/wp-admin/options.php there you can change home and siteurl to your new domain and save. You will also need to ...


3

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/


3

I believe you're looking for SQL UNION.



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