Sep
30
2010

Obtaining The URL_ALIAS In A Drupal MySQL Query

Having the result of a nodes URL in MySQL query can be particularly important when developing custom functionality. It's probably most commonly found in SEO customizations

Share
Email

Custom Canionical links in fact is the most common way I’ve utilized the following technique but it has a ton of maintenance based uses. Especially for various kinds of report generation where you need a click-able link for others to use.

A quick search of a few of my Drupal sites reveal two common modules that utilize this query:

  • Path Auto – Automates your URL Alias’s
  • XML Sitemap – Automatically creates a standardized sitemap for your URL’s

Use MySQL’s CONCAT Function

The problem with obtaining a URL for the node you’re pulling up on a SQL Join lies within the fact that the url_alias table contains an src column as a VARCHAR. On top of that it prepends “node/” before each Node ID. The way around it is to use the CONCAT function in MySQL.

SELECT A url_alias By Node ID

First, lets find a node we know exists. Click edit on one of your nodes and look at the URL for a number. Thats your nodeid. I’m picking nodeid 2 for this example. An edit URL for that would look like this: http://www.domain.com/node/2/edit.

SELECT * FROM url_alias WHERE src = CONCAT('node/','2')

And then use the dst key in the object/array you setup for yourself to grab the value of the URL for that node (without the domain.com/ part). This might be better for maintance than be of much use in any custom module.

JOIN url_alias Using SRC Column’s Node ID

Usually you want to JOIN the URL to other data you’re pulling so you can manipulate the path. This will help for that.

SELECT * FROM node n JOIN url_alias ua ON ua.src = CONCAT('node/',n.nid)

Of course you’ll want to stipulate a filter using the WHERE, GROUP BY, or ORDER BY commands at the end now too, but I cant predict those for you. Also be careful of how you use a query like this. Its not very efficient. If you’re using it in a module and running it repeatedly you’ll want to setup caching on it.

URL Manipulation

Depending on the application I might be splitting the current URL into an array on the slash before I use it for something:

$current_url_array = explode('/',$_SERVER['REQUEST_URI']);

As well as the returned path from our query above:

$returned_url_array = explode('/',$result['dst']);

Maybe you’re just looking to compare the two paths? Note this assumes you had a LIMIT 1 on the query:

if ($result['dst'] == $_SERVER['REQUEST_URI']) {
 echo "You are currently on the page you queried";
}

Or search if the url retrieved is a blog URL:

in_array('blog',$returned_url_array);

But if you didnt explode the URL first then this would work:

preg_match('/(.*)\/blog\/(.*)/',$result['dst']);

This is just a piece of the puzzle and not meant as a full tutorial. It only introduces the concepts. If you’ve found a creative custom use for this yourself, please comment about it below!

Respond: Leave A Comment | Trackback URL

Entrupeners, Subscribe for the lastest tools, tips, and tutorials.


One Response to Obtaining The URL_ALIAS In A Drupal MySQL Query

  1. Jin

    In my drupal 7 instance, the column is called “source”, not “src”. For me, I had to change the sql to SELECT * FROM node n JOIN url_alias ua ON ua.source = CONCAT(‘node/’,n.nid)

Leave a Reply

Custom Theme by Rob Malon | Content & Design © 2010 - RobMalon.Com - Chicago, Illinois