evanct Posted July 2, 2009 Share Posted July 2, 2009 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? Link to comment https://forums.phpfreaks.com/topic/164469-solved-looping-through-mysql-rows-assigning-to-arrays/ Share on other sites More sharing options...
RussellReal Posted July 2, 2009 Share Posted July 2, 2009 try something like this: "SELECT * FROM images JOIN albums ON (albums.id = images.album_id) WHERE user_name = '{$user}'" Link to comment https://forums.phpfreaks.com/topic/164469-solved-looping-through-mysql-rows-assigning-to-arrays/#findComment-867595 Share on other sites More sharing options...
evanct Posted July 2, 2009 Author Share Posted July 2, 2009 that didn't help... Link to comment https://forums.phpfreaks.com/topic/164469-solved-looping-through-mysql-rows-assigning-to-arrays/#findComment-867598 Share on other sites More sharing options...
RussellReal Posted July 2, 2009 Share Posted July 2, 2009 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; } } ?> Link to comment https://forums.phpfreaks.com/topic/164469-solved-looping-through-mysql-rows-assigning-to-arrays/#findComment-867601 Share on other sites More sharing options...
evanct Posted July 2, 2009 Author Share Posted July 2, 2009 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. Link to comment https://forums.phpfreaks.com/topic/164469-solved-looping-through-mysql-rows-assigning-to-arrays/#findComment-867610 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.