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


28

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

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


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

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

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

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


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

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

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

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

WordPress only uses one DB user for everything, and he'll need to have all permissions to the database. CREATE and ALTER are used when upgrading, sometimes. INSERT, UPDATE, and SELECT are used all the time.


5

Unless you need to stay backwards compatible with WP 3.0, you can just take advantage of the advanced taxonomy queries support in WP 3.1. The code that generates the SQL can be found in wp-includes/taxonomy.php


5

InnoDB probably won't help you - page/row level locking helps mitigate contention, but it doesn't feel like that's your issue. There's a lot of stuff out there that suggests MyISAM is slower than InnoDB in the average blog scenario (many more reads than writes). Before making a switch, you should at least do the following run mysqltuner which will give ...


5

Hi @user1893: There are a couple of ways you can achieve what you asked for but the latter is what I'd recommend: Set up a copy of the site in a different directory and using your web host's control panel map the different domains to the appropriate directories. Use the same DB_NAME/DB_USER/DB_PASSWORD/DB_HOST in the /wp-config.php file for each of your ...


5

You could easily do this using mysql: DELETE FROM wp_comments WHERE comment_date < DATE_ADD(CURDATE(), INTERVAL -6 MONTH)



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