While loop issue

[Rope]

Hi all.  Apologies if this has been asked, but I really can't find a solution.

 

I have a while loop inside another, and both are using data from separate mysql tables.

 

<?php

//Connect to Db
include('library/db_connect.php.inc');

//Select all rows from main table where visible = 1, alphabetical order
$main_all = mysql_query("SELECT * FROM main WHERE visible = 1 ORDER BY name");

//Count number of rows in main table
$num_rows = mysql_num_rows($main_all);

//Get all rows from releases table
$releases_all = mysql_query("SELECT * FROM releases");


//Include header
include('header.php.inc');


//Loop to output all rows
while ($row = mysql_fetch_array($main_all)) {

//Start band div
echo("<div class=\"BandListItem\">");

	//Releases
	echo("\n<p class=\"Releases\">Releases: ");

	//While loop to pull releases that match the band id
	while($release_row = mysql_fetch_array($releases_all)) {
	$release_array = array($release_row['release_name'], $release_row['band_id']);
		if ($release_array[1] == $row['id']) {
			echo $release_array[0] . $release_array[1] . ", ";
		}	
	}

	echo("</p>");

//Close band div
echo("</div>");
}

//Include right col and footer
include('footer.php.inc');



?>

 

What's meant to happen is that the second while loop pulls the 'release_name' and 'band_id' from one table, but only echos the info if the 'band_id' matches the 'id' from the other table.

 

What actually happens is that the first loop is completed every time but the second one is only completed once.  What the inner loop echos is fine, but it doesn't do it for every instance of the outer one.

 

I've tried resetting the array (read up about that) and I've tried changing the 'while' loop to a foreach statement but to no avail.

 

There's an example I thought would help on the php.net 'while' entry, but that didn't work either.

 

Any suggestions would be appreciated.

[wildteen88]

You'll want to use a join within your first query. That way you only have to deal with 1 while loop.

 

Try the following:

<?php

//Connect to Db
include('library/db_connect.php.inc');

$sql = "SELECT m.*, r.realease_name FROM main m, realeases r WHERE m.visible=1 AND r.band_id = m.id ORDER BY m.name";
$result = mysql_query($sql) or die('Error: ' . mysql_error());

//Count number of rows in main table
$num_rows = mysql_num_rows($query);

//Include header
include('header.php.inc');

//Loop to output all rows
while ($row = mysql_fetch_array($result))
{
echo '<pre>' . print_r($row, true) . '</pre><hr />';
}

//Include right col and footer
include('footer.php.inc');

?>

The query I have used is untested. So it may not work. It'll probably need changing slightly.

[lemmin]

The loops look fine. I think the problem exists in your sql results. Try taking the "if ($release_array[1] == $row['id'])" criteria out of the inner loop and see what gets printed.

[Rope]

Thanks guys, I'll try both of these right now.

[Rope]

wildteen88: your solution seems to be on the money, it'll take a bit of playing with for me to display the info correctly though (about two years since I did any serious PHP)

 

lemmin: taking the if statement out results in all the 'release_name' results being echoed in the first instance of the outer loop, which is not quite right. hmm...

[wildteen88]

You're far better of doing a join than multiple queries. joins are quite easy once you get the hang of them.

 

To learn how to use joins go through the tutorials @ w3schools.com

[HuggieBear]

I agree, I have the w3schools link in my signature.

 

Regards

Huggie

[Psycho]

The problem with the original code is that the 2nd query is looped through on the first iteration of the first loop. So, on the second iteration of the first while loop there is nothing to loop through on the 2nd query. You could reset the pointer, but a single query wiht a join is the best method int his instance.

 

Try this (not tested, may be typos):

 

<?php

//Connect to Db
include('library/db_connect.php.inc');


$query = "SELECT r.release_name, m.name
          FROM releases r
            LEFT JOIN main m ON m.id = r.band_id
          WHERE m.visible = 1
          ORDER BY m.name";


//Select records from release table joined to
//the main table where visible = 1
$result = mysql_query($query);

//Include header
include('header.php.inc');

$current_band = '';

//Loop to output all rows
while ($row = mysql_fetch_array($result)) {

  if ($current_band!=$row['name']) {
    //Close preceeding band div
    if ($current_band!='') { echo "</p></div>"; }

    //Start band div

    echo "<div class=\"BandListItem\">$current_band<br>\n";
    echo "<p class=\"Releases\">Releases: \n";
  }

  //Add comma if not the first record for this band
  if ($current_band==$row['name']) { echo ", "; }

  //Show the release
  echo ['release_name'];

  //Set the current band
  $current_band = $row['name'];

}

//Close the last band div
echo "</p></div>";

//Include right col and footer
include('footer.php.inc');

?>

[Rope]

Thanks again guys.  mjdamato gets what I'm aiming for, however there must be something slightly wrong in there as it blanks out in the browser (i'm testing online, no localhost available)

 

I've modified wildteen88's code slightly and now I have it almost working.  Only problem is it displays like this:

 

name: BAND_1

releases: ALBUM_1

 

name:BAND_1

releases:ALBUM_2

 

---

 

name: BAND_2

releases: ALBUM_1

 

name:BAND_2

releases:ALBUM_2

 

---

 

Which is slightly wrong, as it should go:

 

name: BAND_1

releases: ALBUM_1, ALBUM_2

 

name: BAND_2

releases: ALBUM_1, ALBUM_2

 

but I guess that's something to do with the while loop.

[Rope]

The best I can do right now is with a modified version of wildteen's JOIN query, but it outputs a new instance of the band name for each release.  Is this likely to be a JOIN issue?

[wildteen88]

Try using mjdamato code (the while loop part) with your sql join.

 

Its a not a problem with the query. You've now got to process the results.

[Barand]

or

<?php


  $query = "SELECT m.name, GROUP_CONCAT(r.release_name SEPARATOR ', ') as releases, 
          FROM releases r
            INNER JOIN main m ON m.id = r.band_id
          WHERE m.visible = 1
          GROUP BY m.name";
  $res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");
  while (list($name. $rels) = mysql_fetch_row($res))
  {
    echo "Band: $name<br/>Releases: $rels<br/><br/>";
  }
?>