Multidimensional Array Query

[luminous]

Hey,

 

Basically I have a table of artist details, a table of exhibitions. One artist can be a part of many exhibitions, the exhibitions table has an 'artistid' column. I simply need to extract the exhibitions the artist is linked too. However, for every exhibition the aritst is linked too, it pulls up the artist details each time, but I only need the artist values once!

Code: [select]

function find_artists($id)
{
   $query = sprintf("SELECT * from EXHIBITIONS
                 WHERE exhibitions.artistid = %s", mysql_real_escape_string($id));

   
   $result = mysql_query($query);

   
   $number_of_posts = mysql_num_rows($result);
   if($number_of_posts == 0)
      {
         return false;
      }
      
   $row = resultToArray($result);
   
   return $row;
}

function resultToArray($result)
{
   $result_array = array();
   for ($i = 0; $row = mysql_fetch_array($result) ; $i++)
      {
         $result_array[$i] = $row;
      }
      
      return $result_array;
}

$posts = find_artists(1);
print_r($posts);

also would i then go about printing it out to screen like

<?php echo $artists[1]['exhibitionname'];?>

 

please help!

[fenway]

I don't follow -- you're asking why you're getting duplicate exhibitionname fields/

[luminous]

sorry let me explain.

 

For each exhibition i pull from the database, i get the entire artist profile with it. Let's say I have an artist with 10 exhibitions, with each array I get from the database filled with the exhibition details, I'll get all the rows from the artist profile aswell.

So I'll have 10 unique exhibition arrays and for each of those 10 replicated artist profiles from the database, whereas I only want one.

 

Thanks!

[fenway]

I only see a single statement, so I don't see where you're getting back anything from a second table.

[kickstart]

Hi

 

I can see 2 options. Either you use a single SQL statement and bring back all the artist details for each exhibition (down side is more details brought back for each row), or you bring back all the artist details, store them in an array seperatly and then bring back the exhibition details (down side is that you need 2 seperate queries).

 

Personally I would bring back everything in one query. Likely to be more efficient than doing 2 queries.

 

All the best

 

Keith

[luminous]

whoops! my mistake! that was from an amended version i was trying. The actual statement I'm using is:

 

$query = sprintf("SELECT * from EXHIBITIONS, ARTISTS
                 WHERE exhibitions.artistid = %s", mysql_real_escape_string($id));

 

i was thinking of trying to put a LIMIT = 1 in there? not sure how to apply that to just artists though?

[fenway]

Yikes... you're missing a ON condition for your JOIN.