RETRIEVING DATA FROM FOUR TABLES 1

[MichaelHooker]

Please forgive what's probably a basic query for all of you. And I don't know the correct terminology yet. I need to retrieve data from four tables into one php "$result" with one MySQL query. The tables are configured as follows:

Images
ImageID (primary key & the only unique field)
Serial
Photographer (all photographers are listed once in the Posters.Photographer field, ie many to one)
Operator (only some operators are listed in the Operators.Operator field, not all, ie many to the occasional one)
Subject (all subjects are listed once in the Subject.Subject field, ie many to one)

Operators
Operator (primary key)
OpField1
OpField2
etc

Photographers
Photographer (primary key)
PhoField1
PhoField2
etc

Subjects
Subjects (primary key)
SubField1
SubField2
etc

The task: I need to select ALL images from the Images table meeting criteria set by conditionals on fields in that table, eg 'where Serial = "ABCDE"'. For each hit I need to fetch all the relevant fields from all four tables, relating to the Image, the Operator (where they exist), Photographer and Subject. Obviously I don't need to retrieve the linking fields twice.

"The answer's going to be a left join" I hear you thinking - well, something like that. Because a straight join between Images.Operator and Operators.Operator results in the correct image data not being retrieved when the particular operator does not appear in the Operators table (there are good reasons for this, the details for many operators simply don't exist or can't be traced).

So, either I add all the Operators that occur in the Images table to the Operators table, even though there is nothing to put in the detail fields, or I need a query that successfully combines all the correct kinds of joins. I've found a few likely-looking examples to follow on the internet and in books, but all sorts of unexpected things happened when I tried them. I've read so much about joins that my head's ing. And I don't learn so fast these days (no hair left to keep the few remaining brain cells warmed up). I have a suspicion that it's actually less complicated than the guidance makes it out to be...

So I'd be very grateful if someone could rescue me and explain how I put together a query which does what I said 3 paragraphs ago, which I hope makes sense to you. I promise to put my woolly hat on and try to make it sink in.

Thanks for listening,

Michael Hooker

 

[Sleidia]

Sorry if I misunderstood your question, but is this what you're trying to do? :

SELECT
tb_images.*,
tb_operators.*,
tb_photographers.*,
tb_subjects.*
FROM
images AS tb_images,
operators AS tb_operators,
photographers AS tb_photographers,
subjects AS tb_subjects
WHERE
serial LIKE 'something'
AND
tb_images.operator = tb_operators.operator
AND
tb_images.photographer = tb_photographers.photographer
AND
tb_images.subjects = tb_subjects.subject

 

[Sleidia]

I meant : tb_images.serial LIKE 'something'

 

[MichaelHooker]

Thanks, but that only seems to return rows from the Images table where there is a match between images.subject and subjects.subject. Where there is no matching entry to be found in subject.subject, the relevant row from Images is not being returned - that would need what I understand to be a left join (but I may be wrong on that as my attempts to include a left join don't work as expected).

I need the "WHERE" to over-ride the "AND". The effect of

AND
tb_images.subjects = tb_subjects.subject

is to only return rows from the images table where that condition is met.

If possible, I would prefer no ANDs at all, as there is a danger that if the various linking fields get out of synchronisation (typing error, accidental deletion or whatever) I would never discover the mistake: it's better to return a set of data with empty fields than not to return anything.

I think the answer may be to use JOIN and LEFT JOIN (?) as in an answer given recently in this forum, but that's one of the examples I failed to make work - and the questioner's final working code was not provided by him.

Thanks

Michael Hooker

 

[r937]

select i.ImageID
     , i.Serial
     , i.Photographer    
     , p.PhoField1       
     , p.PhoField2       
     , i.Operator
     , o.OpField1
     , o.OpField2
     , i.Subject
     , s.SubField1 
     , s.SubField2 
  from Images as i
left outer
  join Photographers as p
    on p.Photographer = i.Photographer
left outer
  join Operators as o
    on o.Operator = i.Operator
left outer
  join Subjects as s
    on s.Subject = i.Subject
 where i.Serial = 'ABCDE'

note which table the key fields in the result set come from

for example, if there is an operator in the images table which isn't found in the operators table, the result set will still show the operator in the images table but the other fields from the operators table will be null -- so i.operator will have a value but o.operator will no, but we don't show o.operator in the result

r937.com | rudy.ca

 

[MichaelHooker]

the result set will still show the operator in the images table but the other fields from the operators table will be null

Thank you, that's precisely what I was aiming for! The books say that's what should happen, it's just that I couldn't make it happen. I can test for nulls in my php and insert "unknown" or something like that in the place-holders on the web-page. If it comes up "unknown" when it shouldn't I'll know there is an error that needs correcting.

I'll try it out today and let you know.

Michael Hooker

 

[Sleidia]

I was certain that r937 would come to your rescue!

 

[MichaelHooker]

Sorry, but in phpAdmin I got this (cut and pasted) response:-

#MySQL said:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT OUTER JOIN Photographers AS P ON P.Photographer = I.Photographer LEFT OUTER JOIN Operato' at line 1

This is my query, cut and pasted:

SELECT I.ImageID, I.Serial, I.Type, I.Msn, I.Operator, I.PhotoDate, I.PostDate, I.PhotoNotes, I.History, P.Nickname, P.Webname, P.Weburl, O.Opurl, O.Iatacode, O.Icaocode, O.Callsign
          FROM Images AS I,
          LEFT OUTER JOIN Photographers AS P ON P.Photographer = I.Photographer
          LEFT OUTER JOIN Operators AS O ON O.Operator = I.Operator
          ORDER BY I.ImageID

I omitted the WHERE clause (so as to retrieve all records from the Image table, and be able to tell from the number retrieved that this matched the number of records in the table). I omitted all reference to the Subject table (for simplicity) and added an ORDER BY clause, which helps me check through the results.

The web-host uses MySQL 5.0.27. I had a look at the documentation, which is way over my head, but it seems that there have been changes in how JOIN works. Could this be the problem?

Thanks

Michael Hooker

 

[r937]

the problem is the dangling comma, right where the error message says to look

r937.com | rudy.ca

 

[MichaelHooker]

Oh, Enfer! (excuse my French).

From now on I'm programming in a bigger, emboldened, font so I can see these things. On my laptop it's hard to tell if you've got a stop, a comma or a speck of dust on the screen.

The code works, and it works very nicely, thank you. You can see it in action at a test page on my website (moderator please note, this is totally non-commercial, nothing is sold or even advertised):

http://www.avphotosonline.org.uk/PHPTests/newtable7.php

The left joins pull out the details in the photographer and aircraft rows, and they will pull more details into the operator rows when I've managed to actually populate the operator table some more. You can see an example with all possible aircraft and operator fields shown if you scroll down to aircraft 5B-DAP on page 1 - number 57 in the page of 100.

Yes, I know a few thumbnail images are missing, and the large-size images aren't yet available, but it's a work in progress. And it's only been tested in IE7 so far.

Thanks <very> much indeed for your help. I can make lots more progress now on the process of upgrading the website from its rather dodgy 2001 coding that only works in IE, and sometimes not even then.

Michael Hooker