Chapter 7 - Pursue Portfolio queries

Using the previously created portfolio database, use more complex sql queries to practice advanced techniques.

Output

Portfolio Database Table Data

Portfolio Items
item_idcategory_idtitledate_createddescriptiondate_posted
11a painting2001-06-12my first painting!2013-09-20 16:28:13
23another painting2007-05-00my favorite painting2013-09-20 18:15:51
31third painting2010-07-00a commisioned painting2013-09-22 20:15:14
Categories
category_idnamedescriptionparent_id
1paintingsgeneral painting category0
2drawingssketches on various media0
3charcoaldrawings using charcoal2
4acrylicpaintings using acrylic1
5oiltraditional paintings using oils1
Tags
tag_idnamedescription
1figurativeimages inspired by the human figure
2abstractcolorful abstractions
Item Tags
item_idtag_id
11
21
12
32
Albums
album_iditem_idtitledescriptiondate_created
11a blank title for album 1painting in progress2013-09-27 13:45:02
21a blank title for album 2completed painting2013-09-27 12:22:50
32another album2013-09-27 12:45:32
Album Images
album_idimage_idsort
111
212
221
233
341
Images
image_idtitlealtfilenamefilepathheightwidthdate_uploaded
1imagetest.png3202400000-00-00 00:00:00
2titletestimg2.png3202402013-09-22 10:06:42
3title2alt testtestimg3.pngimages/6404802013-09-30 20:03:52
4another imagealt2testimg4.pngimages/8006002013-09-30 20:03:52

Advanced Query Examples

    1. JOINS

    • Get all images in all albums associated with the portfolio item with id=1

      SELECT DISTINCT(images.filename), images.title FROM portfolio_items
LEFT JOIN albums
ON albums.item_id = portfolio_items.item_id
LEFT JOIN album_images
ON album_images.album_id=albums.album_id
INNER JOIN images
ON album_images.image_id = images.image_id
WHERE portfolio_items.item_id=1;
      Results
      filenametitle
      test.pngimage
      testimg2.pngtitle
      testimg3.pngtitle2

    • Get the number of albums associated with portfolio item 1

      SELECT COUNT(albums.item_id) as `Number of albums` FROM albums 
INNER JOIN portfolio_items USING (item_id)
WHERE portfolio_items.item_id=1;
      Results
      Number of albums
      2

    • Get all portfolio item titles in the category 'paintings'

      SELECT title FROM portfolio_items 
INNER JOIN categories AS C USING (category_id)
WHERE C.name='paintings';
      Results
      title
      a painting
      third painting

    • Use a self join to retrieve all the subcategories of the painting category.

      SELECT C2.parent_id, C2.name AS 'Subcategory', C1.name AS 'Main Category' 
FROM categories as C1, categories as C2 
WHERE C1.category_id=C2.parent_id AND C1.name='paintings';
      Results
      parent_idSubcategoryMain Category
      1acrylicpaintings
      1oilpaintings

    • Use a left join to retrieve all the tags of portfolio item 1.

      SELECT tags.name AS 'Tag', tags.description FROM portfolio_items 
LEFT JOIN item_tags USING(item_id) LEFT JOIN tags USING(tag_id) WHERE portfolio_items.item_id=1;
      Results
      Tagdescription
      figurativeimages inspired by the human figure
      abstractcolorful abstractions

    • Use a left join to retrieve all the images of portfolio items tagged with 'figurative'

      SELECT images.*, albums.* , portfolio_items.title FROM tags 
LEFT JOIN item_tags USING(tag_id) 
LEFT JOIN portfolio_items USING(item_id)
LEFT JOIN albums USING(item_id)
LEFT JOIN album_images USING(album_id)
LEFT JOIN images USING(image_id)
WHERE tags.name='figurative';
      Results
      image_idtitlealtfilenamefilepathheightwidthdate_uploadedalbum_iditem_idtitledescriptiondate_createdtitle
      1imagetest.png3202400000-00-00 00:00:0011a blank title for album 1painting in progress2013-09-27 13:45:02a painting
      1imagetest.png3202400000-00-00 00:00:0021a blank title for album 2completed painting2013-09-27 12:22:50a painting
      2titletestimg2.png3202402013-09-22 10:06:4221a blank title for album 2completed painting2013-09-27 12:22:50a painting
      3title2alt testtestimg3.pngimages/6404802013-09-30 20:03:5221a blank title for album 2completed painting2013-09-27 12:22:50a painting
      4another imagealt2testimg4.pngimages/8006002013-09-30 20:03:5232another album2013-09-27 12:45:32another painting

    2. Using GROUP BY and Aggregate Functions

    • Use aggregate functions to select different values from a numeric column.

      SELECT MIN(height), SUM(height), MAX(height) FROM images;
      Results
      MIN(height)SUM(height)MAX(height)
      3202080800

    • Get and sort by the total count of images in each album

      SELECT COUNT(images.filename) as 'IMAGE COUNT', albums.title FROM images
