PHP & MySQL mod_rewrite

Server Config
1

I have built a site using a database for content. One thing I wish to fix is the news.
I allready us .htaccess to change .php into .html using RewriteRule ^(.*)\.html $1\.php

Now I have a news page with links to other news articles however they are displayed as [noparse]http://www.mysite.com/news.html?id=6 then when I display it i get the id and the the news article from the db. I wish for it to show this in the url http://www.mysite.com/news/new-news-title.html[/noparse]

In my database i have fields of id,title,article,date and permalink

The permalink is made from the title removing spaces and adding a dash instead. Also making everything small letters.
So a title of “this is news 4 you today” becomes “this-is-news-4-you-today”
For now I want the URL to show the permalink i created, but i also want to have it so when you hover over a news link it will show the desired url instead of the ?id=6 etc

Any help would be great
I need a good tutorial because I get confused reading everything
Pierce

2

CP,

The tutorial you’re after is linked in my signature where I describe why you can’t convert id=6 to “this-is-news-4-you-today” (i.e., Apache is not clairvoyant) and how to work around that using PHP to convert your links (similar to what you’ve done). Additionally, I’ve often displayed the mod_rewrite code I use for a client at http://wilderness-wally.com/ where I simply use titles (after removing reserved characters and converting spaces to _'s [to allow the client to use a hyphen in his titles]). If you search and can’t find ‘wilderness-wally’, please ask for the mod_rewrite again - but ONLY after reading the tutorial which explains it all (because I won’t code for ‘script kiddies,’ I insist that a valid attempt is made to create your own code then I’ll provide kudos for what was correct and explain for everyone why something would be wrong … or not optimal).

Regards,

DK

3

I have looked at your tutorial / help and it is useful but im sorry I just cant get my head around it all.
I do understand that I cant use the mod rewrite to change the news.html?id=2 to news/news-article.html.
I also know clearly that i cant just drop in the url from my db to make the users link look like that news/news-article.html and my php would be

<?php
echo "<a href=\\"news/$permalink.html\\">$newstitle</a>
?>

I looked at having it so i could use the news title instead of the id so it would be

<?php
echo "<a href=\\"news.html?title=$newstitle\\">$newstitle</a>
?>

but then i could end up having two news titles with the same name

So I started again to keep it simple for myself I have two files (index.php and news.php) the index is simpily a link to the news.php page. I do have a mod rewrite so change the .php to .html in the url in withing the document the link itself is a href=“news.html” instead of “.php”
In my db i called test i have 1 table called news with fields of (id,title,article,permalink,date,hide)
The news page will list the news titles and date posted. Here im stuck again the link is built <a href=\“news.html?id=$newsid\”>$title</a> and shows up as news.html?id=2
I KNOW I cant use a permalink because of the 2 same title problems also how does one perform the sql query
This is my news page. I dont wish for anyone to write my code for my but please any extra help would be greatly appricated as this is starting to play on my mind so much

<?php 
    include("db_connection.php");
    $sql = "SELECT * FROM news WHERE hide = 0";
    $result = mysql_query($sql);
    if(!$result){
        echo "No results found";
    }
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<?php 
    $newsid = $_GET['id'];
    if(isset($newsid)){
        $sql = "SELECT * FROM news WHERE id = '$newsid'";
        $result = mysql_query($sql);
        while($row = mysql_fetch_array($result)){
            $title = $row['title'];
            $article = $row['article'];
            $date = $row['date'];
            $op = "<h2>$title</h2>\
";
            $op .= "<p><em>Posted on $date</em></p>\
";
            $op .= "<p>$article</p>\
";
            $op .= "<hr />\
";
            $op .= "<p><a href=\\"news.html\\">Back To News List</a></p>\
";
            echo $op;
        }
    }else{
?>
<table cellspacing="0" cellpadding="10" border="1">
    <tr>
        <td>News Title</td>
        <td>Date</td>
    </tr>
    <?php 
        while($row = mysql_fetch_array($result)){
            $newsid = $row['id'];
            $title = $row['title'];
            $date = $row['date'];
            $op = "<tr>";
            $op .= "<td><h2><a href=\\"news.html?id=$newsid\\">$title</a></h2></td>";
            $op .= "<td>$date</td>";
             $op .= "</tr>";
            echo $op;
        }
    ?>    
</table>
<?php } ?>
</body>
</html>
4

CP,

Indeed, there is so much there and the bit about id vs title is so small that it would easily be overlooked!

