PHP MySQL - 通知面板，单击下拉菜单时更新数据库

Question

我有一个简单的下拉菜单，可以为我的用户显示通知。当登录用户有一条通知是在用户上次检查其通知之前创建的(在我的用户表中存储为notescheck)时，通知下拉菜单会突出显示。我希望当用户单击下拉菜单查看通知列表时更新该用户的 Notescheck。

目前我可以让它更新，但每次加载标题时都会发生这种情况，因此如果用户收到通知然后导航到另一个页面，它会自动更新。

所以我的问题是如何执行这个:

mysqli_query($db_conx, "UPDATE users SET notescheck=now() WHERE username='$username' LIMIT 1");

但只有当用户单击标题中的通知下拉菜单时？

如果需要的话，这是我的 header 的完整代码:

<div class="sticky_wrapper">
<?php

session_start();

$username = $_SESSION['username'];

?>

<!-- Le javascript
    ================================================== -->
    <!-- Placed at the end of the document so the pages load faster -->
    <script src="http://localhost:8888/cge/assets/js/jquery.js"></script>
    <script src="http://localhost:8888/cge/assets/js/bootstrap-transition.js"></script>
    <script src="http://localhost:8888/cge/assets/js/bootstrap-alert.js"></script>
    <script src="http://localhost:8888/cge/assets/js/bootstrap-modal.js"></script>
    <script src="http://localhost:8888/cge/assets/js/bootstrap-dropdown.js"></script>
    <script src="http://localhost:8888/cge/assets/js/bootstrap-scrollspy.js"></script>
    <script src="http://localhost:8888/cge/assets/js/bootstrap-tab.js"></script>
    <script src="http://localhost:8888/cge/assets/js/bootstrap-tooltip.js"></script>
    <script src="http://localhost:8888/cge/assets/js/bootstrap-popover.js"></script>
    <script src="http://localhost:8888/cge/assets/js/bootstrap-button.js"></script>
    <script src="http://localhost:8888/cge/assets/js/bootstrap-collapse.js"></script>
    <script src="http://localhost:8888/cge/assets/js/bootstrap-carousel.js"></script>
    <script src="http://localhost:8888/cge/assets/js/bootstrap-typeahead.js"></script>
    <!-- main js -->
    <script src="http://localhost:8888/cge/assets/js/main.js"></script>
    <script src="http://localhost:8888/cge/assets/slider/js/bootstrap-slider.js"></script>



    <!-- Typeahead -->
    <script>
        $(function() {
            $("#typeahead").typeahead({
                source: function(typeahead, query) {
                    $.ajax({
                        url: 'http://localhost:8888/cge/source.php',
                        type: 'POST',
                        data: 'query=' + query,
                        dataType: 'JSON',
                        async: false,
                        success: function(data) {
                            typeahead.process(data);
                        }
                    });
                }
            });
        });
    </script>

<div style="padding-top: 60px;"></div>