INNER JOIN album_images USING(image_id) 
INNER JOIN albums USING(album_id)
GROUP BY albums.title
ORDER BY `IMAGE COUNT`;
      Results
      IMAGE COUNTtitle
      1a blank title for album 1
      1another album
      3a blank title for album 2

Control Flow Functions

Advanced Comparison Functions

FULLTEXT Searching

Optimization and EXPLAIN

TRANSACTIONS

Data Encryption

Source
<?php
require(CONNECT);
include('includes/portfolio_db/portfolio_queries.php');
?>
	<h2>Portfolio Database Table Data</h2>
	<?php
		if ($result = mysqli_query($link, 'SELECT * FROM portfolio_items;')) {
			results_to_table($result, 'Portfolio Items');
		    /* free result set */
		    mysqli_free_result($result);
		}
	    if ($result = mysqli_query($link, 'SELECT * FROM categories;')) {
			results_to_table($result, 'Categories');
		    /* free result set */
		    mysqli_free_result($result);
		}
	    if ($result = mysqli_query($link, 'SELECT * FROM tags;')) {
			results_to_table($result, 'Tags');
		    /* free result set */
		    mysqli_free_result($result);
		}
		 if ($result = mysqli_query($link, 'SELECT * FROM item_tags;')) {
			results_to_table($result, 'Item Tags');
		    /* free result set */
		    mysqli_free_result($result);
		}
		 if ($result = mysqli_query($link, 'SELECT * FROM albums;')) {
			results_to_table($result, 'Albums');
		    /* free result set */
		    mysqli_free_result($result);
		}
		 if ($result = mysqli_query($link, 'SELECT * FROM album_images;')) {
			results_to_table($result, 'Album Images');
		    /* free result set */
		    mysqli_free_result($result);
		}
		 if ($result = mysqli_query($link, 'SELECT * FROM images;')) {
			results_to_table($result, 'Images');
		    /* free result set */
		    mysqli_free_result($result);
		}
	?>
	<h2>Advanced Query Examples</h2>
	<ol>
		<li>
			<ul class="examples"><h3>JOINS</h3>
				<li>
					<p>Get all images in all albums associated with the portfolio item with id=1</p>
					<pre><?php echo $q1;?></pre>
					<?php
					if ($result = mysqli_query($link, $q1)) {
						results_to_table($result);
					    /* free result set */
					    mysqli_free_result($result);
					}?>
				</li>
				<li>
					<p>Get the number of albums associated with portfolio item 1</p>
					<pre><?php echo $q2;?></pre>
					<?php
					if ($result = mysqli_query($link, $q2)) {
						results_to_table($result);
					    /* free result set */
					    mysqli_free_result($result);
					}?>
				</li>
				<li>
					<p>Get all portfolio item titles in the category 'paintings'</p>
					<pre><?php echo $q3;?></pre>
					<?php
					if ($result = mysqli_query($link, $q3)) {
						results_to_table($result);
					    /* free result set */
					    mysqli_free_result($result);
					}?>
				</li>
				<li>
					<p>Use a self join to retrieve all the subcategories of the painting category.</p>
					<pre><?php echo $q4;?></pre>
					<?php
					if ($result = mysqli_query($link, $q4)) {
						results_to_table($result);
					    /* free result set */
					    mysqli_free_result($result);
					}?>
				</li>
				<li>
					<p>Use a left join to retrieve all the tags of portfolio item 1.</p>
					<pre><?php echo $q5;?></pre>
					<?php
					if ($result = mysqli_query($link, $q5)) {
						results_to_table($result);
					    /* free result set */
					    mysqli_free_result($result);
					}?>
				</li>
				<li>
					<p>Use a left join to retrieve all the images of portfolio items tagged with 'figurative'</p>
					<pre><?php echo $q7;?></pre>
					<?php
					if ($result = mysqli_query($link, $q7)) {
						results_to_table($result);
					    /* free result set */
					    mysqli_free_result($result);
					}?>
				</li>
			</ul>
		</li>
		<li>
			<ul class="examples"><h3>Using GROUP BY and Aggregate Functions</h3>
				<li>
					<p>Use aggregate functions to select different values from a numeric column.</p>
					<pre><?php echo $q6;?></pre>
					<?php
					if ($result = mysqli_query($link, $q6)) {
						results_to_table($result);
					    /* free result set */
					    mysqli_free_result($result);
					}?>
				</li>
				<li>
					<p>Get and sort by the total count of images in each album</p>
					<pre><?php echo $q8;?></pre>
					<?php
					if ($result = mysqli_query($link, $q8)) {
						results_to_table($result);
					    /* free result set */
					    mysqli_free_result($result);
					}?>
				</li>
			</ul>
		</li>
	</ol>
	<h3>Control Flow Functions</h3>
	<h3>Advanced Comparison Functions</h3>
	<h3>FULLTEXT Searching</h3>
	<h3>Optimization and EXPLAIN</h3>
	<h3>TRANSACTIONS</h3>
	<h3>Data Encryption</h3>
<?php 
	require(DISCONNECT);
?>