How can I trigger PHP if a MySQL gets a new highest ID?

Question

Im trying to make my Webpage do an action (in this case play a sound) on the event of the highest ID (auto_increment) in my SQL table increasing, which happens when a new user is registered. E.g. : 3 users registered, highest ID = 3. When a new user registers, highest ID = 4. Webpage echos/plays sound if this happens. The Js and PHP, respectively:

 <script type="text/javascript" src="jquery.js"></script>
  <script type="text/javascript">
   $(document).ready(function() {
    setInterval(function () {
     $('#show').load('data.php')
    }, 3000);
   });
  </script>

<?php
include ('../includes/dbh.inc.php');
if ($conn->connect_error) {
 die("Connection error: " . $conn->connect_error);
}
$result = $conn->query("SELECT * FROM signs WHERE id = (SELECT MAX(id) FROM signs)");
if ($result->num_rows > 0) {
 while ($row = $result->fetch_assoc()) {
  echo $row['firstName'];
    echo $row['lastName'];
    echo $row['inOrOut'] . '<br>';
    $numId = $row['ID'] . '<br>';
    echo $numId;
 }
  $value = 1;
  $value = $numId;
  if ($value < $numId) {
    //echo '<script type="text/javascript">play_sound();</script>';
    echo "increased";
  }
  else 
    echo "nothing detected";
  }
}
?>

As you can tell, I tried doing something with comparing the last and the newest ID value but failed miserably.

My attempt would be to store an initial value for oldID and then comparing this to newID before replacing it.

Please read https://stackoverflow.com/q/58808332/1839439 – Dharman Nov 23 '19 at 21:32 — Dharman, Nov 23 '19 at 21:32

Perways H. Von Kazi · Accepted Answer · 2019-11-23T20:30:53.573

2

You can't do that only with PHP. But you could do it like this: If you have a website, you set the current highest ID in the output of php. You can use javascript to call another php script every 5 minutes (or any other time span you find meaningful) that gives you back the current highest number. If the number from the php script is higher, than the number you have in javascript, you can let javascript play a sound for you.

Assuming your php script returns an id like this:

{"id":4}

an example for the javascript call would be this:

<html>
<head></head>
<script>

let highestId = 2;

window.setInterval(async function(){
    const response = await fetch('http://localhost/jstest/index.php');
    const myJson = await response.json();
    console.log(console.log(myJson.id));
    if (highestId < myJson.id) {
        highestId = myJson.id
        // here you can play your sound
        $s = document.getElementById('myId');
        $s.innerHTML = highestId;
    }
}, 5000);

</script>
<body>
<span id="myId">0</span>
</body>
</html>

edited Nov 23 '19 at 20:30

answered Nov 23 '19 at 20:13

Perways H. Von Kazi

36
3

I like this version more, than from Prifulnath, since it doesn't rely on cookies and also it doesn't need to use additional libraries like jquery. Nice – Daidon Nov 23 '19 at 20:35
Huch, thanks for that, will try asap! I preffer not to use any cookies, as I like to keep things simple ;) – Daniel Jackson Nov 23 '19 at 20:42
Hey, I'm having problems implementing this method. I somehow can not get the PHP value of the ID I take from the sql row into the JS myJson.id. Im using your example in my main page and fetch from data.php (the php code above). What am I doing wrong? – Daniel Jackson Nov 24 '19 at 16:08

Prifulnath · Answer 2 · 2019-11-23T20:29:28.317

You can use a cookie variabale to do this. Set the cookie value using php and send the cookie value with php file call. This way you can identify a new highest id.

<script type="text/javascript" src="jquery.js"></script>
<script type="text/javascript">
    $(document).ready(function() {
        setInterval(function () {
            var id = getCookie("highest_id");
            $('#show').load('data.php?id='+id)
        }, 3000);
    });
</script>

Add set cookie in the code if the value is changed.

<?php
include ('../includes/dbh.inc.php');
if ($conn->connect_error) {
    die("Connection error: " . $conn->connect_error);
}
$result = $conn->query("SELECT * FROM signs WHERE id = (SELECT MAX(id) FROM signs)");
if ($result->num_rows > 0) {
    $numId = 0;
    if ($row = $result->fetch_assoc()) {
        $numId = $row['id'];
    }
    $value = $_GET['id'] ?? 0;
    if ($value < $numId) {
        //echo '<script type="text/javascript">play_sound();</script>';
        echo "increased";
        setcookie("highest_id", $numId, time() - 3600);
    } else {
        echo "nothing detected";
    }
}
?>

Note the points :

In PHP : setcookie("highest_id", $numId, time() - 3600);

In Script : getCookie("highest_id");

Thank you for your answer! I will potentially give it a shot, although I would prefer to solve the problem without cookies. Many Thanks for your efforts! — Daniel Jackson, Nov 23 '19 at 21:05

How can I trigger PHP if a MySQL gets a new highest ID?

2 Answers2