limiting each tag from OR in mysql

Question

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.

you can use `IN` instead of `OR` to minimize your sql. – Md. Sahadat Hossain Jan 08 '17 at 06:58 — Md. Sahadat Hossain, Jan 08 '17 at 06:58
Use `tags IN (?, ?, ?)` – jitendrapurohit Jan 08 '17 at 06:59 — jitendrapurohit, Jan 08 '17 at 06:59

score 2 · Answer 1 · answered Jan 08 '17 at 13:07

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.

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

score 1 · Answer 2 · edited May 23 '17 at 12:24

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:

edited May 23 '17 at 12:24

Community

1
1

answered Jan 08 '17 at 07:03

Gurwinder Singh

38,557
6
51
76

i never seen sql like that can you explain what @rn mean? – jakson Jan 08 '17 at 07:12
1

@jakson I've added some links for you in the answer. – Gurwinder Singh Jan 08 '17 at 07:14
i havent try it out yet. i will try it out tomorrow and i will tell you how it will go. – jakson Jan 08 '17 at 09:16
@Gurv . . . Although this may seem to work, it is not correct. The order of evaluation of the expressions in the subselect is not guaranteed. MySQL does not guarantee than `@rn` is evaluated before `@tags`. – Gordon Linoff Jan 08 '17 at 13:08
1

adding limit 30 to you code did the job :) thank you – jakson Jan 09 '17 at 00:11

score 1 · Answer 3 · edited May 23 '17 at 12:16

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

edited May 23 '17 at 12:16

Community

1
1

answered Jan 08 '17 at 07:22

rob2universe

7,059
39
54

the code you give mean the total fetch will be limited to 6 only, but what i am looking for is for each tags (there is 5 tags) to each have 6 limit so in total it will have 30.example: tags $brand have 6, tags $new have 6 and so on. – jakson Jan 08 '17 at 09:18
Adjusted answer. Does this help? – rob2universe Jan 08 '17 at 11:10
so that mean i have to create 5 separated queries? – jakson Jan 08 '17 at 15:00
Well, 1 query composed of 5 sub queries - unless someone else has a better idea. – rob2universe Jan 09 '17 at 06:08

score 0 · Answer 4 · answered Jan 09 '17 at 00:16

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.

limiting each tag from OR in mysql

4 Answers4