php - 限制mysql中OR的每个标签

Question

我如何限制这个 mysql 序列中的每一个或来自这个 mysql 序列 =

            $sexy = 'sexy';
            $new = 'new';
            $something = 'something';
            $brand = 'brand';
            $one = 'one';

            $limit = 12;
            $query = "SELECT `videoname`,`username`,`videourl`,`uploaddate`,`duration`,`views`,`tags`,`videoimage` FROM `videolist` WHERE `tags` = ? OR `tags` = ? OR `tags` = ? OR `tags` = ? OR `tags` = ? ";
            $stmt = $connection->prepare($query);
            $stmt->bind_param('sssss',$sexy,$new,$something,$brand,$one);
            //$stmt->bindValue(":limitz",4,PDO::PARAM_INT);
            //$stmt->bindValue(":isi",1,PDO::PARAM_INT);
            $stmt->execute();
            $stmt->store_result();
            if($stmt->num_rows > 0){

                $stmt->bind_result($videoname,$username,$videourl,$uploaddate,$duration,$views,$tags,$videoimage);
                while ($stmt->fetch()) 
                {

                    if($tags == $sexy){
                    $descBox = $doc->getElementById('suggested-video');
                        //create the element to append to #element1
                        $appended = $doc->createElement('li', "
                                <a href='video.php?watch=$videourl'>
                                        <div class='leftside'>
                                            <img src='image/$videoimage' width='100%' height='100%' style='background-color: blue;' >                   
                                        </div>

                                        <div class='rightside'>
                                            <h4>$videoname</h4>
                                            <p>$username</p>
                                            <p>$views views</p>
                                            <p>$duration</p>                    
                                        </div>
                                </a>
                        ");
                        //actually append the element
                        $descBox->appendChild($appended);
                    }// end of suggested-video

                    if($tags == $new){
                    $descBox = $doc->getElementById('popular-video');
                        //create the element to append to #element1
                        $appended = $doc->createElement('li', "
                                <a href='video.php?watch=$videourl'>
                                        <div class='leftside'>
                                            <img src='image/$videoimage' width='100%' height='100%' style='background-color: blue;' >                   
                                        </div>

                                        <div class='rightside'>
                                            <h4>$videoname</h4>
                                            <p>$username</p>
                                            <p>$views views</p>
                                            <p>$duration</p>                    
                                        </div>
                                </a>
                        ");
                        //actually append the element
                        $descBox->appendChild($appended);
                    }// end of popular-video

                    if($tags == $something){
                    $descBox = $doc->getElementById('subcription-video');
                        //create the element to append to #element1
                        $appended = $doc->createElement('li', "
                                <a href='video.php?watch=$videourl'>
                                        <div class='leftside'>
                                            <img src='image/$videoimage' width='100%' height='100%' style='background-color: blue;' >                   
                                        </div>

                                        <div class='rightside'>
                                            <h4>$videoname</h4>
                                            <p>$username</p>
                                            <p>$views views</p>
                                            <p>$duration</p>                    
                                        </div>
                                </a>
                        ");
                        //actually append the element
                        $descBox->appendChild($appended);
                    }// end of popular-video

我需要它，所以每个 tags 都有一个限制，不能超过 6 个。这可能吗？如果没有，有没有办法在 php 中限制它？这是整个代码。基本上上面的代码询问 tags 是否等于将要获取的顶部的 5 个标签中的任何一个。每个标签上有超过 6 个视频/列，但我只希望每个标签只获取不超过 6 个。

Answer 1

变量是执行此操作的正确方法。但是，代码有点棘手。 MySQL 非常明确地表示不保证 select 中表达式的求值顺序。因此，一个变量不应在一个表达式中赋值，然后在另一个表达式中使用。

这是一个正确的版本:

SELECT . . .
FROM (SELECT v.*, 
             (@rn := if(@tags = tags, @rn + 1,
                        if(@tags := tags, 1, 1)
                       )
             ) as rn
        @tags := tags
      FROM videolist v CROSS JOIN
           (SELECT @tags := '', @rn := 0) params
      WHERE tags IN (?, ?, ?, ?, ?)  -- optional filtering
      ORDER BY tags
     ) v
WHERE rn <= 6 ;

请注意，只有一个表达式同时分配了 @rn 和 @tags。

具体documentation关于变量赋值的是:

As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement. For example, to increment a variable, this is okay:

SET @a = @a + 1;

For other statements, such as SELECT, you might get the results you expect, but this is not guaranteed. In the following statement, you might think that MySQL will evaluate @a first and then do an assignment second:

SELECT @a, @a:=@a+1, ...;

However, the order of evaluation for expressions involving user variables is undefined.