Tag Info

Hot answers tagged

62

Yes it's possible. The wpdb object can be used to access any database and query any table. Absolutely no need to be Wordpress related, which is very interesting. The benefit is the ability to use all the wpdb classes and functions like get_results, etc so that there's no need to re-invent the wheel. Here's how: $mydb = new ...


31

This is where your problem is: I then edited the resulting .sql file to update all of the file paths and URL references to point to our production site. You can't do that. WordPress stores many options as "serialized data", which contains both the string content of things and their length. So when you modify the URL and the length changes, then ...


17

See this special note about using the `the_date' SPECIAL NOTE: When there are multiple posts on a page published under the SAME DAY, the_date() only displays the date for the first post (that is, the first instance of the_date()). To repeat the date for posts published under the same day, you should use the Template Tag the_time() or ...


16

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


14

Its best practice to always use prepare but the main use of it is to prevent against SQL injection attacks, and since there is no input from the users/visitors or they can't effect the query then that is not an issue in your current example. but like i said before its best practice to use it and once you start using it you never stop , so in your example ...


14

Connecting to a second database is easy in WordPress, you simply create a new instance of the WPDB class and use it the same way you would use the standard $wpdb instance we all know and love. Assuming the second database has the same login information as the main WP one you can even use the predefined constants from wp-config.php to avoid hardcoding the ...


11

"All privileges" usually means you should grand everything to the user. However ... I've found at least one article that claims the MySQL user only needs: SELECT INSERT UPDATE Digging deeper, I found that in order to operate fully (automated updates, plug-in installation/uninstallation, etc.), WordPress requires some additional permissions: DELETE ...


11

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


10

the_date() prints the date only if the same date was not printed before. No, that's not consistent with other similar functions. But that’s how it worked in WordPress’ ancestor b2/cafelog, and backwards compatibility always trumps logic … :) To print the date always use get_the_date() <?php echo get_the_date(); ?> or <?php echo mysql2date( ...


9

There are a few different reasons. 1. Separation of Concerns Fundamentally, your logical code (i.e. your plugin or your theme) should not need to know anything about the database. At all. Really. The $wpdb object is the global database access layer, and you should be using it for all of your database access. If you need to run a custom query (let's say ...


9

no one has said this so I thought I'd add an even easier way.. as long as your additional database has the same user/pass details to access it as your wordpress database you can use the database name before the table name like this $query = $wpdb->prepare('SELECT * FROM dbname.dbtable WHERE 1'); $result = $wpdb->get_results($query);


9

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


9

I would indeed switch to InnoDB. Table locking/row locking has long been discussed by many. I would always choose InnoDB hands down. However, there is another profound reason for choosing InnoDB...CACHING. While most people boast that MyISAM is faster for reads, most people forget that the many cache for MyISAM, which is called the key cache (set by ...


8

A standard WordPress schema "sync" via dbDelta() will only add indexes, not drop them. Same goes for fields. We never touch the storage schema either, so it'd be the default for MySQL (which in latest versions is now InnoDB). On the face, a comment_id_meta_key index makes perfect sense. But when you look at how WordPress actually uses its metadata tables, ...


8

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. https://interconnectit.com/products/search-and-replace-for-wordpress-databases/


8

Strictly from a MySQL Point-of-View SHORT VERSION MyISAM Storage Engine Table locks Writes are first come, first serve Reads slow down writes from initiating InnoDB Storage Engine Row locks Transactions (non blocking) Deadlock may occur when updating indexes LONG VERSION If the underlying tables use the MyISAM Storage Engine, row locks are not ...


7

Codex has decent guide - Changing Site URL. Basically there are several places there URL matter or not so much (I may be missing some): home and siteurl options that control where WP thinks site is; post GUIDs, these look like links but really they aren't - only identifiers. I am not absolutely sure they are irrelevant, but I had URLs change on test stack ...


6

The data you're looking at is not JSON formatted. JSON doesn't (normally) store value types and lengths like that. What you're looking at is SERIALIZED data. A google search for "mysql replace serialized" yields this page, which might help: http://davidcoveney.com/mysql-database-search-replace-with-serialized-php/


6

Like so: INSERT INTO $mytable (post_id) SELECT ID FROM $wpdb->posts as posts LEFT JOIN $mytable as dup_check ON dup_check.post_id = posts.ID WHERE dup_check.post_id IS NULL;


6

Hi @goatlady: While this is really a MySQL question it does help to understand the WordPress SQL schema and also I love trying to optimize SQL queries so rather than send you off to StackOverflow I'll try to answer you here. You may still want to post it over there to get some other opinions. And while I don't fully understand your requirement I think I ...


6

As cool as infinite scalability would be, it is not really necessary to code into most projects. That limit of 100000000000000 is high enough that you could run a single WordPress Network for all the inhabitants dyson ring 1000 km wide at Earth's orbit with current population density to still have 7 accounts each. Or in another way thats about 15000 posts ...


6

If INT is set to UNSIGNED, allowing only non-negative integers, your value range is from 0 to 4294967295. Thats... 4,294,967,295 (4 billion +). ...for the given table. Just to put things into perspective. Only through poor management and unnecessary incrementation would you exhaust that range, say for your posts table as an example. That said if you ...


6

It is that simple for Wordpress too. I use the following to back up my WP sites: mysqldump -u <user> -p<pass> --quick --extended-insert <db-name> > backup.sql The mysqldump document gives the details on all the parameters. --extended-insert is quicker when updating a DB from a dump file and makes the dump file smaller. --quick makes ...


6

You can create a simple plugin and either add a shortcode to run your php, or filter the_content and add a conditional check for your specific page and inject your DB output. This way your code will be independent of the theme and more portable. Use the wpdb class to query any database /table.


6

Without looking at your box to see exactly what's going on, here are some potential avenues of slowness: Potential Causes Apache Apache is usually configured in such a way that a single httpd process is always running in the background. When a request comes in over the wire, it spins up a new httpd process to handle the request. Once the request closes, ...


6

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


5

This doesn't have anything to do with WordPress or your user's passwords. What it means is that your MySQL server is still using the old-password-hash mechanism, which was changed in MySQL 4.1. The PHP mysqli client is newer and doesn't support the old password mechanism. Since this causes an error, WordPress falls back to the old mysql client, which does ...


5

This answer is the way I would take to solve the issue if I had to face it, it means is not the answer but one of the possibilities. All the operations I will suggest should be run on a development/local server on a backup of database and not on production nor on original database. If you have 26,000 posts but post id like 4,863,166,253 there are zillions ...


5

There is no build in function to achieve what you want, at least not for complicated meta queries like this. If you need to use build in functions for this, the best will be to make use of WP_Query. To make the query faster and to skip the unwanted returned array of WP_Post properties, and because you are only interested in post count, you can use the ...



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