I'm have a problem with my joined tables! I am now getting 6 of 1 image

[vet911]

I'm have a problem with my joined tables! I am now getting 6 of 1 image before it goes on to the next one. I wasn't getting this before the join. Can anyone help?

Thanks in advance.

 

<?php
include 'config1.php';

$search=$_GET["search"];
// Connect to server and select database.
mysql_connect($dbhost, $dbuser, $dbpass)or die("cannot connect");
mysql_select_db("xxxxx")or die("cannot select DB");

$query = "SELECT table1.year, table2.year, table2.group, table2.image1, table1.tn, table1.title, table1.link FROM table1 LEFT JOIN table2 ON table1.year =  table2.year";
$result = mysql_query($query) or die(mysql_error());


// keeps getting the next row until there are no more to get
if($result && mysql_num_rows($result) > 0)

{
    $i = 0;
    $max_columns = 6;
    echo "<table>";
echo "<br>";
    while($row = mysql_fetch_array($result))
   {
       // make the variables easy to deal with
       extract($row);

       // open row if counter is zero
       if($i == 0)

          echo "<tr>";

          echo "<td align=center>";
          ?>
          <div style="float: left;">
          <div><a href="<?php echo $link; ?>"><img src="<?php echo $tn; ?>"></a></div>
	  <div><?php echo $title; ?></div>
	  </div>


          <?php
          echo "</td>";

       // increment counter - if counter = max columns, reset counter and close row
       if(++$i == $max_columns)
       {
           echo "</tr>";
           $i=0;
       }  // end if
   } // end while
} // end if results

// clean up table - makes your code valid!
if($i > 0)
{
    for($j=$i; $j<$max_columns;$j++)
        echo "<td> </td>";
   echo '</tr>';
}
mysql_close();
?>
</table>

[fenway]

Then you have a one-to-many relationshiop.

[vet911]

Thanks for the info. I kind of knew something was different after the join of the tables.

I have attached pdfs of the tables, table1, table2. You will see the problem is one table has

images of thumbnails, the other table has multiple images that are linked to one of the thumbnails. My thought was to link them so I could show all the thumbnails for a particular year  and then when I click the link show all the images that are part of that particular thumbnail images family. (ie 1954-01 when clicked would show all 1954-01-01, 1954-01-02, 1954-01-03 etc.) Currently this only works when they are only one set of pictures.

I am new to PHP, mysql, but enjoy the challange. Can you give me some advice?

 

[attachment deleted by admin]

[optikalefx]

i just made a video about this. use group_concat

 

it will take your 1 to many relationship and join it by comma to be used.