mysql and php, multidimensional result

[Gozil]

Hello everyone,
I have a bit of a problem here, lets me explain the database part first:
I want to select pictures from the database and each picture is connected to multi comments.

normally I would do this with a foreach loop and then a foreach loop for each comment in each picture like this:
[PHP]$pic = new pictureClass();
foreach($pic->getPictures( ) as $row) {
echo $row['name'];
foreach($pic->getPictureComm ent($row['id']) as $comment) {
echo $comment;
}
}[/PHP]

however this wont be possible for me because I have to get all the data in the first foreach loop because I dont want to call the class more then once (because Im doing it in flash and it takes longer time to call a php function in flash)

So like I said, I have to get all the data in one array and this is where I'm totally lost. I have no idea how to populate the multidimensiona l array when it comes to this.
This is what I have written but Its wrong:
[PHP] public function getPictures() {
$sql = "SELECT id, name FROM pictures ORDER BY added DESC";
$result = mysqli_query($t his->db,$sql);
if(!$result) throw new Exception("Erro r .....");

$arr = array();
while($row = $result->fetch_assoc( )) {
$arr[] = $row;
$comment = "SELECT comment FROM comments WHERE picture_id = '" . $row['id'] . "'";
$innerResult = mysqli_query($t his->db,$comment) ;
if(!$innerResul t) throw new Exception("Erro r ...");
while($innerRow = $innerResult->fetch_assoc( )) {
$arr[count($arr)-1]['comment'][] = $innerRow;
}
mysqli_free_res ult($innerResul t);
}
mysqli_free_res ult($result);

return $arr;
}[/PHP]

[Atli]

Hi.

How is the code not working?
Are you getting any error messages?

It looks fine to me at first glance, except for one thing.
In your second SQL query you enclose your $row['id'] in single-quotes, which you shouldn't. (Assuming that this ID is an integer).

In MySQL numbers should never be quoted, or they will be treated as a string, and the string "1" will not be equal to the integer 1.

[Gozil]

Thanks for the reply Atli, I will remove the single quotes around the int value.

However this is the part thats a bit messed up:
[PHP]
$arr[] = $row;
....
while($innerRow = $innerResult->fetch_assoc( )) {
$arr[]['comment'][] = $innerRow;
}[/PHP]

I want to select the latest array value and insert an array called "comment".
Im unsure if $arr[] creates a new array value in php and im also unsure if $arr[]['comment'] will create another new value of $arr instead of inserting a value to the last $arr value.
I probably confusing you right now, but its a bit confusing for me as well =)

[Atli]

This can be a bit confusing at first, I know :)

Whenever you do $arr[] = ... a new array element will be added to that array.
You can always specify the last element (count($arr)-1) to insert into the last element rather than create a new one.

What you did in your getPictures example in your first post should work fine.

But you don't really need to add the $row to the $arr array until at the end of the loop. You can use the $row array in your loop, rather than adding it to the $arr and messing with that.

For example:
[code=php]
$arr = array();
$articles = mysqli_query($d b, "SELECT id, title FROM articles");
while($article = $result->fetch_assoc( ))
{
$comments = $mysqli_query($ db, "SELECT author, text FROM comments WHERE articleID = {$article['id']}");
while($comment = $comments->fetch_assoc( ))
{
// Add the comments to the $article array, rather then the $arr array.
$article['Comments'][] = $comment;
// This just added a new element to the 'Comments' element of
// the $article array.
}

// And now, add the $article array to the $arr array.
$arr[] = $article;
}
[/code]
This way you don't have to worry about whether your using the correct element in the real array.

That would procude and array that look something like:

Array(
  [0] = Array(
    ['id] = x,
    ['title'] = 'String',
    ['Comments'] = Array(
      [0] = Array(
        ['author'] = 'String',
        ['text'] = 'String'
      ),
      [1] = Array(
        ['author'] = 'String',
        ['text'] = 'String'
      )
    )
  )
)

[Gozil]

Thanks a lot Atli, your example worked good :)

Although Is it possible to get the comments in the comment array, let me show you what I mean:
$arr['comments'][0] // this will output "array" right now, to get the comment I have to type:
$arr['comments'][0][0] // heres the comment.

This would somehow be easier to work with for this purpose:
$arr['comments'][0] // output comment.

The funny thing is if I remove the [] after $row['comments'] the 'comments' wont act as an array, like this:

$row['comments'] = $innerRow; // gives me a single value instead of an array once I output all the results
$row['comments'][] = $innerRow; // gives me an array with arrays for each value