Because you’re attempting to do what I did for http://wilderness-wally.com, i.e., use titles for the links EVERYWHERE, you can do what you were writing about by some minor thought process changes:

  1. Your articles are currently linked by id. Get that out of your head!

  2. Ensure that your titles are UNIQUE in your database, i.e., SELECTing using a title will not return multiple records. UNIQUE is a feature of the MySQL database and has been for many years.

  3. Create your links using the titles, i.e., <a href=“<?=$title2?>” title=“<?=$title1?>”><?=$title1?></a> where $title1 is the title in the database with all the spaces, etc. There are some characters which you must NEVER use (because they are reserved in a URI) but the spaces are your main concern. $title2 is the same as $title1 with the spaces replaced by another character (I use 's because underscores do not occur in natural text whereas -‘s do occur naturally). <?php $title2 = str_replace(’ ‘,’',$title1); ?> will make that conversion for you.

  4. LOOK at http://wilderness-wally.com’s links. They are all as described above, i.e., http://wilderness-wally.com/Wally’s_Front_Porch works to bring up his “Wally’s Front Porch”-titled article because …

  5. When a request is processed by mod_rewrite, the title is captured (because it doesn’t match a file or directory [and uses ^([-a-zA-Z0-9_&'!\…]+)$ for the RewriteRule’s regex]) and is used as the query string for the display script.

  6. The display script captures the query string and converts the _'s back to spaces with another str_replace directive BEFORE querying the database looking for a match for the title (rather than the id as you’d been using).

  7. “Easy-peasy” and “Bob’s your uncle” (or aunt as the case may be) and you’ll have a site as sweet as Wilderness Wally!

Regards,

DK

5

Thank you I am starting to so of get my head around this.

I have my links nearly exactly the way you have shown it my line is as the following
$op .= “<td><a href=\”$title2\" title=\“$title1\”>$title1</a></td>“;
Yours is <a href=”<?=$title2?>" title=“<?=$title1?>”><?=$title1?></a>
I think these are the same.

Im assuming here that your <?= ?> are the tags. I think im right here.
And if so I am looking at yours I dont see where the ?=title is, just title= (will that create a query string?)

It is making the link display the way i wish but now I dont know how to do the query now.

Is the link im expecting
http://localhost/tutorials/rewrite/news/news_title.php or
http://localhost/tutorials/rewrite/news/title.php

And another thing the rewrite line you gave RewriteRule ^([-a-zA-Z0-9_&'!\…]+)$ this will give a Server error! Error 500
So i am asuming i am doing this wrong

This is what I have at the moment
.htaccess
#Options +FollowSymlinks
RewriteEngine on
RewriteRule ^([-a-zA-Z0-9_&'!\…]+)$

And news.php is
<?php
include(“db_connection.php”);
$sql = “SELECT * FROM news WHERE hide = 0”;
$result = mysql_query($sql);
if(!$result){
echo “No results found”;
}
?>
<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Strict//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd”>
<html xmlns=“http://www.w3.org/1999/xhtml”>
<head>
<meta http-equiv=“Content-Type” content=“text/html; charset=utf-8” />
<title>Untitled Document</title>
</head>
<body>
<?php
$title = $GET[‘title1’];
if(isset($newsid)){
$sql = “SELECT * FROM news WHERE title = ‘$title’”;
echo $sql;
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)){
$title = $row[‘title’];
$article = $row[‘article’];
$date = $row[‘date’];
$op = "<h2>$title</h2>
";
$op .= "<p><em>Posted on $date</em></p>
";
$op .= "<p>$article</p>
";
$op .= "<hr />
";
$op .= "<p><a href=\“news.html\”>Back To News List</a></p>
“;
echo $op;
}
}else{
?>
<table cellspacing=“0” cellpadding=“10” border=“1”>
<tr>
<td>My News Link</td>
<td>Your News Link</td>
<td>Date</td>
</tr>
<?php
while($row = mysql_fetch_array($result)){
$newsid = $row[‘id’];
$title1 = $row[‘title’];
$title2 = str_replace(” ", "“, $title1);
$date = $row[‘date’];
$op = “<tr>”;
$op .= “<td><a href=\”$title2?\” title=\“$title1\”>$title1</a></td>";
$op .= “<td>$date</td>”;
$op .= “</tr>”;
echo $op;
}
?>
</table>
<?php } ?>
</body>
</html>

Thanks again for your help it is slowly sinking it im sorry im not getting it quicker.

6

CP,

Oh, my!

May I also recommend that you search for articles which “safe” text before saving to a database and recovering the article from the database before displaying it?

Regards,

DK