PHP/MySQL 多重嵌套 SELECT

Question

我有2个数据库表user_pm和user_pm_hide

基本上我想从user_pm_hide中选择数据然后从user_pm中选择数据哪里id != pm_id在user_pm_hide .

我已经编写了我需要的代码，但是遇到了问题..

My Code hides only one message, with latest id, all other stays visible.

我的核心功能:

public function inbox(){
    try{

        require_once("../System/class.user.php");
        $auth_user = new USER();

        $id = $_SESSION['user_session'];
        $user_group = $_SESSION['user_session'];

        $stmt = $auth_user->runQuery("SELECT * FROM users WHERE id=:id");
        $stmt->execute(array(":id"=>$id));

        $userRow=$stmt->fetch(PDO::FETCH_ASSOC);
        $username = $userRow['id'];

        $stmt = $this->conn->prepare("SELECT count(*) FROM user_pm WHERE sentto=('$username')");
        $stmt->execute();
        $inboxall = $stmt->fetchColumn();

        $stmt = $this->conn->prepare("SELECT count(*) FROM user_pm_hide WHERE user_id=('$username') AND sender != ('$username') ");
        $stmt->execute();
        $inboxhidden = $stmt->fetchColumn();

        $inboxtotal = $inboxall - $inboxhidden;

        $inboxperpage = 15;
        $inboxpages = ceil($inboxtotal / $inboxperpage);

        $inbox_get_pages = isset($_GET['inboxpage']) ? $_GET['inboxpage'] : 1;

        $inboxdata = array(

            'options' => array(
                'default'   => 1,
                'min_range' => 1,
                'max_range' => $inboxpages
                )
        );

        $inboxnumber = trim($inbox_get_pages);
        $inboxnumber = filter_var($inboxnumber, FILTER_VALIDATE_INT, $inboxdata);
        $inboxrange = $inboxperpage * ($inboxnumber - 1);

        $inboxprev = $inboxnumber - 1;
        $inboxnext = $inboxnumber + 1;

        $stmt = $this->conn->prepare("SELECT * FROM user_pm WHERE sentto=('$username') ORDER BY id DESC");
        $stmt->execute();

        $inboxresult = $stmt->fetchAll();

        foreach($inboxresult as $result){

            $checkid = $result['id'];
            $stmt = $this->conn->prepare("SELECT pm_id FROM user_pm_hide WHERE pm_id=('$checkid') AND sender !=('$username') ORDER BY id ");
            $stmt->execute();

            $checkidresult = $stmt->fetchAll();

            if($inboxhidden != 0){

            foreach($checkidresult as $check){
                $hiddenMessage = $check['pm_id'];
                echo $hiddenMessage;

                $stmt = $this->conn->prepare("SELECT * FROM user_pm WHERE sentto=('$username') AND id !=('$hiddenMessage') ORDER BY id DESC LIMIT :limit, :perpage");
                $stmt->bindParam(':perpage', $inboxperpage, PDO::PARAM_INT);
                $stmt->bindParam(':limit', $inboxrange, PDO::PARAM_INT);
                $stmt->execute();

                $inboxresult = $stmt->fetchAll();

                foreach($inboxresult as $message){

                    $stmt = $auth_user->runQuery("SELECT * FROM users WHERE id=:sentby");
                    $stmt->execute(array(":sentby"=>$message['sentby']));

                    $userRow=$stmt->fetch(PDO::FETCH_ASSOC);

                    $date = $message['sent'];
                    $timestamp = strtotime($date);

                    $strTime = array("second", "minute", "hour", "day", "month", "year");
                    $length = array("60","60","24","30","12","10");

                    $currentTime = time();
                    if($currentTime >= $timestamp) {
                        $diff     = time()- $timestamp;
                        for($i = 0; $diff >= $length[$i] && $i < count($length)-1; $i++) {
                            $diff = $diff / $length[$i];
                        }
                        $diff = round($diff);
                    }
                    ?>

                    <a href="/Users/ViewMessages.php?inboxpage=<?php echo $inboxnumber?>?action=View&id=<?php echo $message['id']?>">
                <div class="mail_list">
                <div class="left">
                                <img src="../Users/<?php echo $userRow['image']; ?>" style="width:50px; height:50px">
                </div>
                <div class="right">
                                <h3 style="margin-left:30px"><?php echo $userRow['username']?><small><?php echo $diff . " " . $strTime[$i] . "(s) ago "; ?></small></h3>
                                <p style="margin-left:30px"><?php echo $message['title']?><small style="float:right"><?php echo $message['readed']?></small></p>
                </div>
                </div>
            </a>

                    <?php
                }

                if($inboxresult && count($inboxresult) > 0){
                    echo "<center>";
            echo "<h4>Page $inboxnumber/$inboxpages</h4>";
                    echo "<h4>Messages : $inboxtotal</h4>";

                    # first page
            if($inboxnumber <= 1)
              echo "<span>&laquo; prev</span> | <a href=\"?inboxpage=$inboxnext\">next &raquo;</a>";
                # last page
            elseif($inboxnumber >= $inboxpages)
              echo "<a href=\"?inboxpage=$inboxprev\">&laquo; prev</a> | <span>next &raquo;</span>";
                    # in range
            else
              echo "<a href=\"?inboxpage=$inboxprev\">&laquo; prev</a> | <a href=\"?inboxpage=$inboxnext\">next &raquo;</a>";
                        echo "</center>";
            }else{
              echo "<p>No results found.</p>";
            }

                return $inboxresult;
            }
            }else{
                $stmt = $this->conn->prepare("SELECT * FROM user_pm WHERE sentto=('$username') ORDER BY id DESC LIMIT :limit, :perpage");
                $stmt->bindParam(':perpage', $inboxperpage, PDO::PARAM_INT);
                $stmt->bindParam(':limit', $inboxrange, PDO::PARAM_INT);
                $stmt->execute();

                $inboxresult = $stmt->fetchAll();

                foreach($inboxresult as $message){

                    $stmt = $auth_user->runQuery("SELECT * FROM users WHERE id=:sentby");
                    $stmt->execute(array(":sentby"=>$message['sentby']));

                    $userRow=$stmt->fetch(PDO::FETCH_ASSOC);

                    $date = $message['sent'];
                    $timestamp = strtotime($date);

                    $strTime = array("second", "minute", "hour", "day", "month", "year");
                    $length = array("60","60","24","30","12","10");

                    $currentTime = time();
                    if($currentTime >= $timestamp) {
                        $diff     = time()- $timestamp;
                        for($i = 0; $diff >= $length[$i] && $i < count($length)-1; $i++) {
                            $diff = $diff / $length[$i];
                        }
                        $diff = round($diff);
                    }
                    ?>

                    <a href="/Users/ViewMessages.php?inboxpage=<?php echo $inboxnumber?>?action=View&id=<?php echo $message['id']?>">
                        <div class="mail_list">
                            <div class="left">
                                <img src="../Users/<?php echo $userRow['image']; ?>" style="width:50px; height:50px">
                            </div>
                            <div class="right">
                                <h3 style="margin-left:30px"><?php echo $userRow['username']?><small> <?php echo $diff . " " . $strTime[$i] . "(s) ago "; ?>    </small></h3>
                                <p style="margin-left:30px"><?php echo $message['title']?><small style="float:right"><?php echo $message['readed']?></small></p>
                            </div>
                        </div>
                    </a>

                    <?php
                }

                if($inboxresult && count($inboxresult) > 0){
                    echo "<center>";
                    echo "<h4>Page $inboxnumber/$inboxpages</h4>";
                    echo "<h4>Messages : $inboxtotal</h4>";

                    # first page
                    if($inboxnumber <= 1)
                        echo "<span>&laquo; prev</span> | <a href=\"?inboxpage=$inboxnext\">next &raquo;</a>";
                    # last page
                    elseif($inboxnumber >= $inboxpages)
                        echo "<a href=\"?inboxpage=$inboxprev\">&laquo; prev</a> | <span>next &raquo;</span>";
                    # in range
                    else
                        echo "<a href=\"?inboxpage=$inboxprev\">&laquo; prev</a> | <a href=\"?inboxpage=$inboxnext\">next &raquo;</a>";
                        echo "</center>";
                    }else{
                        echo "<p>No results found.</p>";
                    }

                return $inboxresult;
            }
        }

    }
    catch(PDOException $e)
    {
        echo $e->getMessage();
    }
}

Answer 1

使用 SQL NOT IN

SELECT *
FROM table1
WHERE id NOT IN
  (
     SELECT id
     FROM table2
  )