Jump to content

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


MarcyS
Go to solution Solved by Ch0cu3r,

Recommended Posts

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!  

Link to comment
Share on other sites

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 by Ch0cu3r
Link to comment
Share on other sites

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? 

Link to comment
Share on other sites

  • Solution

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 by Ch0cu3r
Link to comment
Share on other sites

 


...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.

Link to comment
Share on other sites

 

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!

Link to comment
Share on other sites

 

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 by Ch0cu3r
Link to comment
Share on other sites

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! 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.