HOME       >>       Programming

Select All Rows With Same Name how to do this ???


oxida

I have a table called albums it contains the following:

albumidbandname
albumname

the following data is in it
albumid bandname albumname0 Grendel test album 11 Grendel test album 22 VNV test album 13 VNV test album 2
Now I want to display the albumname from only grendel or vnv or anyother band who comes in the table.

How can I do this ???

thanks in advance

truefusion

This is where the WHERE clause comes into play (assuming you're using PHP)—we'll use Grendel in this example:

$result = mysql_query("SELECT `albumname` FROM `albums` WHERE `bandname` = 'Grendel'");
To be able to pull up all ablums by the band, you'll have to do a little looping:
while ($r = mysql_fetch_assoc($result)){$albums[] = $r;}
Once that is done, you'll have to go through the albums array:
foreach ($albums as $key => $val){echo $albums[$key]['albumname'];}

Note: i chose mysql_fetch_assoc over mysql_result for two reasons: mysql_result has always caused me problems in the long run; you may later want to select more than one column.

oxida

thanks for your fast respond, it seems to work but it mixes up 2 albums because grendel has 2 albums: cool album 1 and cool album 2
when i apply your code it displays: ccool album 2

note: notice the: double c, whish is the first cool album 1 i think.

here is the link to see the page in action link to the bands page
Klik on grendel to go to the detailed page.

here is the code for the page:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://forums.xisto.com/no_longer_exists/ xmlns="http://forums.xisto.com/no_longer_exists/ http-equiv="Content-Type" content="text/html; charset=utf-8" /><title>Detailed band info</title><link href="style/style.css" type="text/css" /><?php require_once('inc/dbconnection.php'); ?> <?phpif (!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_details = "-1";if (isset($_GET['recordID'])) { $colname_details = $_GET['recordID'];}mysql_select_db($database_dbconnection, $dbconnection);$query_details = sprintf("SELECT * FROM bands WHERE bandname = %s ", GetSQLValueString($colname_details, "int"));$details = mysql_query($query_details, $dbconnection) or die(mysql_error());$row_details = mysql_fetch_assoc($details);$totalRows_details = mysql_num_rows($details);$colname_albums = "-1";if (isset($_GET['recordID'])) { $colname_albums = $_GET['recordID'];}mysql_select_db($database_dbconnection, $dbconnection);$query_albums = sprintf("SELECT albumname FROM albums WHERE bandname = %s ", GetSQLValueString($colname_albums, "int"));$albums = mysql_query($query_albums, $dbconnection) or die(mysql_error());$row_albums = mysql_fetch_assoc($albums);$totalRows_albums = mysql_num_rows($albums);?></head><body><?php do { ?> <fieldset name="Bandinfo" style="width: 600px;"><legend><span style="font-size: 15px; color: #99FF00;"><b>Band info</b></span></legend><table> <tr> <td rowspan="4" style="width: 150px; vertical-align: top;"> <div id="menu-header"> //Options</div> > <a href="add-album.php?recordID=<?php echo $row_details['bandname']; ?>">Add more albums</a> </td> <td><fieldset name="Bandname" style="width: 200px;"> <legend><b>Band Name</b></legend> <?php echo $row_details['bandname']; ?> </fieldset> </td> <td><fieldset name="Bandgenre" style="width: 200px;"> <legend><b>Band Genre</b></legend> <?php echo $row_details['genre']; ?> </fieldset></td> </tr> <tr> <td><fieldset name="Bandmembers" style="width: 200px; height: 200px;"> <legend><b>Band Members</b></legend> <?php echo $row_details['bandmembers']; ?> </fieldset></td> <td><fieldset name="Albums" style="width: 200px; height: 200px;"> <legend><b>Albums</b></legend> <?php while ($r = mysql_fetch_assoc($albums)){$row_albums[] = $r;}foreach ($row_albums as $key => $val){echo $row_albums[$key]['albumname'];} ?> </fieldset></td> </tr> <tr> <td colspan="2"> <fieldset name="Bio" style="width: 427px; height: 200px;"> <legend><b>Biography</b></legend> <?php echo $row_details['bio']; ?> </fieldset></td> </tr> <tr> <td style="text-align: right" colspan="2">[<a href="bands.php">Terug</a>]</td> </tr></table></fieldset> <?php } while ($row_details = mysql_fetch_assoc($details)); ?></body></html><?phpmysql_free_result($details);mysql_free_result($albums);?>


truefusion

Hmm. Can you do me a favor and run the following, after the while statement, and show me the results?

echo "<pre>";print_r($row_albums);echo "</pre>";

oxida

Ok i have pasted the code,

It shows this:

Array( [albumname] => cool album [0] => Array ( [albumname] => cool album 2 ))

To see it in action:
Link to bands.php

truefusion

That's how i thought it would be. You have two instances of the variable row_albums that give the variable mysql_fetch_assoc(). The foreach in my coding assumes that the array would be ordered like this:

Array( [0] => Array ( [albumname] => cool album 1 ) [1] => Array ( [albumname] => cool album 2 ) ...)
If you comment out the line near the top where the row_albums variable is initiated, that should fix the problem where the word gets cut.

But i need to update a certain part of the foreach so that each album appears on its own line:
foreach ($albums as $key => $val){echo $albums[$key]['albumname']."<br/>\n";}


oxida

truefusion thanks for your help, it worked well although I don't understand why

//$row_albums = mysql_fetch_assoc($albums);

displays only a cyou can see the result to klick on the link in one of my posts but i don't think it is necessary .thanks for your time and effort


oxida

while everything works ok, another problem occurred.When i add another band like: VNV Nation.And when i click on grendel to see the details page, it displays also the band VNV Nation.piece of code for the link, this should send the bandname to the detail page where it selects it from the database.

<td colspan="2"><a href="details.php?recordID=<?php echo $row_bands['bandname']; ?>"><?php echo $row_bands['bandname']; ?></a></td>

Wholde code of the details.php page.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://forums.xisto.com/no_longer_exists/ xmlns="http://forums.xisto.com/no_longer_exists/ http-equiv="Content-Type" content="text/html; charset=utf-8" /><title>Detailed band info</title><link href="style/style.css" type="text/css" /><?php require_once('inc/dbconnection.php'); ?> <?phpif (!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_details = "-1";if (isset($_GET['recordID'])) { $colname_details = $_GET['recordID'];}mysql_select_db($database_dbconnection, $dbconnection);$query_details = sprintf("SELECT * FROM bands WHERE bandname = %s ", GetSQLValueString($colname_details, "int"));$details = mysql_query($query_details, $dbconnection) or die(mysql_error());$row_details = mysql_fetch_assoc($details);$totalRows_details = mysql_num_rows($details);$colname_albums = "-1";if (isset($_GET['recordID'])) { $colname_albums = $_GET['recordID'];}mysql_select_db($database_dbconnection, $dbconnection);$query_albums = sprintf("SELECT albumname FROM albums WHERE bandname = %s ", GetSQLValueString($colname_albums, "int"));$albums = mysql_query($query_albums, $dbconnection) or die(mysql_error());//$row_albums = mysql_fetch_assoc($albums);$totalRows_albums = mysql_num_rows($albums);?></head><body><?php do { ?> <fieldset name="Bandinfo" style="width: 600px;"><legend><span style="font-size: 15px; color: #99FF00;"><b>Band info</b></span></legend><table> <tr> <td rowspan="4" style="width: 150px; vertical-align: top;"> <div id="menu-header"> //Options</div> > <a href="add-album.php?recordID=<?php echo $row_details['bandname']; ?>">Add more albums</a> </td> <td><fieldset name="Bandname" style="width: 200px;"> <legend><b>Band Name</b></legend> <?php echo $row_details['bandname']; ?> </fieldset> </td> <td><fieldset name="Bandgenre" style="width: 200px;"> <legend><b>Band Genre</b></legend> <?php echo $row_details['genre']; ?> </fieldset></td> </tr> <tr> <td><fieldset name="Bandmembers" style="width: 200px; height: 200px;"> <legend><b>Band Members</b></legend> <?php echo $row_details['bandmembers']; ?> </fieldset></td> <td><fieldset name="Albums" style="width: 200px; height: 200px;"> <legend><b>Albums</b></legend> <?php while ($r = mysql_fetch_assoc($albums)){$row_albums[] = $r;} foreach ($row_albums as $key => $val){echo $row_albums[$key]['albumname']."<br/>\n";}?> </fieldset></td> </tr> <tr> <td colspan="2"> <fieldset name="Bio" style="width: 427px; height: 200px;"> <legend><b>Biography</b></legend> <?php echo $row_details['bio']; ?> </fieldset></td> </tr> <tr> <td style="text-align: right" colspan="2">[<a href="bands.php">Terug</a>]</td> </tr></table></fieldset> <?php } while ($row_details = mysql_fetch_assoc($details)); ?></body></html><?phpmysql_free_result($details);?>



VIEW DESKTOP VERSION REGISTERGET FREE HOSTING

Xisto.com offers Free Web Hosting to its Members for their participation in this Community. We moderate all content posted here but we cannot warrant full correctness of all content. While using this site, you agree to have read and accepted our terms of use, cookie and privacy policy. Copyright 2001-2019 by Xisto Corporation. All Rights Reserved.