help with query

nepzap2 · January 9, 2009

I was wondering if one of you can help me modify my SQL so that I can sort the left hand menu in the code below to look like the menu that is highlighted in the image below.

So that when I click the albums on the top I can sort my pictures by year. Any help is greatly appreciated.

Thank you guys.

Below are my queries and html and image.

<?php 
require_once('Connections/conn.php'); 

mysql_select_db($database_conn, $conn);
	if (isset($_GET['photoID'])) {

			  $query_rsAlbum = "SELECT tblalbum.albumID, 
			  						   tblalbum.albumTitle, 
									   tblalbum.albumDesc, 
									   tblphotos.photoID, 
									   tblphotos.photoTitle, 
									   tblphotos.photoLocation, 
									   tblphotos.photoThumb, 
									   tblphotos.photoDesc 
									   
							    FROM tblalbum, tblphotos 
								WHERE tblalbum.albumID = tblphotos.albumID AND tblphotos.photoID = " . $_GET['photoID'];

	} elseif (isset($_GET['albumID'])) {

			  $query_rsAlbum = "SELECT tblalbum.albumID, 
			                           tblalbum.albumTitle, 
									   tblalbum.albumDesc, 
									   tblphotos.photoID, 
									   tblphotos.photoTitle, 
									   tblphotos.photoLocation, 
									   tblphotos.photoThumb, 
									   tblphotos.photoDesc 
									   
								FROM tblalbum, tblphotos 
								WHERE tblalbum.albumID = tblphotos.albumID AND tblalbum.albumID = " . $_GET['albumID'];

	} else {

			  $query_rsAlbum = "SELECT tblalbum.albumID, 
			                           tblalbum.albumTitle, 
									   tblalbum.albumDesc, 
									   tblphotos.photoID, 
									   tblphotos.photoTitle, 
									   tblphotos.photoLocation, 
									   tblphotos.photoThumb, 
									   tblphotos.photoDesc 
									   
							     FROM tblalbum, tblphotos 
								 WHERE tblalbum.albumID = tblphotos.albumID AND tblalbum.albumDefault = 1 ";
	}

	$rsAlbum = mysql_query($query_rsAlbum, $conn) or die(mysql_error());
	$row_rsAlbum = mysql_fetch_assoc($rsAlbum);
	$totalRows_rsAlbum = mysql_num_rows($rsAlbum);

	mysql_select_db($database_conn, $conn);
	$query_rsThumbs = "SELECT tblphotos.photoID, 
					          tblphotos.photoTitle, 
							  tblphotos.photoThumb,
							  tblphotos.photoYear 

					    FROM tblalbum, tblphotos 

						WHERE tblphotos.albumID = tblalbum.albumID AND tblalbum.albumID = " . $row_rsAlbum['albumID'];

	$rsThumbs = mysql_query($query_rsThumbs, $conn) or die(mysql_error());
	$row_rsThumbs = mysql_fetch_assoc($rsThumbs);
	$totalRows_rsThumbs = mysql_num_rows($rsThumbs);

	mysql_select_db($database_conn, $conn);
	$query_rsAlbumList = "SELECT * FROM tblalbum";
	$rsAlbumList = mysql_query($query_rsAlbumList, $conn) or die(mysql_error());
	$row_rsAlbumList = mysql_fetch_assoc($rsAlbumList);
	$totalRows_rsAlbumList = mysql_num_rows($rsAlbumList);
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<head>

<title>Summer Interns</title>

</head>

<body>

<ul>

        	<?php do { ?>
                     <li><a href="index.php?albumID=<?php echo $row_rsAlbumList['albumID']; ?>">
                     <?php echo $row_rsAlbumList['albumTitle']; ?></a> 
                     </li>
                     <?php } while ($row_rsAlbumList = mysql_fetch_assoc($rsAlbumList)); ?>

</ul>

</div>

<ul>

    	<?php do { ?>
                  <li><a href="index.php?photoID=<?php echo $row_rsThumbs['photoID']; ?>"><?php echo $row_rsThumbs['photoTitle']; ?></a> 
			  <?php echo $row_rsThumbs['photoYear']; ?></li>
                     
                  <?php } while ($row_rsThumbs = mysql_fetch_assoc($rsThumbs)); ?>

</ul>

</div>

<p class="desc">Title:

<?php echo $row_rsAlbum['photoTitle']; ?></p>
                    <p class="desc">Description: <?php echo $row_rsAlbum['photoDesc']; ?></p>
<img src="images//<?php echo $row_rsAlbum['photoLocation']; ?>" alt="<?php echo $row_rsAlbum['photoTitle']; ?>" class="mainpic" width="400" height="300" />

</div>

</body>

</html>

bubbasheeko · January 9, 2009

You should find out what years are present first.

$query = mysql_query('SELECT 'photoYear' FROM `tblalbum` GROUP BY 'photoYear' ORDER BY `photoYear` ASC');

$year_results = mysql_fetch_assoc($query);

That will get you a list of the album years without repeating the year over and over again.

Use that query to then pull the other information based on the year.

Now you can use a loop to pull all other information for those given years and format it accordingly using a similar query.

Sign In

help with query

Recommended Posts

nepzap2

Link to comment

Share on other sites

bubbasheeko

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information