asked on

If there is any change made to the table a javascript function called!

Hi how to know any change happen to the table form the sqlite database
So any new row added the database i got a notification, for now i can make a push notification via javascript but i don't know how to know if a new row added

This is my code a simple table with small javascript to refresh the table with new data using load

<div class="tbl-header">
<table cellpadding="0" cellspacing="0" border="0">
<thead>
        <tr>
          <th>IP</th>
          <th>Email</th>
          <th>Phone</th>
          <th>Seller</th>
          <th>Item</th>
		  <th>Code</th>
        </tr>
</thead>
</table>
</div>
<div class="tbl-content">
<table id="infos" cellpadding="0" cellspacing="0" border="0">

</table>
</div>
<script>
window.setInterval('refresh()', 1000); 
function refresh(){
$('#infos').load ('streets.php', 'update=true');
}
</script>

Open in new window

It get the data from streets.php every 1 second
and this is the PHP code of streets.php to get the the data from sql database ( data.sqlite ) and show it as a table

<tbody>
<?php
$data = new SQLite3('data.sqlite');
$result = $data->query("SELECT * FROM userdb"); //  WHERE ip ='$ip'

while ($row = $result->fetchArray()) {
    echo "<tr>
            <td>{$row['IP']}</td>
            <td>{$row['Email']}</td>
            <td>{$row['Phone']}</td>
            <td>{$row['Seller']}</td>
            <td>{$row['Item']}</td>
			<td>{$row['Code']}</td>
          </tr>";
}

?>
</tbody>

Open in new window

I need to know if it is possible to do know any update happen and call a javascript function if there is a new row added

Julian Hansen

Not by default no.

What you are looking for is a database solution with real time updates - like those provided by Google Firebase.

For this to happen you would need to create a service on the server side that monitors the database for changes and then uses sockets to communicate these changes back to event listeners on the client. For this to work you would need a database that provides event functionality that can be attached to so you can relay changes back. I doubt this is something SQLLite supports.

Have you had a look at Firebase? It is a NoSQL database - which takes some getting used to and is not a solution for every problem but it is a mature platform based on realtime updates.

I have seen some implementation proposals using Oracle and Socket.io with Node.Js but I don't have any experience with those.

leakim971

i don't know how to know if a new row added

you need
1) create a trigger on the table you're supervising : https://sqlite.org/lang_createtrigger.html
maybe you want to create a trigger for each type of event (insert, update, delete) not only when a new row
2) create another table for logs, the triggers update this table, this table have a column let's call it "seen" by default the value is set to 0

to know if there's a new row, check(setInterval) the logs table for seen with the value 0, once you get it, update the logs (for only the primary key(s) you get with seen to zero)

Julian Hansen

to know if there's a new row, check(setInterval) the logs table for seen with the value 0, once you get it, update the logs (for only the primary key(s) you get with seen to zero)

@Leak, I interpreted the question to be how to move away from polling the database for changes - i.e. to work more like Firebase where db changes trigger server side event which then notify event listeners through sockets of the change. Push rather than poll?

leakim971

@Julian you right about polling/pushing. Currently he load the full table because he doesn't have a way to know when the table change. I just want to show a way to know without a full loading.

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.