Sorting related pictures to artists - PHP/MySQL

I need anyones help, i'll really appriciate that.

I have made 2 JOiN-s. As a fact I have 3 Tables

1. Table with names of Artists (m)
2. Table with Product iformations, in this case Picture addresses (p)
3. Table that has id of Artists and id of Products. (pmx)

here's the SELECT statement:

SELECT
    m.manufacturer_id ,
    m.mf_name ,
    p.product_id ,
    p.product_full_image
FROM
    jos_vm_product_mf_xref AS pmx 
JOIN
    jos_vm_manufacturer AS m ON m.manufacturer_id = pmx.manufacturer_id 
JOIN
    jos_vm_product AS p ON p.product_id = pmx.product_id 
WHERE
    m.mf_chars = 'm'

the effect that I'm trying to achieve is like on [http://www.ugallery.com/ArtistList.aspx?RC=1][1]

and in fact I'm getting just:

1. Name1 - prod1
2. Name1 - prod2
3. Name2 - prod1
4. Name1 - prod3

I want it, after foreach (smth) { smth } to get:

1. Name1 - prod1 prod2, prod3
2. Name2 - prod1 prod2

3. Name3....etc

   <ul>
   <?php foreach ($this->artistlist as $item) 
               {   ?>
   开发者_开发百科    <li><a href="index.php?option=com_virtuemart&page=shop.browse&manufacturer_id=<?php echo $item->manufacturer_id; ?>"><?php echo $item->mf_name; ?></a>
   
       <a href="index.php?page=shop.product_details&flypage=flypage.tpl&product_id=<?php echo $item->product_id; ?>&option=com_virtuemart">
           <img src="components/com_virtuemart/shop_image/product/<?php echo $item->product_full_image; ?>" height="75px">
       </a>
   
       </li>
   <?php   } ?>
   </ul>
   

this is PHP code that I'm using for now...

So plz, plz, plz.... Can anyone help me?

Here's the piece of the code

foreach ($this->artistlist as $picture) {
   if(!isset($artists[$picture['manufacturer_id']])) {   <---this is line 22
      $artists[$picture['manufacturer_id']] = array(
         'name'=>$picture['mf_name']
      );
   }

So... the error: Warning: Invalid argument supplied for foreach() in D:\Server\xampp\htdocs\ta\components\com_artists\views\artists\tmpl\default.php on line 35

foreach($artist['pictures'] as $pictureId=>$pictureFullImage) { <--- line 35

And another additional question: the outputed HTML is like this:

       <ul>
    <li>
        <a href="/ta/index.php?option=com_virtuemart&amp;page=shop.browse&amp;manufacturer_id=3">Giorgi Mihkeil</a>

**</li><li>** <--- and, can we get rid of this pieces? *<a href="/ta/index.php?option=com_virtuemart&amp;page=shop.browse&amp;manufacturer_id=$picture->artist_id"></a>* <-- what is this link? where does it come from?    
    <a href="/ta/index.php?page=shop.product_details&amp;flypage=flypage.tpl&amp;product_id=1&amp;option=com_virtuemart">
       <img src="/ta/components/com_virtuemart/shop_image/product/Lonely_Boat_4cfa773e83874.jpg" height="75px">
    </a>    <a href="/ta/index.php?page=shop.product_details&amp;flypage=flypage.tpl&amp;product_id=2&amp;option=com_virtuemart">
       <img src="/ta/components/com_virtuemart/shop_image/product/Naked_Couple_4cfbd12805f5b.jpg" height="75px">
    </a>    <a href="/ta/index.php?page=shop.product_details&amp;flypage=flypage.tpl&amp;product_id=4&amp;option=com_virtuemart">
       <img src="/ta/components/com_virtuemart/shop_image/product/Lonely_Boat_4d246dbef30e1.jpg" height="75px">
    </a></li></ul>

now this html give's smth like: <ul><li>NAME </li><li> <img1><img2><img3> </li></ul> can we get smth like: <ul><li>NAME <img1><img2><img3> </li></ul> ?

---

Usually, you'd use GROUP_CONCAT for that.

SELECT
    m.mf_name ,
    GROUP_CONCAT(p.product_id),
    p.product_full_image
FROM
    jos_vm_product_mf_xref AS pmx 
JOIN
    jos_vm_manufacturer AS m ON m.manufacturer_id = pmx.manufacturer_id 
JOIN
    jos_vm_product AS p ON p.product_id = pmx.product_id 
WHERE
    m.mf_chars = 'm'
GROUP BY m.mf_name

But if you're selecting pictures, you can't use that, you'll have to do it in your application language, using a Hash.

---

Assuming you've got your pictures in an array already:

$artists = array();

foreach($pictures as $picture) {
   if(!isset($artists[$picture['manufacturer_id']])) {
      $artists[$picture['manufacturer_id']] = array(
         'name'=>$picture['mf_name']
      );
   }

   $artists[$picture['artist_id']]['pictures'][$picture['product_id']] = $picture['product_full_image'];
}

foreach($artists as $artistId=>$artist) {
   echo $artist['name'].': ';
   foreach($artist['pictures'] as $pictureId=>$pictureFullImage) {
      echo "<img src='{$pictureFullImage}' />";
   }
}

This is a bit more complex than the solution presented by dqhendricks because it doesn't just store the ID of the artist, but also his/her name. That means there's an additional layer of arrays involved.

Based on your comment below: http://pastebin.com/2Cuyvp3U - I think this should do it? Don't know what the mf_prior field is you suddenly introduced though...

---

add an order by to your query

 ORDER BY  m.manufacturer_id ,p.product_id

An ordered query will be far easier to parse on your foreach loops.

---

You may want to process this PHP side. In seudo code.

$record_list = array();
while ($row_result) {
    if (!array_key_exists($row_result['name'], $record_list)) {
        $record_list[$row_result['name']] = array();
    }
    $record_list[$row_result['name']][] = $row_result['value'];
}
var_dump($record_list);

This will make a multi dimensional array in the format that you are looking for.