How to query new records?

Hi,I am confuse on how to query the newly inserted record.I have a javascript that check to my database if there is newly inserted records via ajax.then the response is I append it dynamically to the table row.but the problem is, it is still fetching the record even it is not newly inserted.I am confuse how can I know if that records is newly inserted.

my code is just like this

select * from table
…inner join


order by id desc LIMIT 1

Thank you in advance.

Keep a note of the highest ID in your table, and add “where id > old_id” to the query?

I need to create a new table to store the id’s?

Keeping track by highest id is a terrible way to do it, especially if you use a system that is based on a sequential instead of an autoid.

Do you have a created date field? This would be the easiest way to get the last inserted record. It’s a good idea to have a “created” column on every insertable table.

you mean to create a column field “created” =>timestamp?

Yes.

ALTER TABLE table ADD COLUMN created TIMESTAMP DEFAULT now();

That should work.

Then:

SELECT * FROM table
....
ORDER BY table.created DESC
LIMIT 1

Actually, I found this looking up some of the sytax. LAST_INSERT_ID() which will return the last inserted auto-incremented. However, adding in a created timestamp is the most flexible way and it’s usually good to have it there just in case.

SELECT last_insert_id() FROM table;

@mawburn,in created colum,so what if there is No newly inserted ?does it still fetching the last record?

Ah, I see that now:

even it is not newly inserted.

I missed that part. Yes, grabbing the last inserted date will get the last inserted so you will need to do something like @droopsnoot mentioned, but with the dates instead. Send the last date you have in you your Javascript to your query and get everything that comes after it.

Ok, Thank you @mawburn

Out of interest @mawburn, why is keeping track by highest ID a bad way? I must admit I assumed an autoid, but making that assumption, what’s the problem with it?

Because Autoincrmented ID’s are not made for numerical sorting.

I could be wrong, I can’t seem to find anything to back me up but I’ve read about it in the past. Perhaps @r937 has an answer?

Ah, the natural and artificial key debate lingers on still :stuck_out_tongue_winking_eye:

How are you defining “newly inserted”. Newly as compared to what?

I can query 1000 times for new entries, but if i’ve got nothing to say “anything _______ than _____ is ‘new’”, then i’m just pulling data for data’s sake.

[quote=“mawburn, post:11, topic:97775, full:true”] Perhaps @r937 has an answer?[/quote]auto_increment should be used only for uniqueness

OK, I just wondered. My signature used to say ‘learning PHP’ so I had no significant opinion one way or the other, I mainly read on here to pick up knowledge. Not sure I particularly understand the response from @r937, but reading the post from @Starlion it seems it’s a can of worms, so I’ll have a look around for more reading.

[quote=“droopsnoot, post:14, topic:97775, full:true”]Not sure I particularly understand the response from @r937
[/quote]i’d be happy to go into more detail

which part didn’t you understand… the uniqueness bit?

Is this still going about the newest record in the database?

Anyway there are several ways of doing this. In one case I used a counter column (first row in the table) and checked every x seconds if that value had changed. A more popular method is using a timestamp.

No, I’m familiar with the concept of uniqueness, I just wondered why an auto-incrementing id was a bad candidate to do the job the OP asked about. I didn’t want to derail someone else’s thread opening up what sounded like an argument without a conclusion. The timestamp, as long as it’s unique, is obviously a more useful bit of information to store as a ‘created’ or ‘updated’ date, at the expense of taking up unnecessary space if the only thing it gets used for is to identify records that have been added since a page was last drawn or a user last logged in.

I don’t trust lastInsertId() at all. When running multiple simultaneous instances of the same cron script, it would often return an insert ID generated in another copy of the script. My workaround was to insert a unique ID into a UNIQUE column other than the auto-increment column, then do a SELECT query to get the auto-increment ID where the known unique ID matched what I just inserted.

@diggy_dude - Your script is doing something wrong. lastInsertId will always return the id of the last inserted record for a given connection. You do need to call it before anymore insert operations. But it is 100% reliable. Each instance needs to have it’s own database connection.

@ahundiak

Is there some connection sharing amongst script instances with Zend_Db that I’m not aware of?