Linked results pages - not sure how to achieve it

Report · Feb 23, 2010

Hi all. This is my problem. I have a MySQL database with a single table which contains a number of listings of albums by different musicians. The client has asked for a specific way of searching this and for the life of me I can't work out how to achieve it.

Essentially what's needed are 3 pages:

page 1 will pull into a repeating region all the listings in the table, displaying the artist's name. You click on the artist's name and it takes you to page 2, which shows all the available albums by that artist (and only that artist). You click on the album of choice and it takes you to page 3 where you'll see the detailed listing for that item.

So far I've managed to get a page which shows all the different albums for the artist (for test purposes the database currently only has two entries, both by Queen) - these display fine and click through to the appropriate detail page for each album. What I can't work out is how to insert a page before the first of these to allow for selection of artists. I could probably easily produce a results page which pulls the artist name from the database (using a SQL query such as DISTINCT artist) but I can't work out how whatever results are produced would link through to the first of my pages.

If anyone can get me facing the right way on this I'd really appreciate it. I've appended the code for my two pages (op_1.php and op_2.php) below.

Thanks in advance,

Jeff

op_1.php code:

<?php require_once('Connections/ourprice.php'); ?>

<?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {

case "text":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "long":

case "int":

$theValue = ($theValue != "") ? intval($theValue) : "NULL";

break;

case "double":

$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";

break;

case "date":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "defined":

$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

break;

}

return $theValue;

}

mysql_select_db($database_ourprice, $ourprice);

$query_rsOPchoose = "SELECT * FROM OPmusic";

$rsOPchoose = mysql_query($query_rsOPchoose, $ourprice) or die(mysql_error());

$row_rsOPchoose = mysql_fetch_assoc($rsOPchoose);

$totalRows_rsOPchoose = mysql_num_rows($rsOPchoose);

?><?php

// RepeatSelectionCounter_1 Initialization

$RepeatSelectionCounter_1 = 0;

$RepeatSelectionCounterBasedLooping_1 = false;

$RepeatSelectionCounter_1_Iterations = "5";

?>

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

<head>

<title>Untitled Document</title>

</head>

<body>

<?php do { ?>

<tr>

<?php

// RepeatSelectionCounter_1 Begin Loop

$RepeatSelectionCounter_1_IterationsRemaining = $RepeatSelectionCounter_1_Iterations;

while($RepeatSelectionCounter_1_IterationsRemaining--){

if($RepeatSelectionCounterBasedLooping_1 || $row_rsOPchoose){

?>

<td><a href="op_2.php?recordID=<?php echo $row_rsOPchoose['id']; ?>"><img src="images/<?php echo $row_rsOPchoose['smallphoto']; ?>" alt="" name="smallpic" width="150" height="150" border="0" id="smallpic" /></a></td>

<?php

} // RepeatSelectionCounter_1 Begin Alternate Content

else{

?>

<?php } // RepeatSelectionCounter_1 End Alternate Content

if(!$RepeatSelectionCounterBasedLooping_1 && $RepeatSelectionCounter_1_IterationsRemaining != 0){

if(!$row_rsOPchoose && $RepeatSelectionCounter_1_Iterations == -1){$RepeatSelectionCounter_1_IterationsRemaining = 0;}

$row_rsOPchoose = mysql_fetch_assoc($rsOPchoose);

}

$RepeatSelectionCounter_1++;

} // RepeatSelectionCounter_1 End Loop

?>

</tr>

<?php } while ($row_rsOPchoose = mysql_fetch_assoc($rsOPchoose)); ?>

</table>

<br>

</body>

</html>

<?php

mysql_free_result($rsOPchoose);

?>

op_2.php code:

<?php require_once('Connections/ourprice.php'); ?><?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {

case "text":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "long":

case "int":

$theValue = ($theValue != "") ? intval($theValue) : "NULL";

break;

case "double":

$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";

break;

case "date":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "defined":

$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

break;

}

return $theValue;

}

$colname_DetailRS1 = "-1";

if (isset($_GET['recordID'])) {

$colname_DetailRS1 = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']);

}

mysql_select_db($database_ourprice, $ourprice);

$query_DetailRS1 = sprintf("SELECT * FROM OPmusic WHERE id = %s", GetSQLValueString($colname_DetailRS1, "int"));

$DetailRS1 = mysql_query($query_DetailRS1, $ourprice) or die(mysql_error());

$row_DetailRS1 = mysql_fetch_assoc($DetailRS1);

$totalRows_DetailRS1 = mysql_num_rows($DetailRS1);

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

<head>

<title>OurPrice | <?php echo $row_DetailRS1['mtTitle']; ?> Fundraising Auction Prize To Use At Your Fundraising Event</title>

<!--

.style3 {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; }

.style5 {

font-family: Verdana, Arial, Helvetica, sans-serif;

font-size: 12px;

font-weight: bold;

color: #FF0000;

}

-->

</style>

</head>

<body>

<tr>

<td rowspan="12"><img src="images/<?php echo $row_DetailRS1['largephoto']; ?>" alt="" name="bigpic" width="200" height="200" id="bigpic" /></td>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

</table>

</body>

</html><?php

mysql_free_result($DetailRS1);

?>

Report · Feb 27, 2010

jeffmg wrote:
Hi all. This is my problem. I have a MySQL database with a single table which contains a number of listings of albums by different musicians.

That's where your problem lies. You need one table for the artists, and a second table for the albums.

artist_id	artist
1	Queen
2	The Beatles
3	Rod Stewart

album_id	artist_id	album
1	1	Made in Heaven
2	1	Innuendo
3	2	Please, Please Me
4	2	Revolver
5	2	Sergeant Pepper's Lonely Hearts Club Band
6	3	Every Picture Tells a Story

Report · Feb 28, 2010

Hi David - thanks for your input, I appreciate it. Sorry it's taken me a day or two to get back to you, but I've been away from the Mac for 48 hours.