Accessing a Second Database in WordPress
September 18, 2012Tweet
WordPress is a powerful Content Management System that allows for ease of use in updating content and templating pages, all while obeying SEO best practices. WordPress uses the PHP coding language augmented with specially designed WordPress-dependent functions to edit and grab information from a dedicated database. WordPress is shipped with the ability to use “pretty” permalinks, which are translated into query strings, then used to talk to the database. “Pretty” permalinks are a powerful feature that increases your site’s legibility to both users and Search Engines.
When incorporating a second database into a site, which would require additional query strings, a few extra steps must be taken in order to generate the “pretty” permalinks.
“Pretty” Permalinks Explained “Permalinks are the permanent URLs to your individual weblog posts, as well as categories and other lists of weblog postings.
‘Pretty’ Permalinks is the idea that URLs are frequently visible to the people who click them, and should therefore be crafted in such a way that they make sense, and not be filled with incomprehensible parameters.”
(http://codex.wordpress.org/Introduction_to_Blogging#Pretty_Permalinks)
Typically, when a user fills out a form on a website and clicks send the url in the address bar would then
look like:
Similarly, when a user clicks on a link in WordPress a query is sent through the url:
www.mysite.com?page_id=5
This query is interpreted by PHP script and used to grab information about this page from the database in order to populate the page. However, if “pretty” permalinks are enabled, the user will never see this query. Instead they would see:
www.mysite.com/myawesomepage
When you select “pretty” permalinks in the WordPress backend, WordPress places code into your .htaccess file and uses a series of PHP scripts to rewrite the URL. In .htaccess there is an Apache function known as mod_rewrite (http://www.sitepoint.com/guide-url-rewriting/) – I will get back to this later.
There are many benefits to “pretty” permalinks. As described above, they “make sense” to visitors and are thus easy to remember when trying to find the page for a second time. They are also “bookmarkable”.
When a php script dynamically generates a page, as is the case with WordPress, the resulting page doesn’t really exist in your server. www.mysite.com/myawesomepage doesn’t exist, what does exist is www.mysite.com/index.php. The index.php script accesses the database and populates itself with information pertaining to a page with id “5.”
If “pretty” permalinks is left off, Search Engine spiders treat www.mysite.com?page_id=5 and www.mysite.com?page_id=4 as the same page since they use the same script, index.php.
When you turn “pretty” permalinks on, the resulting HTML file made by www.mysite.com?page_5 is treated as www.mysite.com/myawesomepage and the result of www.mysite.com?page_id=4 as www.mysite.com/myotherawesomepage. Now when a spider goes to these pages, they never see index.html, instead www.mysite.com/myawesomepage and www.mysite.com/myotherawesome page are treated as two separate pages.
This brings us to the best part of pretty permalinks; the pages they represent are completely readable by SEO Spiders. They can now be cached by Google and other search engines and appear in search results.
Accessing a Second Database
We’ll come back to permalinks in a moment, but first; a quick thing to note about using a second database. For this project a second database needs to be accessed in order to grab additional information which will populate a page with information based upon a search form that a user fills out. While accessing a second database isn’t something that WordPress sites typically do, there is an easy and clean way to do this.
$newdb = new wpdb($DB_USER, $DB_PASSWORD, $DB_NAME, $DB_HOST); $newdb->show_errors();
This script goes into WordPress’s function.php.
This allows the programmer to access the second database the same way they would access the WordPress database without either connection being interrupted. The programmer would use $wpdb with functions found here http://codex.wordpress.org/Class_Reference/wpdb to access the WordPress database. They can now use $newdb with all the same functions to access the second database.
Pretty Permalinks with Additional Queries for the Second Database
If I have a form on my homepage, which defaults to reading index.php in WordPress, it would consist of a drop down menu with a list of number like this:
When a user selects a number and clicks submit a url is sent to the address bar that looks like:
www.mysite.com?id=5
This brings us back to our original problem, URLs with a query visible in it. This query is being interpreted and used to access a second database. Since this isn’t a default feature of WordPress, WordPress doesn’t know how to automatically turn it into a “pretty” permalink.
Outside of WordPress, a programmer would use mod_rewrite directly to rewrite the URLs. However, because most of WordPress’s own rewriting functions aren’t in the .htaccess file but in a separate PHP script, manipulating the mod_rewrite code directly might break WordPress’s system.
WordPress does give us a way to create our own functions for URL rewriting with the WP_Rewrite class of functions (http://codex.wordpress.org/Function_Reference/WP_Rewrite). We are now able to place rewrite rules in the functions.php file that WordPress provides and this places our rules alongside WordPress’s rules when it talks to mod_rewrite. Typically WP_Rewrite is used to modify WordPress’s queries but we can use it with our custom query strings.
The code I used to create the pretty permalink for my prototype was:
function add_rewrite_rules() {
add_rewrite_rule('([0-9]+)','index.php?tid=$matches[1]','top');
} add_action( 'init', 'add_rewrite_rules' );
Beautifully simple. The key function is add_rewrite_rule which takes three values:
“rule – a regular expression with which to compare the request URL against
rewrite – the query string used to interpret the rule. The $matches array contains the captured matches and starts from index ’1′.
position – ‘top‘ or ‘bottom‘. Where to place the rule: at the top of the rewrite array or the bottom. WordPress scans from the top of the array to the bottom and stops as soon as it finds a match. In order for your rules to take precedence over existing rules you’ll want to set this to ‘top‘. Default is ‘bottom‘.”
(http://wp.tutsplus.com/tutorials/creative-coding/the-rewrite-api-the-basics/)
So now if I place www.mysite.com/1 in the url, WordPress will interpret it as www.mysite.com?tid=1 . However, this is a one way process. I can’t put www.mysite.com?tid=1 in and get www.mysite.com/1 There is a way around this, but I will get to that a little later.
Accessing the Query Values
We must take an extra step if we want to use get_query_var() to get “tid” ‘s value. We must register “tid” as a query value with some more easy code:
function tid_register_rewrite_tag() { add_rewrite_tag( '%tid%', '([0-9]+)');
} add_action( 'init', 'tid_register_rewrite_tag');
The key function here is the add_rewrite_tag() which also takes three values:
“tag name – (with leading and trailing %) e.g: %eventdate% regex – Regular expression to validate the value, e.g. ‘([0-9]{4}-[0-9]{2}-[0-9]{2})‘
query – (optional) How the tag is interpreted, e.g. ‘eventdate=‘. If provided, must end with a ‘=’.”
(http://wp.tutsplus.com/tutorials/creative-coding/the-rewrite-api-the-basics/)
However, only the first two values are important for us. Now we can have $id = get_query_var(‘tid’); in our script and get the value of tid from either www.mysite.com/1 or www.mysite.com?tid=1 .
So that the Users never see the Query
In our original example we used a form to send a query that would access the second database. Despite all the beautiful code we have written, this query will still appear. The rewriting can only translate www.mysite.com/1 into www.mysite.com?tid=1 but not the other way around.
To solve this I changed the way that our form works. Instead of using a SUBMIT button, I am using a link that is automatically updated based on the drop down menu with javascript.
Our HTML now looks like:
And the necessary Javascript code (using jQuery) is:
Now, the user will never see the query string which is important for site security.
Web Design and Development, Web Tutorials, Wordpress
