nepzap2 Posted January 9, 2009 Share Posted 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"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Summer Interns</title> <link href="styles/default.css" rel="stylesheet" type="text/css" /> </head> <body> <div class="menu"> <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> <div class="internYears"> <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> <div class="main"> <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> </div> </body> </html> Link to comment https://forums.phpfreaks.com/topic/140180-help-with-query/ Share on other sites More sharing options...
bubbasheeko Posted January 9, 2009 Share Posted 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. Link to comment https://forums.phpfreaks.com/topic/140180-help-with-query/#findComment-733622 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.