[SOLVED] looping through mysql rows, assigning to arrays

[evanct]

okay, so I have a table 'albums' and a table 'images' with several rows that are linked to their respective album via the field album_id. What I want to do is

 

1. index each row in 'albums' in a sequential array $a.

2. for each album array in $a, add a new key 'contents', which is another array.

3. get each row from 'images' that has the same album_id as the given album and assign the entire row to a sequential key in 'contents'.

 

here's my shot at it:

<?php
function getAlbums($user) {
	$sql="SELECT * FROM albums WHERE user_name='$user'";
	$result=mysql_query($sql,$this->connection);
	return $result;
}

function getAlbumContents($user) {
	$assoc=$this->getAlbums($user);
	$a=array();
	$i=0;
	while ($row=mysql_fetch_assoc($assoc)) {
		$a[$i]=$row;
		$albumid=$row['album_id'];
		switch ($row['album_type']) {
			case 0: $albumtype=TBL_IMAGES;
			break;
			case 1: $albumtype=TBL_AUDIO;
			break;
			case 2: $albumtype=TBL_DOCUMENTS;
			break;
		}
		$sql="SELECT * FROM ".$albumtype." WHERE album_id='$albumid'";
		$result=mysql_query($sql,$this->connection);
		if (mysql_num_rows($result)==0) {
			$a[$i]['contents']='No content found!';
		} else {
			$j=0;
			while ($row=mysql_fetch_assoc($result)) {
				$a[$i]['contents']=array();
				$a[$i]['contents'][$j]=$row;
				$j++;
			}
		}
		$i++;
	}
	return $a;
}
?>

 

It all works except this part:

$j=0;
while ($row=mysql_fetch_assoc($result)) {
$a[$i]['contents']=array();
$a[$i]['contents'][$j]=$row;
$j++;
}

 

what happens is only the last iteration of the while loop is put in 'contents'. so if i had three images in a given album it should give me this:

 

0 => ('contents' => (0 => image row 0 array)

                          (1 => image row 1 array)

                          (2 => image row 2 array))

 

but all i get is this:

 

0 => ('contents' => (2 => image row 2 array))

 

what am i doing wrong?

[RussellReal]

try something like this:

 

"SELECT * FROM images JOIN albums ON (albums.id = images.album_id) WHERE user_name = '{$user}'"

[evanct]

that didn't help...

[RussellReal]

oh I see what you wanted.. I figured 1 query would do better for you than two queries..

 

but here you go..

 

<?php
$q = mysql_query("SELECT * FROM albums WHERE user_name = '{$user}'");
$a = array();
while ($b = mysql_fetch_assoc($q)) {
	$g1 = mysql_query("SELECT * FROM images WHERE album_id = '{$b['id']}'");
	$currentRow = ($a[] = $b);
	$currentRow['contents'] = array();
	while ($b1 = mysql_fetch_assoc($q1)) {
		$currentRow['contents'][] = $b1;
	}
}
?>

[evanct]

OH, the problem was that I was defining $a[$i]['contents'] as an array within the while loop, right? So during each iteration it reset.