2

how do i limit each of the or from this mysql sequence =

            $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

i need it so each tags have a limit to fetch no more than 6. is it possible? if not is there a way to limit it in php?. here is the whole code. basically the code above asking if tags is equal to either of the 5 tags at the top it will be fetched. and there are more than 6 videos/ column on each tags but i only want each tag to only fetch no more than 6.

jakson
  • 255
  • 3
  • 10

4 Answers4

2

Variables are the correct way to do this. However, the code is a bit tricky. MySQL is quite explicit that the order of evaluation of expressions in a select is not guaranteed. So, a variable should not be assigned in one expression and then used in another.

Here is a correct version:

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 ;

Note that only one expression is assigning both @rn and @tags.

The specific documentation about variable assignment is:

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Nice one. Thanks for the that. +1 – Gurwinder Singh Jan 08 '17 at 13:14
  • i try the code, but it give me this error Fatal error: Call to a member function bind_param() on a non-object in /Applications/XAMPP/xamppfiles/htdocs/videosharing/index.php on line 126 – jakson Jan 08 '17 at 15:13
  • this is the whole code look like $query = "SELECT `videoname`,`username`,`videourl`,`uploaddate`,`duration`,`views`,`tags`,`videoimage` 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 "; – jakson Jan 08 '17 at 15:13
  • @Jakson . . . Why do you have `@tags := tags` twice in your sample query. Remove the second assignment. – Gordon Linoff Jan 09 '17 at 02:20
1

Tag-wise limit is not possible in MySQL. You can use variables to achieve this. Also, use IN instead of lots of ORs

SELECT `videoname`,
  `username`,
  `videourl`,
  `uploaddate`,
  `duration`,
  `views`,
  `tags`,
  `videoimage`
FROM (
    select 
        v.*, 
        @rn := case when @tags = tags then @rn + 1 else (if(@tags := tags, 1, 1)) end rn
    from (select * from `videolist` order by tags) v 
    cross join (select @tags := '', @rn := 0) t2) t
WHERE rn <= 6 and `tags` in (?,?,?,?,?);

More on User Defined Variables:

Community
  • 1
  • 1
Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
1

If you want to limit the result set to 6 you can use LIMIT in MySQL. See: http://www.w3schools.com/sql/sql_top.asp

SELECT column_name(s)
FROM table_name
LIMIT number;

You can combine these 5 tag queries limited to 6 result entries using this approach:

SELECT x.a, y.b FROM (SELECT * from a) as x, (SELECT * FROM b) as y

also see Combining 2 SQL queries and getting result set in one

Community
  • 1
  • 1
rob2universe
  • 7,059
  • 39
  • 54
0

by adding LIMIT at the end of code by GurV i actually able to work it out so the whole query ended up to look like this.

    $query = "SELECT `videoname`,`username`,`videourl`,`uploaddate`,`duration`,`views`,`tags`,`videoimage` FROM (
    select 
        v.*, 
        @rn := case when @tags = tags then @rn + 1 else (if(@tags := tags, 1, 1)) end rn
    from (select * from `videolist` order by tags) v 
    cross join (select @tags := '', @rn := 0) t2) t
WHERE rn <= 6 and `tags` in (?,?,?,?,?) LIMIT 30";

the LIMIT 30 indicate the TOTAL number you want for the whole page, so if you want it to show more then added more, and it will still limit each tags to show no more than 6. this is the end result look like https://postimg.org/image/vqz3rrhrx/. if there is less than 6 it will show 5 and if it have more than 6 it wont show up at all. everything else in the question stay the same only the query that i change.

jakson
  • 255
  • 3
  • 10