Friday, March 14, 2014

Now showing "Related Quotes" in our Motivational Quotes Website



As explained in my previous posts, we are taking lot of steps to add many useful features to our Motivational Quotes Website TheQuotes.Net

 But it is not yet getting Search traffic from Google.  According to Google Analytic Report, the search traffic from Google to the TheQuotes.net website got reduced significantly on May 25th 2012 and September 29th 2012. These dates correspond to the Google Algorithm updates dates. So,  It seems the site got affected by both Panda and Penguin Updates of Google.

For recovering from Penguin attack, I am taking steps to remove links from low quality websites and from  footer of our other websites.


For addressing Panda related issues I am taking steps to  correct "Thin Content" issues. For example, I have added "noindex, nofollow" meta tag to many pages like greetings cards editing pages, and search results page.

And, I removed some adsense units from some pages to keep good "ads to content " ratio.

For example, this page was having only one sentence as content. Initially I thought of adding "noindex, nofollow" to these kind of pages. But I changed my decision as these pages are getting few visitors from other search engines such as Bing.

http://www.thequotes.net/quote/9-Unknown-Courage-A-ship-is-safe-in-harbor-but-that039s-n.html


So, I decided to show  "Related Quotes" in this type of pages. i-e Showing few other quotes from the category of the quote displayed on that page.

First, I used below query to show 5 random quotes from the specified category.

"select distinct q.id,q.category_id,c.category ,q.author_id,q.quotes,a.author from quotes_text q, quotes_author a , quotes_category c where q.status='active' and q.category_id=c.id and q.author_id=a.id  and q.category_id=$category_id and q.id not in($id) order by rand()limit 5"
 It was working properly. But this approach had two issues. The pages took more time to load, because of the use of random in the query. And, I am not sure how Google will treat the frquently changing content.

So, I decided to show the quotes without using random. If I show first few quotes from the category, all the quotes of that category will be having same set of quotes. It will again create the duplicate content issue. To handle this issue, I decided to use the below query so as  to show the few quotes  from below and above the current quote. I thought it will solve the page loading time issue as well as duplicate content issue.

select distinct q.id,q.category_id,c.category ,q.author_id,q.quotes,a.author from quotes_text q, quotes_author a , quotes_category c where q.status='active' and q.category_id=c.id and q.author_id=a.id  and
(
q.id in (select  id from  quotes_text where  status='active' and category_id=$category_id  and id>$id  order by id  asc limit 3)

or q.id in (select  id from  quotes_text where  status='active' and category_id=$category_id   and id<$id order by id desc limit 3)
  )
But this query didn't work. Mysql server was giving below error message.

#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

So, to solve this error I have rewritten the query as below, by following this suggestion.
select distinct q.id,q.category_id,c.category ,q.author_id,q.quotes,a.author from quotes_text q, quotes_author a , quotes_category c where q.status='active' and q.category_id=c.id and q.author_id=a.id  and
(
q.id in (
select * from
(select  id from  quotes_text where  status='active' and category_id=$category_id  and id>$id  order by id  asc limit 3) as t1)

or q.id in (
select * from
(select  id from  quotes_text where  status='active' and category_id=$category_id   and id<$id order by id desc limit 3) as t2)
)
Now I hope everything is working fine. Let me know if you find any issue when using our motivational quotes website TheQuotes.Net.

You can subscribe to our Email posts, and you can subscribe to our blog feed.

No comments:



Thanks for visiting our blog


twitterrss facebook linkedin YouTube subscribe


Subscribe via email

Enter your email address: