CODEWITHSUNDEEP
javascriptphphtmlmysqlajax
Akriti Anand
Akriti Anand

Reputation: 186

How to create notifications through php and ajax?

I have a database which when inserted into should create a notification in the webpage. What I did here was polling through ajax. I execute a query which selects for a row whose timestamp is less than 3 seconds from current time. Here is my code- HTML file:

    <html>
    <head>
    <title>Dashboard</title>
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.8.3                    /jquery.min.js"></script>
    </head>
    <body>
        <div id="responsecontainer"></div>

        <script type="text/javascript">
           $( document ).ready(function() {
           setInterval(ajaxcall, 500);

           function ajaxcall() {
            $.ajax({
            type: "GET",
            url: "maintdashsubmit.php",             
            dataType: "html",   //expect html to be returned                
            success: function(response){                   
                $("#responsecontainer").append(response); 
            }
           });}
           });
         </script>
    </body>
    </html>

PHP file:

    <?php
    session_start();

    $link = mysqli_connect("localhost", "root", "*****", "DMRC");

    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit(); 
    }

    $sql = "SELECT * FROM Ticket, Station, user_auth WHERE TIME(ticket_open_datetime) BETWEEN curtime()-3 AND curtime() AND Ticket.ticket_station_id = Station.station_id AND Ticket.ticket_open_emp_id = user_auth.emp_id AND Ticket.ticket_close_open='open" ;

    $result = $link->query($sql);
    if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()){
        echo "<tr><td style='width:15%''>".$row['ticket_id']."</td><td><table><tr><td>".$row['ticket_equipment']."</td></tr><tr><td>".$row['ticket_equipment_id']."</td></tr></table></td><td>".$row['station_name']."</td><td><table><tr><td>".$row['emp_firstname']." ".$row['emp_lastname']."</td></tr><tr><td> Sec:".$row['emp_section']."</td></tr><tr><td>".$row['emp_id']."</td></tr></table></td><td>".$row['ticket_close_open']."</td></tr>";
        }
    }

    $link->close();
    ?>

This gives me the most recent notification that I want to see. But it displays multiple times as long as it remains within the 0.5 seconds time limit. I read that I can keep a column in the database that can be set 1 if the notification has been displayed and 0 if not.

But I have a doubt. Suppose this notification has to be received by many users. What if displaying a notification to one user sets the column entry 1 before it was viewed by another? This is all so confusing. If anyone can suggest a way?

Upvotes: 0

Views: 985

Answers (1)

David
David

Reputation: 218798

What if displaying a notification to one user sets the column entry 1 before it was viewed by another?

It sounds like what you're looking for is called a "many to many" relationship. Suppose you have tables for Users and Notifications:

Users
----------
ID
Username
etc.

Notifications
----------
ID
MessageBody
etc.

If each notification can be seen by many users, and each user can see many notifications, and you want to track some piece of information about that interaction (whether a user has seen a notification), then you want a linking table between them. Something like this:

UserNotifications
----------
UserID
NotificationID
HasBeenSeen
etc.

(You can give this table its own ID, or you can use the combination of UserID and NotificationID as a compound primary key. Either way is valid, it's up to you.)

With a many-to-many relationship you need to have such a "linking table" between the two entities which are related. In that table you'd track information about the relationship itself, which in this case would include whether the user has seen the notification.

If you expect this table to grow significantly over time, there are a variety of things you can do to modify the approach slightly. For example, do you need to permanently retain the history of these things? Maybe delete records after they've been viewed? Do notifications themselves get deleted? If so, you would also delete these linking records along with them. Or perhaps the table doesn't store whether a user has seen a notification, but instead stores the notifications a user still needs to see. Then as it's seen, the record is deleted.

There are a variety of approaches you can take to solve the overall problem. But to address the specific question asked here, what you need to track the information is a many-to-many linking table.

Upvotes: 1

Related Questions