PHP/MySQL multiple nested SELECTs

Question

I have 2 database tables user_pm and user_pm_hide

Basicly I want to select data from user_pm_hide and then select data from user_pm where id != pm_id in user_pm_hide.

I have coded something like I need, but got problem..

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

My hardcore function:

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();
    }
}

If you cannot explain the problem you will certainly never find a solution. You have put some effort in your question, but it is not clear what you want to achieve. I stumble at: "want to select data from user_pm_hide", but you never say what you want to select. Please read How to create a Minimal, Complete, and Verifiable example. — KIKO Software
– KIKO Software, Commented Aug 30, 2017 at 8:54
Try to create a SELECT - JOIN-condition. This does have the advantage, that the database will create a 'view' about both tables and then select the data. Much more efficiency and less coding ;P — SacrumDeus
– SacrumDeus, Commented Aug 30, 2017 at 9:02
For this i can recommend you this link: Joining 2 Tables in PHP — SacrumDeus
– SacrumDeus, Commented Aug 30, 2017 at 9:03
Don't insert $username into queries. Use parameters instead, like you did in the first query. How can I prevent SQL injection in PHP? — Dmitry
– Dmitry, Commented Aug 30, 2017 at 9:37

Dmitry · Accepted Answer · 2017-08-30 09:13:27Z

1

Use SQL NOT IN

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

answered Aug 30, 2017 at 9:13

Dmitry

7,29614 gold badges42 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

SacrumDeus Over a year ago

This solution works, however it's not as fast as a join condition. A join condition creates a view over the joined tables. The nested select causes many database accesses which need much more time

SacrumDeus Over a year ago

Try to use: SELECT * FROM table1 JOIN table2 ON condition JOIN table3 ON condition WHERE condition

Crelix Over a year ago

@SacrumDeus I don't really understand these JOIN methods. Tried something like this, but it dosn't hide any and displays all records twice.

Crelix Over a year ago

@SacrumDeus $stmt = $this->conn->prepare("SELECT * FROM user_pm JOIN user_pm_hide WHERE user_pm.id != user_pm_hide.pm_id AND user_pm.sentto = ('$username') AND user_pm_hide.sender != ('$username') ORDER BY user_pm.id DESC")

SacrumDeus Over a year ago

You have to set a join condition. I don't see JOIN user_pm_hide ON condition. This condition is very important that the database can create a correct view

Collectives™ on Stack Overflow

PHP/MySQL multiple nested SELECTs

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related