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

oxida · July 29, 2008

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 · July 29, 2008

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 · July 29, 2008

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 rel="stylesheet" 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 · July 29, 2008

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 · July 30, 2008

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 · July 30, 2008

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 · July 30, 2008

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 · August 4, 2008

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 rel="stylesheet" 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);?>

Sign In

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

Recommended Posts

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Important Information