fetching article from mysql using slug

Question

Hi all,

i am writing my own news article section on my site, and store each article in a mysql database. Each article has a unique id, a title, the main body and thanks to the jquery plugin, it's own url-friendly slug.

However, I am not sure how on earth I go about fetching the article when linking to the slug.

How do i get:

www.site.com/news/nice-looking-title/

to work like:

www.site.com/news/index.php?id=1

so that i can then use SQL to fetch the record from the MySQL table such as:

tbl_news:
news_id
news_title
news_slug
news_body
news_date

is the .htacccess involved in doing this?

As ever, all and any help is most appreciated! :)

paul

Answer 1

You'll need to change your SQL query to select by news_slug instead of news_id. A simple application could be as follows:

.htaccess

RewriteEngine On
RewriteCond %{REQUEST_URI} ^/news #only do this for URLs beginning /news
RewriteRule (.*) /news/index.php [L] #redirect control to index.php

PHP

$slug = explode('/', $)SERVER['REQUEST_URI']); // turn the path into an array
$slug = $slug[2]; // get the 2nd part of the array (nice-looking-title)
$query = sprintf ("SELECT * FROM tbl_news WHERE news_slug = '%s'", $slug); // new SQL query

NOTE: THIS HAS NOT BEEN TESTED SO MAY HAVE ERRORS!

Answer 2

ok here is what I have had to do to resolve this. Slightly hacky, but it works.

Having RewriteRule (.*) /news/index.php cause an infinite loop on the site, as did the fact that having the php page in the news directory. So i have had to send links to a different directory name, and rewrite them to the actual directory.

.htaccess

RewriteCond %{REQUEST_URI} /guild-news
RewriteRule guild-news/(.*)/ news/news-item.php?slug=$1
RewriteRule guild-news/(.*) news/news-item.php?slug=$1

PHP

$slug=$_GET['slug'];
$newsSQL = "SELECT *, DATE_FORMAT(news_date, '%W, %D %M, %Y') news_date_f FROM tbl_news WHERE news_slug = '".$slug."' AND news_visible=1";
$result = mysql_query($newsSQL, $conn) or die(mysql_error());

so probably not the best code ever, but it does work mighty fine for my needs :)