mysql query help

[Ne0_Dev]

Hi All,

 

Wondered if someone could help me out with a sql query that I am having difficulty with?

 

My database consists of 3 tables, clients, video, category.  The video table stores the primary key value of the clients table and the category table as a foreign key.  What I am trying to achieve is return all the videos that are associated to a particular client and group them under the relevant category.  If there are now videos that match the category then I do not want to display the category.  Here is my code so far:

  <?php
$sql = "SELECT category.cat_id, category.name AS catname FROM category";
$result = mysql_query($sql) or die (mysql_error());
while($categoryrow = mysql_fetch_assoc($result)) { ?>
    </p>
<div class="themeheader"><h5><?php echo $categoryrow['catname']; ?></h5></div>
<Br />
<?php
$vsql = "SELECT video.video_id, video.title, video.description, video.thumbnail FROM video WHERE video.cat_id = '" . $categoryrow['cat_id'] . "' AND video.client_id = $customerid ORDER BY video.video_id DESC";
$vresult = mysql_query($vsql) or die (mysql_error());	
?>
<div class="videos">
		<ul>
			<?php
			while($videorow = mysql_fetch_assoc($vresult)) {
			?>
                <li id="categoryList"><a href="film-details.php?video_id=<?php echo $videorow['video_id']; ?>"><img src="+_1m4g35/<?php echo $videorow['thumbnail']; ?>" alt="<?php echo $videorow['title']; ?>" title="<?php echo $videorow['title']; ?>" width="291" height="142" border="0" /></a>
			  <h2><?php echo $videorow['title']; ?></h2>
                  <p><?php $limit = 100;
			  if (strlen($videorow['description']) > $limit)
					  $description = substr($videorow['description'], 0, strrpos(substr($videorow['description'], 0, $limit), ' ')) . '... <a href="film-details.php?video_id='.$videorow['video_id'].'">read more</a>';
					  echo $description;
			   ?>
                  </p>
		<?php } //end video loop?>
		</ul>
<br class="clearfloat" />
</div>
<?php } //end category loop ?>
  </div>

 

The above code is the closest I have got but it still outputs the categories even when there are no videos that match the category id and the client id.

 

Any help in the right direction gratefully received as I am gradually going insane!  :-\

[spiderwell]

you want to select all from video and inner join category on to that table where blah blah.

 

then you have one record set with only the categorys listed in the video table and not a full list of categorys

 

do you know enough sql to do that?

[gizmola]

Yes of course you get empty categories, because you select all the categories and loop through those spitting out a category header div before you've queried to see if there are any videos for that category.

 

Do you not understand why that is?  A call to mysql_num_rows would take care of this problem, as you could check before you otuput either the category div or start the ul. 

 

With that said, the best answer is to do a single query that inner joins your video table to category, with an ORDER BY category_id, video_id DESC. 

 

You would be doing only one qeuery, getting only one result set, and would need only one loop.  You would simply need a variable for category that you check to see if there's a new category which requires you to output a new category section.

[spiderwell]

did i inner join the wrong way round? I will admit its one of my weaker points  :'(

[Ne0_Dev]

Hi spiderwell/gizmola,

 

Thank you both for your prompt comments/suggestions.  I did understand why all the categories were being returned, but couldn't figure out the right way to limit them.  I am not too familiar with JOINS and the which way round the tables go.  Would either of you be able to provide an example for me?

 

I have  had a go with this?

$sql = "SELECT * FROM video INNER JOIN category ON category.cat_id = video.cat_id ORDER BY category_id, video_id DESC";

[gizmola]

You suggested what I would suggest,  start with video and inner join to category.    For an inner join it really doesn't matter because you only get a row when the join matches values in both tables.

[gizmola]

That is the basics of it, but since you need some things from category, you want to specify those columns in your list.  One way to make this a bit simpler is to use table aliases.  I usually use a letter or 2 from the name of the table.  This would get you all the columns, but you're probably better off just listing the exact columns you want.

 

$sql = "SELECT c.*, v.* FROM video v INNER JOIN category c ON c.cat_id = v.cat_id ORDER BY c.cat_id, video_id DESC";

[Ne0_Dev]

Hi gizmola,

 

I have had a go at implementing your suggestions and this si what i have come up with:

 

