Join two mysql tables together to get one image

Wed, 05/19/2010 - 04:26 — russellharrower

Hi I want to do the following.
I have a gallery table that says each gallery like
fruit
meat
veg

in another table I have all the fruit, veg and meat pictures.

What I want to do is when user clicks gallery it shows each gallery category but only one image from the category.

Wed, 05/19/2010 - 11:02 — dmorison

Hi Russell, If category has

Hi Russell,

If category has been passed to your script through, say, a $_GET parameter in the URL, then you can simply use it to define which table to select a single image from the images table, for example:

$sql = "SELECT image  FROM `images` WHERE category='".mysql_escape_string($_GET["category"])."' LIMIT 1";

Cheers,
David.

Sat, 05/22/2010 - 05:43 — russellharrower

Sorry, maybe I did not explain so well.
I need away so when a user first clicks on gallery in the menu, it shows them all the galleries that are availble for them to look at. but instead of just showing gallery category i want it to show one picture from each category.

Sat, 05/22/2010 - 07:50 — dmorison

Hi Russell, I understand -

Hi Russell,

I understand - perhaps something like this is what you're after....

<?php
  $galleries = array();
  $sql = "SELECT * FROM gallery ORDER BY gallery";
  $result = mysql_query($sql);
  while($row = mysql_fetch_assoc($result))
  {
    $galleries[] = $row["name"]; // I am assuming that name is a field in the `gallery` table
  }
  foreach($galleries as $gallery)
  { 
    $sql = "SELECT * FROM images WHERE gallery='".mysql_escape_string($gallery)."' ORDER BY RAND() LIMIT 1";
    $result = mysql_query($sql);
    $row = mysql_fetch_assoc($result);
    print "<p>";
    print "<img src='".$row["url"]."' />";  // I am assuming that url is a field in the `images` table
    print "<br />"; 
    print "<a href='gallery.php?gallery=".$gallery."'>".$gallery."</a>"; // this is the link to your gallery page
    print "</p>";
  } 
?>

Hope this helps!
Cheers,
David.

Tue, 05/25/2010 - 05:45 — russellharrower

It does but because I am

It does but because I am joining the galleries to the photos by ID, not by name, the ID is showing and not the name of the gallery
trying to work out how to fix this.