Null recordset

[Brent_GOT]

Hi all,

I'm very new to php/javascript etc but have made some good headway. I have a php class "functions" which amongst many things has a public function that checks a table in a mysql database for any new "instruction" records. This is triggered from a javascript function every 2 seconds. The entire process is working perfectly. If I have a record in the instruction table it retrieves it perfectly and then deletes it and returns the instruction perfectly. The problem comes in in that it then triggers 2 seconds later and returns the exact same instruction (I do set the instruction variable to '' before retrieving the record and it echos as '' before being set by the retrieved record). It's as if the recordset is cached instead of being refreshed. The same happens if there are no instructions and then I create one in phpadmin, it returns '' (ie the new instruction is not retrieved). I do free the query after each poll etc. The connection is effectively permanent and I'm not getting any errors at all. Does anyone have any ideas on how to rectify this? Ps the deletion in the table is also working perfectly if an instruction is found. There aren't any records remaining either (my basic test process to ensure things are working correctly). 

Edited July 24, 2022 by Brent_GOT

[Barand]

try adding a creation timestamp to the instruction records then query

select created_time, instruction ... where created_time > :last_time_retrieved

You then have a different query each time which may prevent the caching effect. It also means you won't have to delete records enabling you to maintain a history of instructions.

[Brent_GOT]

I'll try that. I'm a little perplexed why php doesn't requery correctly though, I like to understand as I've never encountered this with other languages that use mysql. Do you perhaps know why? 

[Barand]

I suspect (as I implied above) that it's because it is repeatedly receiving the same query. With the time condition it changes.

[Brent_GOT]

Turns out Ajax was the only way. Got it working properly now. Txs for suggestions.