<?php
$sql = "SELECT c.name, v.* FROM video v INNER JOIN category c ON c.cat_id = v.cat_id WHERE v.client_id = $customerid ORDER BY c.cat_id, video_id DESC";
$result = mysql_query($sql) or die (mysql_error());
while($categoryrow = mysql_fetch_assoc($result)) { ?>
    </p>
<div class="themeheader"><h5><?php echo $categoryrow['name']; ?></h5></div>
<Br />
<div class="videos">
		<ul>

                <li id="categoryList"><a href="film-details.php?video_id=<?php echo $categoryrow['video_id']; ?>"><img src="+_1m4g35/<?php echo $categoryrow['thumbnail']; ?>" alt="<?php echo $categoryrow['title']; ?>" title="<?php echo $categoryrow['title']; ?>" width="291" height="142" border="0" /></a>
			  <h2><?php echo $categoryrow['title']; ?></h2>
                  <p><?php $limit = 100;
			  if (strlen($categoryrow['description']) > $limit)
					  $description = substr($categoryrow['description'], 0, strrpos(substr($categoryrow['description'], 0, $limit), ' ')) . '... <a href="film-details.php?video_id='.$categoryrow['video_id'].'">read more</a>';
					  echo $description;
			   ?>
                  </p>
		</ul>
<br class="clearfloat" />
</div>
<?php } //end category loop ?>
  </div>

This is limiting the results by customer id and is returning only categories that have videos associated to them.  the only thing I cannot seem to do is get videos that are in the same category appearing under one category header. At the moment I have 2 videos under the same category but category header appears twice, once with one video and once with the other video.

Thanks in advance..

[Ne0_Dev]

Sorry forgot to mention in my previous post that this is limiting the results by customer id and is returning only categories that have videos associated to them.  The only thing I cannot seem to do is get videos that are in the same category appearing under one category header. At the moment I have 2 videos under the same category but category header appears twice, once with one video and once with the other video.

 

Thanks in advance..

[gizmola]

This is basically the same problem as the one discussed here:  http://www.phpfreaks.com/forums/index.php?topic=331579.msg1560489#msg1560489

 

Take a look at my answer, as it's the same answer I alluded to previously, when I stated that you need a variable you assign to cat_id to track whether or not you need to start a new category block.

[Ne0_Dev]

gizmola,

 

thanks for the link and I have had a go at implementing it:

 

<?php
$sql = "SELECT c.name, v.* FROM video v INNER JOIN category c ON c.cat_id = v.cat_id WHERE v.client_id = $customerid ORDER BY c.cat_id, video_id DESC";
$result = mysql_query($sql) or die (mysql_error());
$category_id = '';
while($categoryrow = mysql_fetch_assoc($result)) { 
if($categoryrow['cat_id'] != $category_id) {
?>
<div class="themeheader"><h5><?php echo $categoryrow['name']; ?></h5></div>
<?php 
$category_id = $categoryrow['cat_id']; 
}
?>
<Br />
<div class="videos">
		<ul>

                <li id="categoryList"><a href="film-details.php?video_id=<?php echo $categoryrow['video_id']; ?>"><img src="+_1m4g35/<?php echo $categoryrow['thumbnail']; ?>" alt="<?php echo $categoryrow['title']; ?>" title="<?php echo $categoryrow['title']; ?>" width="291" height="142" border="0" /></a>
			  <h2><?php echo $categoryrow['title']; ?></h2>
                  <p><?php $limit = 100;
			  if (strlen($categoryrow['description']) > $limit)
					  $description = substr($categoryrow['description'], 0, strrpos(substr($categoryrow['description'], 0, $limit), ' ')) . '... <a href="film-details.php?video_id='.$categoryrow['video_id'].'">read more</a>';
					  echo $description;
			   ?>
                  </p>
		</ul>
<br class="clearfloat" />
</div>
<?php
} //end category loop ?>
  </div>

 

The only problem I have now is that each video is not being output under a new <li> element under the same category.

[gizmola]

The way to look at it is that you have a block which includes the category header AND the unordered list that you need to output for every category.  Each time you are doing this, it means that you already had a category previously that you need to close out first, by providing the end tags.  The only time that is not true, is when you first enter the loop.  In that case you don't need to close a ul and div first. You can check that by testing to see if category_id == '', which will only be true on entry intot he while loop.  Something like this is closer to what you need:

 

$sql = "SELECT c.name, v.* FROM video v INNER JOIN category c ON c.cat_id = v.cat_id WHERE v.client_id = $customerid ORDER BY c.cat_id, video_id DESC";
$result = mysql_query($sql) or die (mysql_error());
$category_id = '';
while($categoryrow = mysql_fetch_assoc($result)) { 
if($categoryrow['cat_id'] != $category_id) {
  if ($category_id != '') {
//close up prior list and div




  }
?>



$category_id = $categoryrow['cat_id']; 
}
?>

			  
                  
				  if (strlen($categoryrow['description']) > $limit)
					  $description = substr($categoryrow['description'], 0, strrpos(substr($categoryrow['description'], 0, $limit), ' ')) . '... read more';
					  echo $description;
			   ?>
                  
} //end category loop ?>




  

[Ne0_Dev]

Hi Gizmola,

 

Thanks for your help with this I have taken your advice and managed to implement a successful solution that works just as I hoped!

 

Cheers

 

Ne0_Dev