Jump to content
xisto Community
Sign in to follow this  
oxida

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

Recommended Posts

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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>";

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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";}

Share this post


Link to post
Share on other sites

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 :)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×
×
  • Create New...

Important Information

Terms of Use | Privacy Policy | Guidelines | 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.