Hot answers tagged

76

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


32

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


21

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


21

It's 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 it's best practice to use it and once you start using it you never stop, so in your example ...


19

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


18

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


17

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


13

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


12

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


11

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


10

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


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


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

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


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

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


7

To deal with this issue I always use WordPress Serialized Search & Replace tool provided here. It works perfectly fine with out any issues. I have been using this for a long time on all my site migration requirements. This really take care of the issues with migrating development database to production. ...


7

Using the defines the user sets in wp-config: mysql_connect(DB_HOST, DB_USER, DB_PASSWORD); EDIT: Since your script is outside the Wordpress environment, what you want to do is initiate it before using the defines in wp-config. require_once(path/to/wp-config.php'); mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);


7

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


7

An update for Wordpress 3.9+ - with PHP 5.5 deprecating the mysql_* functions Wordpress has begun using the mysqli if it is available on the server. Your future plugins and code should take this into consideration and stop using mysql_*. Right now, in 3.9, wordpress checks for the availability of mysqli and uses it if available. If not it uses mysql_* but ...


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


6

The Bad News: The core open source base of Wordpress does make quite a few assumptions about being run on a single server (wp-content, user uploads and media library to name a few) The Good News: Pretty much all cloud providers (including Azure) have abstractions that allow you to work around these design limitations. Fundamentally, you'll be addressing ...


6

Here's what Codex has to say on restricting database user privileges: For normal WordPress operations, such as posting blog posts, uploading media files, posting comments, creating new WordPress users and installing WordPress plugins, the MySQL database user only needs data read and data write privileges to the MySQL database; SELECT, INSERT, UPDATE and ...


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

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

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;



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