Recently I created a MySQL database for a CMS for websites that promote "venues". Each of these websites could be accessed via any number of domain names and I needed a way to tie incoming URL request to the right Venue.

To match a request to a venue I added a table called "Domains", which looks like this:

Notice how each of the 4 venues has multiple web addresses, but always only has one preferred address.

In PHP we can use the variable $_SERVER["HTTP_HOST"] to find the domain of the incoming request. We can then find the target Venue from the above table by using the following SQL:

SELECT venues.*, domains.domain as preferred_domain
FROM venues 
INNER JOIN domains ON venues.venue_id=domains.venue_id
WHERE venues.venue_id = 
        (SELECT venue_id FROM domains WHERE domain='www.royaloakpub.org') 
AND domains.preferred=1;

The trick here is the nested SELECT statement inside the WHERE clause. A new one on me. My approach to mastering SQL is to assume everything must be possible. This one had me stumped at first for a while, but I persisted and found the solution in the end.

The above SQL tells us - for each request - what Venue we're dealing with and, also, what the preferred domain for that Venue is.

By running the above query in PHP you can assign a value to a  PHP variable called $VenueID and then, for all subsequent queries to the CMS, you can append the Venue's ID to the WHERE clause. All tables in the CMS I made have a column called venue_id. So, for example, here's the SQL to get all the photos for one of the venues:

SELECT *
FROM photos
WHERE photos.venue_id = 2;

As an example of using this approach, notice how www.royaloakpub.org serves the same content as www.peakpub.co.uk. Notice in each site's HTML source that the canonical link meta-tag always points to the preferred domain, which is peakpub.co.uk in this case.

Ideally each Venue's website should always have a single domain name in use, but, you know how it is, it's never that simple is it? To get round the negative SEO effect in this case I used a meta tag but I could just as well have done a 301 redirect if the preferred domain weren't used in the original request.