如何避免PHP中的一些SQL查询？

Question

I am developing a PHP web app with jQuery and Twitter Bootstrap. And it uses AJAX for everything. So, I show a form in HTML5, the user press a button (class="btn"), the form is sent to PHP (jQuery, AJAX), PHP makes a query to the MySQL and echoes an answer, which is shown in the form (jQuery). This is basically how the web app works.

But here's the deal, the first form it is showed, it's a div that shows some news. For example:

 A new user was created.
 There is new important date.
 Someone wants to text you.

So I've created a table in MySQL called News where I saved some values than mean something like:

 1: A new user was created
 ...

Everytime the user log in will se that. It means that there will be a query and a response as soon as the HTML5 get loaded when a user log in.

The index.html file has a navbar (Bootstrap), and a option call News. When the user clicks it, the same query will be executed, but not necessarily the same response.

I thought in modifying the div with news whenever the user does an action. But, an action can also be done by another user. So it is necessary to make the query again!

Is there any solution that allows me to avoid querying the database when the user wants to get the news? Or how can I know that it is necessary to update the div right now? I was taking a look at caching queries but didn't arrive to a conclution.

Sorry if my english is not too good, it is not my native language.

Thank you.

Answer 1

You can send a timestamp in every news response from the server and save it in javascript. The next time you make a request, send the timestamp you saved and the server checks if there are more recent news, sending nothing if there is none as the last response is still the newest.

Well, there is a downside here, you still need to make a query to the database (filtring the results with a WHERE clause like 'WHERE ... TIMESTAMP > last_timestamp_from_browser') which is perfectly valid, SGBDs are designed for this, and if you don't have thousands of users accessing your website at the same time there will not be any problem. With this approach you will only save bandwitdh as the connection to the database is still made.

There is another way that prevents this connection from being made, cache some values of last news inserted which could be user specific or global and save them in APC module (or memcached). You'll need to discover what to cache and when (you can't cache the entire database, just some well organized timestamps and maybe the most requested news for example). This way you prevent the database connection from being made. This will force you to do many many more code, so, use it only if you really need it, like thousands of user connections at once.