Jump to content


Photo

mysql query output not displaying in foreach loop


  • Please log in to reply
4 replies to this topic

#1 apheli0n

apheli0n
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 13 October 2006 - 03:06 PM

Hello,

I am a newbie to php and to this forum...I'm posting this question after hours of troubleshooting with no found solution. My background is in ColdFusion which i am fairly fluent with. I'm basically trying to do the php equivalent of this coldfusion code...

<cfoutput query="bla" group="AlbumName">
#AlbumName#
<cfoutput>
#ListOfTracks#
</cfoutput>
</cfoutput>

The output i'm going for is:

Album Name 1
Track 1
Track 2
Track 3

Album Name 2
Track 1
Track 2
Track 3

Currently, the php code i'm using is this:




//Discog query
$sqldisog = "SELECT Albums.*, Tracks.* FROM Albums LEFT JOIN Tracks on Albums.Album_Name = Tracks.AlbumName WHERE ArtistName = '$aName'";
$resultdiscog = mysql_query($sqldisog)
                or die("Query failed: " . mysql_error()); 

while ($rowdiscog = mysql_fetch_array($resultdiscog)) $category[$rowdiscog["Album_Name"]][] = $rowdiscog["title"];
//album header info

foreach ($category as $cat=>$title_array)
{       

echo("<br/><b>$cat</b> ($resultdiscog[Album_Year]) <br/>");
echo("Label link <br/>");

//album tracklist
foreach ($title_array as $title) 
echo("$rowdiscog[TrackOrder]) asdf<br>");
} 

This php code is outputting the Album name correctly (which is currently set to $cat) and is even outputting the correct number of "asdf<br>" for each album, but for some reason i cant output any of the other data returned in the query by using...$rowdiscog[ColumnName]. Can someone please show me how to do this properly?

Thanks!

Thanks!!!

#2 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 13 October 2006 - 03:36 PM

Try changing this:

while ($rowdiscog = mysql_fetch_array($resultdiscog)) $category[$rowdiscog["Album_Name"]][] = $rowdiscog["title"];
//album header info

foreach ($category as $cat=>$title_array)
{       

echo("<br/><b>$cat</b> ($resultdiscog[Album_Year]) <br/>");
echo("Label link <br/>");

//album tracklist
foreach ($title_array as $title) 
echo("$rowdiscog[TrackOrder]) asdf<br>");
}

To this:

while ($rowdiscog = mysql_fetch_array($resultdiscog)){
   $category[$rowdiscog['Album_Name']] = $rowdiscog['title'];

   //album header info
   foreach ($category as $cat => $title_array){
      echo "<br/><b>$cat</b> {$resultdiscog['Album_Year']} <br/>";
      echo "Label link <br/>";

      //album tracklist
      foreach ($title_array as $title){
         echo "$rowdiscog['TrackOrder'])asdf<br>";
      }
   }
}

All I've done is tidy up your code.  I'll post and easier way for you shortly.

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#3 apheli0n

apheli0n
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 13 October 2006 - 04:34 PM

Thanks for your help...though the cleaned up code you posted doesn't work quite right...its almost a step backwards because it repeats the album name for each track in the album.

Right now, with the original code i posted, the output is like this...

Album Name 1
asdf
asdf
asdf
asfd
asdf
asdf
asdf

Album Name 2
asdf
asdf
asdf
asdf
asdf
asdf
asdf
asdf
asdf
asdf
asdf

So its outputting the number of rows for each album correctly, but i just cant figure out how to output the track names from the query.



#4 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 13 October 2006 - 05:02 PM

No problem, assuming the column that holds the track name is called Track_Name then give this a try

<?php
//Discog query
$sqldisog = "SELECT Albums.*, Tracks.* FROM Albums LEFT JOIN Tracks on Albums.Album_Name = Tracks.AlbumName WHERE ArtistName = '$aName' ORDER BY Albums.Album_Name";
$resultdiscog = mysql_query($sqldisog) or die("Query failed: " . mysql_error());

$album_title = "null";
while ($rowdiscog = mysql_fetch_array($resultdiscog)){
   if (is_null($album_title) || strcmp($album_title,$rowdiscog['Album_Name']) !=0 ){
      $album_title = $rowdiscog['Album_Name'];
      
      // Album header info
      echo "<br>$album_title - $rowdiscog['Album_Year']<br>\n";
   }
   // Track listing
   echo "$rowdiscog['Track_Name']<br>\n"; // Change this variable to the name of your column
}
?>

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#5 apheli0n

apheli0n
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 13 October 2006 - 05:55 PM

Thanks! I was able to get it to work from that code...now i can define all the variables I want from the query by using this code:

$album_title = "null";
while ($rowdiscog = mysql_fetch_array($resultdiscog)){
   if (is_null($album_title) || strcmp($album_title,$rowdiscog['Album_Name']) !=0 ){    
      // Album header info
	  $album_title = $rowdiscog['Album_Name'];
	  $album_year = $rowdiscog['Album_Year'];<br />
	  $album_label = $rowdiscog['LabelName'];
	  $labelurl = $rowdiscog['LabelURL'];
      echo "<br><b>$album_title</b> ($album_year)<br>\n";
   }
   // Track listing
   	  $TrackName = $rowdiscog['TrackName'];
	  $TrackNum = $rowdiscog['TrackOrder'];
   echo "$TrackNum) $TrackName<br>\n"; 
}





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users