Jump to content

help with query


nepzap2

Recommended Posts

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.

 

examplehb6.gif

 

 

<?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

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.