<!-- WHITE USER STRIP -->
    <div class="navbar navbar-inverse navbar-fixed-top">
      <div class="navbar-inner">
        <div class="container">

    <?php
        if ($username) {
//display USER
            include('./php_includes/db_conx.php');
            $sql = "SELECT notescheck FROM users WHERE username='$username' LIMIT 1";
            $query = mysqli_query($db_conx, $sql);
            $row = mysqli_fetch_row($query);
            $notescheck = $row[0];
            $sql = "SELECT id FROM notifications WHERE username='$username' AND date_time > '$notescheck' LIMIT 1";
            $query = mysqli_query($db_conx, $sql);
            $numrows = mysqli_num_rows($query);

            $sql = "SELECT * FROM friends WHERE user2='$username' AND accepted='0' ORDER BY datemade DESC";
            $friendquery = mysqli_query($db_conx, $sql);
            $friendnumrows = mysqli_num_rows($friendquery);
            if ($numrows == 0 && $friendnumrows == 0) {
                $notifications = '<i class="fa fa-inbox"></i>';
            } else {
                $notifications = '<i style="color:#3dca9c" class="fa fa-inbox"></i>';
                    }

//notifications panel
            $notification_list = "";
            $sql = "SELECT * FROM notifications WHERE username LIKE BINARY '$username' AND date_time > '$notescheck' ORDER BY date_time DESC";
            $query = mysqli_query($db_conx, $sql);
            $numrows = mysqli_num_rows($query);

            if($numrows < 1 && $friendnumrows < 1){
            $notification_list = "<li><a href='#'>You do not have any notifications</a></li>";
            } else {
            while ($row = mysqli_fetch_array($query, MYSQLI_ASSOC)) {
            $noteid = $row["id"];
            $initiator = $row["initiator"];
            $cardid = $row["cardid"];
            $app = $row["app"];
            $note = $row["note"];
            $date_time = $row["date_time"];
            $date_time = strftime("%b %d, %Y", strtotime($date_time));


            $notification_list .= "<li><a href='http://localhost:8888/cge/card.php?id=$cardid'>$note</a></li>";
            }
            while ($row2 = mysqli_fetch_array($friendquery, MYSQLI_ASSOC)) {
            $friendrequester = $row2["user1"];

            $friendrequest_list .= "<li><a href='http://localhost:8888/cge/account/notifications.php'><strong>$friendrequester</strong> sent a friend request</a></li>";
            }
            }
                    mysqli_query($db_conx, "UPDATE users SET notescheck=now() WHERE username='$username' LIMIT 1");
            echo
            "<ul class='nav'>
              <li class='dropdown'>
                <a href='#' class='dropdown-toggle' data-toggle='dropdown'>$username <b class='caret'></b></a>
                <ul class='dropdown-menu'>
                  <li><a href='http://localhost:8888/cge/account/user.php?u=$username'>View Profile</a></li>
                  <li><a href='#'>Another action</a></li>
                  <li><a href='#'>Something else here</a></li>
                  <li class='divider'></li>
                  <li><a href='http://localhost:8888/cge/account/logout.php'>Log out</a></li>
                </ul>
              </li>
            </ul>

            <ul class='nav pull-right'>
                <li class='dropdown'>".
                "<a href='#' class='dropdown-toggle' data-toggle='dropdown'>" . ($notifications) . "<b class='caret'></b></a>
                <ul class='dropdown-menu'>
                  " . $notification_list;
                  if ($friendrequest_list){
                  echo "<li class='divider'></li>" . $friendrequest_list; }
                  echo "<li class='divider'></li>
                  <li><a href='http://localhost:8888/cge/account/notifications.php'>View all notifications</a></li>
                </ul>
              </li>
            </ul>";
        }
        else
//diplay login or register button
            echo "<ul class='nav login-or-register'>
            <li><a href='http://localhost:8888/cge/account/login.php'>Login or Register</a></li>
           </ul>

           <ul class='nav pull-right'>
            <li><a href='#'>Get a Card Graded!</a></li>
           </ul>";

        if ($_SERVER['QUERY_STRING'] == logout){
            echo "<body onload=\"window.location='http://localhost:8888/cge/index.php'\">"; 
            session_destroy();
        }
    ?>

        </div>
      </div>
    </div>


    <!-- MAIN NAV & LOGO -->
    <div class="nav-second container">
        <div class="row-fluid">
            <div class="logo span4">
                <img src="http://localhost:8888/cge/assets/img/logo.png" />
            </div>

            <div class="main-nav span8">
                <ul class="nav pull-right">
                    <li><a href="#">Browse Cards</a></li>
                    <li><a href="#">Charts</a></li>
                    <li><a href="#">Forum</a></li>
                    <li><a href="#">Blog</a></li>
                    <li><a href="#">How-To Guide</a></li>
                </ul>
            </div>
                <div class="search-bar span8 pull-right">
                    <div class="search-bar-inner pull-right">
                    <form action='http://localhost:8888/cge/search.php' method='get'>
                        <input type="text" placeholder="Enter a Card..." id="typeahead" data-provide="typeahead" autocomplete="off" name ="keyname" value="<?php echo preg_replace('/[^a-zA-Z0-9_\-\­.\(\)\s\!\?\,]/', '' ,$_GET['keyname']);?>"/>
                        <button type="submit" class="btn">Search</button>
                    </form>
                    </div>
                    <hr>
                </div>


        </div>
    </div>

谢谢!

Answer 1

您也可以使用 JQuery 来实现此目的。向仅运行更新查询的文件发送 ajax 请求。

$(".notifications").click(function(){
    $.post('update/notifications.php');
});

其中 update/notifications.php 如下所示

session_start();
$username = $_SESSION['username'];
//connect
mysqli_query($db_conx, "UPDATE users SET notescheck=now() WHERE username='$username' LIMIT 1");

因此，仅当用户实际单击通知时才会运行查询。这是一种方法，我想还有其他方法，老实说，我不能 100% 确定您可以使用 session ，但您只需通过 POST 请求发送用户名即可。