Tag Info

Hot answers tagged

42

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


30

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


15

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


15

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


11

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


9

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


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


9

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


9

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

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


8

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


7

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/


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

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

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

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

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

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

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.


5

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


5

What you're talking about is a database fallback. It's possible to set something like this up, but not directly with WordPress. How it would work Instead of accessing the database directly, you'd access a proxy. This proxy would sit in front of both your primary and backup databases and pass traffic through. Essentially, it would work as a load balancer ...


5

You could do one of two things: Setup Manual Failover Setup MySQL Circular Replication with a DBVIP point at one of the DB Servers. The other DB Server would be used as a passive but hot standby. You would have to limit all reads and writes to the DBVIP. Suppose the DBVIP you want is 10.1.2.30. You would simply run this on whichever DB Server would server ...


5

Try this code: WARNING: THIS IS JUST PSEUDOCODE! $entries = $wpdb->get_results("SELECT * FROM wp_posts WHERE post_type IN ('post', 'page')"); foreach($entries as $entry) { $post_id = $entry->ID; $comment_count = $wpdb->get_var("SELECT COUNT(*) AS comment_cnt FROM wp_comments WHERE comment_post_ID = '$post_id' AND comment_approved = '1'"); ...


5

You can design your table in such a way that an ID can only exists once by using a key. You can then run a insert into query that selects existing ID values directly with a subselect. Like so (credits Denis): 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 ...


5

As Rarst said above there's only two settings in the database that NEEDS changing. After importing the database I log into PHPMyAdmin and edit the database direct. I use a development server on my PC all the time to import datafeeds and they have the URL attached to the Posts along the lines of http://localhost/testsite/post-name/ and it's never caused a ...



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