0

I have 2 tables of data that are category (category table) and video (Video table) as the image below. I was querying as this picture (Query mysqli test) to get the video and its catalog. Is there a way I can use only one query but still have the same result as the current query?

<?php
include('config.php');
$page=intval($_REQUEST['page']);
$limit=10;
$start=$page*$limit - $limit;
$video=mysqli_query($conn,"SELECT * FROM video ORDER BY video_id DESC LIMIT $start,$limit");
while($r_video=mysqli_fetch_assoc($video)){
    $cat_info=mysqli_query($conn,"SELECT * FROM category WHERE cat_id IN ({$r_video['video_cat']}) ORDER BY cat_sort ASC");
    while($r_cat=mysqli_fetch_assoc($cat_info)){
        $list_cat.='<a href="/category/'.$r_cat['cat_name'].'.html">'.$r_cat['cat_name'].'</a>,';
    }
    $list_cat=substr($list_cat, 0,-1);
    echo $r_video['video_name'].'<br>'.$list_cat;
    unset($list_cat);

}
?>

category table

Video table

Query mysqli test

xahoigiaitri
  • 51
  • 6

2 Answers2

0

I would advise a LEFT JOIN Query. For example, if you want to get all the videos from Category 1 "Music", you could perform the following:

SELECT c.cat_name, v.video_name, v.video_url FROM video as v LEFT JOIN category as c ON c.cat_id = v.video_cat WHERE c.cat_id = 1;

This would result in a dataset that contains:

cat_name video_name video_url Music Music Video 1 URL 1 Music Music Video 2 URL 24

Working SQLFiddle: http://sqlfiddle.com/#!9/8008cc/3

In the SELECT statement, we calling just the columns we need, using aliases (Notice the AS portions). We're also gathering data from both tables based on a common column. This is the ON portion of the command.

The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.

In PHP you can then just use 1 while statement to iterate all the results.

Hope that helps.

UPDATE

This is more like getting tags, then categories, as you may have multiples. See: Get all posts for specific tag with SQL

Twisty
  • 30,304
  • 2
  • 26
  • 45
  • My video_cat is not an int, it is a string of category ids. For example: 1,2 is cat_id of Music and Funny. That is, it has more than one category – xahoigiaitri Jul 07 '18 at 02:38
0

You can JOIN the queries as others have stated, the problem that you are facing may be related to the fact that one video could be in more than one category.

In this case, I would use one analytical function to format the value as you want.

I currently cannot test this, but taking a look in the docs I would suggest that you try the GROUP_CONCAT or the CONCAT_WS after the join so you can group and return the data in the best way possible.

Just grab the queries already suggested and them group them the way you want so you can apply the functions to the group and extract the values.

Hope it helps.

Douglas Souza
  • 1
  • 2