How Would You Query a Post in Database by Title Only?

[MarcyS]

I am just curious how one would go about this.. 

 

Let's say I have a mysql table called articles and 3 columns: postid, title, content

 

...and I wanted to format my url to look like mysite.com/this-is-my-title

 

I understand that the normal approach is to query for postid and include that in the url.. and probably use a rewrite rule in htaccess, but is it possible to simply query the database for the title only and append to the url after .com/  ?

 

I understand that in such a case, every title would have to be unique, but still curious about the approach. Since the url would have to be sanitized and dashes added, can you still make a request by title?

 

Thanks!  

[mentalist]

Look at the address bar on this page... an id with title, otherwise you are going to need to check for uniqueness before adding a new page title to the db.

[Ch0cu3r]

Yes you can do this.

 

However in the url I'd contain both the post id and the post title, like how this forums url are (the 283442 is the topic/post id and the text after it is the topic title). That way the your existing code that gets the post from the database doesn't have to be changed. The only the code needs changed is when you make the links.

 

For example you have code like the following for outputting post links like site.com/post.php?id=123

echo '<a href="site.com/post.php?id='.$post_id.'">'.$post_title.'</a>';

To have links like site.com/123-the-post-title. You'd use something like the following for outputting the formatted links

$post_url_title = str_replace(' ', '-', strtolower($post_title)); // convert to lowercase and convert spaces to dashes
$post_url_title = preg_replace('~[^0-9a-z_-]+~', '', $url_title); // strip out any punctuation characters
echo '<a href="site.com/'.$post_id.'-'.$post_url_title.'">'.$post_title.'</a>'; // output formatted url

The following rewrite rule will capture these urls and call post.php

RewriteEngine On
RewriteRule ^([0-9]+)-([a-z0-9_-]+)/?$ post.php?id=$1&title=$2

In post.php you'll still get the post id using $_GET['id']. To get the url formatted post title you can use $_GET['title'].

Edited October 30, 2013 by Ch0cu3r

[MarcyS]

What a great explanation. Thank you, Ch0cu3r. 

 

My only confusion.. and what got me thinking about a situation such is this.. how does a site like upworthy.com manage to pull only the domain name followed by the post with no other identifier in the url? 

[Ch0cu3r]

They will be querying the database and returning the record that matches the url for the article/page.
 
You can of course do this too if you so wish. If you want to go this route then add an extra column in your table that holds the url for the post. You'd then change your query to something like

$post_url = mysql_real_escape_string($_GET['title']);
mysql_query("SELECT post_id, post_title, post_content, etc... FROM posts WHERE post_url = '$post_url'");

The following rewrite rule will match urls like site.com/page-title-here

RewriteEngine On
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^([a-z0-9_-]+)/?$ post.php?title=$2

Edited October 30, 2013 by Ch0cu3r

[vinny42]

 

...and I wanted to format my url to look like mysite.com/this-is-my-title

 

Which would be the only right way to do it. Allmost.

 

The reason why the ID is often spammed into the URL is because of a common database design fault: the surrogate primary key.

Instead of making a proper primary key that contains all that makes the record unique, they just spam an ID kolumn in the table and start using that.

 

A proper PK would be for example the title itself, but titles are usually re-used for multiple articles so a date or even a datetime would be a good addition. Serialnumbers are not.

 

News sites and even shops will have URL's like; www.news.com/Programming/PHP/2013/10/30/Breaking_news_in_the_morning.html

The categories, date and the title are the PK, no messy ID's required, the entire URL is pertinent to the article; it's an article in the PHP subcategory of Programming, it was posted on the 30th of the 10th month of 2013 and it's called "Breaking news in the morning". The only problem you can habe now is that two articles cannot have the same name during the same day, which would be confusing to the readers anyway.

The URL is also choppable; if you remove the title the website can list all articles of that day, if you also chop the day it can list the articles of that month, etc. 

 

 

If you want to go this route then add an extra column in your table that holds the url for the post. 

 

That's not required, and if you do do that, make sure the URL is filled automatically from the title, and use it as a cache. Never let humans write URL's because they will come up with things like "Breaking news: are we all going on holiday?? Say Smith & Clarkson" And boom goes your website.

[MarcyS]

 

They will be querying the database and returning the record that matches the url for the article/page.

 

You can of course do this too if you so wish. If you want to go this route then add an extra column in your table that holds the url for the post. You'd then change your query to something like

$post_url = mysql_real_escape_string($_GET['title']);
mysql_query("SELECT post_id, post_title, post_content, etc... FROM posts WHERE post_url = '$post_url'");

The following rewrite rule will match urls like site.com/page-title-here

RewriteEngine On
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^([a-z0-9_-]+)/?$ post.php?title=$2

 

Great idea!  I could add a new column and sanitize it before storing it in the database so it's displayed in the exact way it's stored. I'm so new to php and programming in general so trying to learn.  

 

Thanks a bunch for the clear explanations.. really helped me a lot! 

 

Much appreciated!

[Ch0cu3r]

 

Great idea!  I could add a new column and sanitize it before storing it in the database so it's displayed in the exact way it's stored.

If you are doing that you don't want to be storing any punctuation characters for the url. You'll want to do something like this

$post_url_title = str_replace(' ', '-', strtolower($post_title)); // convert to lowercase and convert spaces to dashes
$post_url_title = preg_replace('~[^0-9a-z_-]+~', '', $post_url_title); // strip out any punctuation characters

So the urls for your posts can only contain letters, numbers, underscores and dashes. 

Edited October 31, 2013 by Ch0cu3r

[MarcyS]

If you are doing that you don't want to be storing any punctuation characters for the url. You'll want to do something like this

$post_url_title = str_replace(' ', '-', strtolower($post_title)); // convert to lowercase and convert spaces to dashes
$post_url_title = preg_replace('~[^0-9a-z_-]+~', '', $post_url_title); // strip out any punctuation characters

So the urls for your posts can only contain letters, numbers, underscores and dashes. 

Thanks!  I copied this down and plan to use it!