Get newly-added rows from MySQL using PHP

I have a table posts with columns post_id, user_id, post.

When a user visits his page, I retrieve a list of his posts (select post from posts where user_id=$uid开发者_StackOverflow). He can then add one or more posts to the table through a form.

Is there any way I can retrieve these newly-added posts without querying the entire table again?

I'm particularly interested in using this with jQuery to auto-refresh his posts periodically (like YouTube comments when 'auto-refresh' is selected, for example).

Thanks, Albert

If I were you, I would keep track of when the posts were made, e.g.

post_id, user_id, post, made

You can use AJAX to ping a script to check for posts made since the last ping~

query you db like this:

"select * post from posts where user_id=$uid order by post_id desc limit 0,20"

this will get the most recent 20 posts.

Assuming the 'post_id' field is an auto increment int, you can keep track of the largest value ID when you first load the page. For subsequent jquery ajax requests, just do a

SELECT * FROM posts WHERE user_id = '{$user_id}' AND post_id > '{$last_id}' ORDER BY post_id ASC

If the user is submitting posts on the same page that you want to update, why not just update the page as soon as the post is successfully submitted (via ajax)? You avoid re-fetching information you already know, and you avoid re-querying the database all together.

You would dynamically add a post to the page as it is submitted.

function onPostSubmitted(post){
    // AJAX submit of the post

    $("#posts").prepend( $("<div />").text( postData ) );
}