SQL required to showthe highlights of a database on a website homepage

Question

Hi,

Wikipedia http://en.wikipedia.org/wiki/Main_Page , has sections like:

1. Todays featured article,
2. From the news,
3. Did you know etc.

Say in my page, I want to get the main highlights from the database table(s) (multiple databases possible), what is the best possible way to query? I mean create separate connections and then query or use multiple queries? Is it better to use PDO for this purpose? And how can I make a particular section update without refreshing the page say every 10 min? Is the code going to be complicated?

Can anyone please let me know.

Thanks.

Answer 1

If you want to refresh the data, you have two options.

First of all, you could write JavaScript to run away after a set interval and get fresh data. This is a VERY BAD idea - it exposes you to browser specific bugs, it won't work on machines with JavaScript disabled, and far more importantly it means you'll be furnishing your pages with the connection details for your database (and also allowing connections to it from anywhere).

A better solution would be to use AJAX to handle this - which basically means that you serve "just the data" rather than the whole page. Again, it's dependent on Javascript, but then implementing client side behaviour without the benefit of client side scripting will never work anyway!

Martin.

Answer 2

yes the code is going to be complicated but not difficult.

If you want to use PDO then you should use it, it depends if you want to use it or not.

First you need to decide the highlights that you want to show in the main page and then decide how to fetch this related info.

You can use multiple queries. First fetch and then display.

And how can I make a particular section update without refreshing the page say every 10 min
for this you will have to use ajax.

Answer 3

#1 The todays feature is either calculated from the current date; or specified, either manually or via a randomizing script which saves todays' date together with the id, so it will stick for the rest of the day.

#2 From the news is a simple query with order by date desc from the news.

#3 Did you know says it's from their recently added articles, which would also be somewhat of an order by date desc-query.

PDO is just a wrapper for the same SQL-queries as usual, you can achieve the same result without it.

About updating a section, I'd use a cache with a 10 minute limit. This will not reload for people staying on the page for 10 minutes (for that you would need AJAX) but it will load fresh content on a timebased period. You should base your choice of whether you expect users to spend 10 minutes on the same page, or just give some news at least 10 minutes in the spotlight before they get exchanged.