How do I display items under a category with one MySQL query?

[SauloA]

I have a video table and a video category table.  The tables are pretty much set up like the following:

 

video_tbl

------------

video_id

video_title

video_cat_id

 

video_cat_tbl

-----------------

video_cat_id

video_cat

 

I have 2 recordsets, 1 for each table, that use the following queries

 

video table

SELECT video_cat, video_title
FROM video_tbl, video_cat_tbl
WHERE video_cat_tbl.video_cat_id = video_tbl.video_cat_id

 

video category table

SELECT *
FROM video_cat_tbl
ORDER BY video_cat ASC

 

And I'm using the following PHP code to display the data:

<?php do { ?>
<table border="1" width="100%">
<tr>
<td colspan="2"><?php echo $row_rsVideoCat['video_cat']; ?></td>
</tr>
<?php do { ?>
<tr>
<td><?php echo $row_rsVideo['video_title']; ?></td>
</tr>
<?php } while ($row_rsVideo = mysql_fetch_assoc($rsVideo)); ?>
</table>
<br />
<?php } while ($row_rsVideoCat = mysql_fetch_assoc($rsVideoCat)); ?>

 

Using the code above the data displays like this

 

Video Category 1

--------------------

Video 1

Video 2

Video 3

Video 4

 

Video Category 2

---------------------

But I want it to display like this:

 

Video Category 1

--------------------

Video 1

Video 2

 

Video Category 2

---------------------

Video 3

Video 4

 

How do I display all the categories with with their specific Items under them?

[SauloA]

I've been doing some research and I think what I need to do is combine the 2 recordsets in an array.  I still don't know what I'm doing though.  I'll keep looking until I find the correct answer or somebody provides me the correct answer.

[HoTDaWg]

<?php
$connection = mysqli_connect('localhost','username','password','db_name')
$query = "SELECT * FROM video_cat_tbl";
$result = mysqli_query($connection,$query);
while($row = mysqli_fetch_array($result){
{
   echo $row['video_cat'] . '<br>';
   $query2 = "SELECT * FROM video_tbl WHERE video_cat_id ='".$row['video_cat_id']."' LIMIT 2";
   $result2 = mysqli_query($connection, $query2);
   while($row2 = mysqli_fetch_array($result2)){
      echo $row2['video_title'] . '<br>';
   }
}
?>

Based on your description, here is my interpretation.

HoTDaWg

 

 

[SauloA]

Thank you HoTDaWg.  Your code has sparked the correct idea and achieved what I wanted to do.  With your insight I was able to produce the following code:

 

<?php do { ?>
<table border="1" width="100%">
<tr>
<td><?php echo $row_rsVideoCat['video_cat']; ?> <?php echo $row_rsVideoCat['video_cat_id']; ?></td>
</tr>
<?php
mysql_select_db($database_connDB, $connDB);
$query_rsVideo = "SELECT * FROM video_tbl WHERE video_cat_id ='".$row_rsVideoCat['video_cat_id']."'";
$rsVideo = mysql_query($query_rsVideo, $connDB) or die(mysql_error());
$row_rsVideo = mysql_fetch_assoc($rsVideo);
$totalRows_rsVideo = mysql_num_rows($rsVideo);
?>
<?php do { ?>
<tr>
<td><?php echo $row_rsVideo['video_title']; ?></td>
</tr>
<?php } while ($row_rsVideo = mysql_fetch_assoc($rsVideo)); ?>
</table>
<br />
<?php } while ($row_rsVideoCat = mysql_fetch_assoc($rsVideoCat)); ?>

 

Also, the code you gave me had some errors.  I believe it goes something like this:

 

<?php
$connection = mysqli_connect('localhost','username','password','db_name');
$query = "SELECT * FROM video_cat_tbl";
$result = mysqli_query($connection,$query);
while($row = mysqli_fetch_array($result))
{
   echo $row['video_cat'] . '<br>';
   $query2 = "SELECT * FROM video_tbl WHERE video_cat_id ='".$row['video_cat_id']."' LIMIT 2";
   $result2 = mysqli_query($connection, $query2);
   while($row2 = mysqli_fetch_array($result2)){
   echo $row2['video_title'] . '<br>';
   };
};
?>