0

I'm wondering if there's any ability from the PHP side; to detected if the connected mySQL database has changed; or updated?

I would like to eventually print a time stamp if it was / has.

So, I have a connection file.

i.e.

$host = "BIGHOST:3306";
$user = "root";
$password = "TWIN@*";
$db = "bigDB_db88";

then in other .php files; I call it before queries.

i.e.

require_once('core/include/connection.php');

Context.

i.e.

I'm trying to print a time stamp in a .html file when the connected, dynamic database has changed or updated. i.e. 'Last Updated at'

Dr Upvote
  • 8,023
  • 24
  • 91
  • 204
  • Can you post a sample code of your connection? I think it can check by using `boolean` and some logic. – Roshan Feb 16 '19 at 00:53
  • Could you explain a little bit more about what your are trying to accomplish. Usually, you shouldn't look for update to your database, the service that update the database should notice other services that the data has changed. You could use socket or webhook to do so. – Nicolas Feb 16 '19 at 00:55
  • I'm trying to print a time stamp in a .html file when the connected, dynamic database has changed or updated. i.e. 'Last Updated at' – Dr Upvote Feb 16 '19 at 00:59
  • What does `changed` mean in this context? – Ibu Feb 16 '19 at 01:00
  • 1
    then you could save the updated time in the database and retreive it. You can do so using a trigger to update the `update_time` field to `NOW()` after any update – Nicolas Feb 16 '19 at 01:00
  • If you which to update other client of changes into your database, you could use websocket to make a realtime connection between you backend and your users, and each time someone updates the data, you send and update message to every connected sockets. – Nicolas Feb 16 '19 at 01:01
  • Each client then retreive the updated data from the server. That's how i would do it. – Nicolas Feb 16 '19 at 01:02
  • @Nicolas thanks; that seems like the approach I will pursue - can I write this from the PHP side? i.e. something like $sql = "INSERT INTO – Dr Upvote Feb 16 '19 at 02:08
  • What part ? For the trigger it's in MySQL directly, but it's something you setup one time and it's run at every query ( [see exemple here](https://stackoverflow.com/questions/6296313/mysql-trigger-after-update-only-if-row-has-changed)) for the Websocket part, i suggest you look into [Ratchet](http://socketo.me/) a websocket library written for PHP. – Nicolas Feb 16 '19 at 02:48
  • Not all database types support update time information via TABLE_SCHEMA by default. Yes, you can add a trigger to set that information when a INSERT/UPDATE/DELETE happens, but in this case I would just write a short script that simply uses a single file that stores that information by accessing the database DATA directory directly and checks each database/table file for the last time it was modified. And NO that won't tell you when the last INSERT/UPDATE/DELETE happened @ time individually, but it will tell you when a database or a database table has changed! – Stephanie Temple Feb 16 '19 at 05:20
  • @StephanieTemple can you elaborate on this solution? What do you mean by 'using a single file'? What file? There is not a file that stores that information (to my knowledge lol) hence the question! I like the concept of your idea; but I just don't understand what you mean here..... – Dr Upvote Feb 17 '19 at 22:51

1 Answers1

0

Not sure if this is what you are asking... You can try:

SHOW TABLE STATUS FROM your-db-name;

The query will return Update_time for each table.

You can limit result set (to exclude views or federated tables for example) by adding WHERE clause:

SHOW TABLE STATUS FROM your-db-name WHERE Engine IS NOT NULL AND Update_time IS NOT NULL;

fifonik
  • 1,556
  • 1
  • 10
  • 18