Learning A Bit

PHP, SEO and MySQL Walk into a Bar…

Ok, maybe they don’t walk into a bar, but meet up on a website. In this case, it’s a site for work.

This new website has a section for articles, similar to blog posts. Creating the articles and accessing them were “easy” (I will not go into detail about how to access a mysql database with php, as there are plenty of demonstrations that explain this very well. But I will allude to it later).

A page with a list of available articles is created, and I wanted to create a dynamic page that the articles would populate, which was done. The links created to access these articles had a query string after the page name that accessed the database with a template, like this… 

https://example.com/article/view.php?id=1

Which is still less than optimal for for SEO purposes.

I located multiple posts that explained how to alter the link using the .htaccess file, but they were incredibly convoluted, which included creating an intermediary page that involved making a table to be referenced. This may have worked, but I kept thinking, “There has to be a simpler way…”

Getting to the next step took looking more directly at the .htaccess file, with the rewrite rule (which was also involved in the “convoluted” solution). Posts at 24ways.org and mediatemple.com were great resources.

For the link to be SEO friendly, they needed to use a url that would describe the article with “normal” words (with dashes), rather than a query string. So I came up with an idea… Each article will have a unique name and title, create a unique url (based on the title) and include that in the database. And then, search the database for the unique url instead of the id!

TADA, it works!!! This has a two fold benefit. You can create the linking url from the list of available articles with an SEO friendly structure, and it will pull up a unique article! All with no intermediary page of having to build a new table.

Instead of having to use this link to access an article, and have it as an active page on the site, 

https://example.com/article/view.php?id=1

it would be…

https://example.com/article/welcome-to-the-future.html

Here is pretty much what I did… (in a simplified structure). Fields in the database table…

id
name
url
content

Below will be the page that lists all of the visible articles, using the “name” field for the text of the link, and the “url” of the href. (Again, at this point, the way to access the database is not being covered in detail, see W3Schools).

(access the database and do a query of all articles)

<?php
$result = 'SELECT * FROM <table_name>';  

// this will go through each item in the returned array, and display a list of articles, creating a link 

    foreach($result as $value) {
        echo '<a href="/article/'.$value['url'].'.html">'.$value['name'].'</a><br>';
    }
?>

On the template page, php will “GET” the portion of the url that corresponds to that field of table and load the article object.

article.php

(do a query where the url from the link on the article-list.php page is searched in the table. “url” in brackets accesses the relevant portion of the domain/url that is unique to url field in the database table)

<?php
    $result = "SELECT * FROM <table_name> WHERE url = '{$_GET['url']}' ";

    // there may be another way to do this, but I was forced to iterate over items in an array, and then access that value. There should only be a single value
    if ($result->num_rows > 0) {
        foreach($result as $value) {
            echo 'You are seeing details for <b>'.$value['name'].'</b>;
        }
    } else {
        // redirect to 404 page
        header("Location: /404.html");
        exit();
    }
?>

And now for the last bit of magic. In the .htaccess file, turn on the rewrite rule. The breakdown of the rule is the left side is the SEO friendly link (with a regex expression), and the right side is the behind the scenes portion that is searching the database with data from the SEO friendly part.

.htaccess

RewriteEngine On
RewriteRule ^article/([a-zA-Z0-9-/]+).html$ article/article.php?name=$1 [L]

And that’s all I had to do! Three pages – 1) A list of available articles. 2) A template page that pulls data from the database for said article, and 3) A rewrite rule in the .htaccess file!

It took me a little while to get to this point, as I was relying too much on other peoples solutions. (slightly presumptuous ->) Sometimes its better to get a base understanding, and see what works for you.

learnabit

, , ,
, , , ,