Grab 1 image from 2nd table per listing

Databases
1

Hi,

I have 2 tables… one for listings and the other for photos… I havnt been able to display one image per listing trying a query within a loop and also a table join… Both options are briging back the same photo for all listings, even listings with no photos…

the link between the tables is “listingid”

How can I acheive this?

Thanks

2

could you do a SHOW CREATE TABLE for each table please

3 

CREATE TABLE listings (
  listingid int(25) NOT NULL auto_increment,
  datelisted datetime NOT NULL default '0000-00-00 00:00:00',
  location varchar(55) NOT NULL default '',
  type varchar(55) NOT NULL default '',
  comments varchar(255) NOT NULL default '',
  PRIMARY KEY (listingid)
) TYPE=MyISAM;


CREATE TABLE listingphotos (
  photoid int(25) NOT NULL auto_increment,
  listingid int(5) NOT NULL default '0',
  photoname varchar(55) NOT NULL default '',
  PRIMARY KEY (photoid)
) TYPE=MyISAM;
4

here ya go, one photo per listing –

SELECT listings.listingid
     , listings.datelisted
     , listings.location
     , listings.type
     , listings.comments
     , MAX(listingphotos.photoname) AS photoname
  FROM listings
LEFT OUTER
  JOIN listingphotos
    ON listingphotos.listingid = listings.listingid
GROUP
    BY listings.listingid

by the way, int(25) and int(5) are the same size, they both hold numbers up to 2 billion

5

Thanks… Its producing the folowing error:

mysql_fetch_array(): supplied argument is not a valid MySQL result resource in

Checked the field names and it all looks ok?

6

bummer

please run it outside of php and show me the actual mysql error

7

Ops my bad it is actually working thanks…

I just need to resurch what the MAX does for the query…

8

Looks like max just grabs the latest file with the largest id number… Can I grab the photos by random?

Thanks

9

If the file details (name, etc) are kept in an array, if you don’t want all the photos then you could use array_rand() on the array containing the photos. If you want all the photos but in a random order then you could use shuffle() on the array. You’ll probably need to have php create an array of the listings then for each listing add a sub-array containing the photos for that listing.

10

actually, in this case it retrieves the photo with the highest name, not the largest id number

random is a lot harder to do

why don’t you add a column to the listingphotos table, a flag that you can turn on for each listing, on the photo which you want returned as the “default” for each listing

11

I did think about that, but its more options for the members to set… its not really an issue for what photo is displayed random would have just spiced it up for general browsing