Query returning too many results...

[Huxley]

I'm new to databases and PHP, so please excuse my newbie rubishness!

I'm in the process of redesigning my currently HTML-only anime review site in to PHP. So far everything has gone to plan - to accomodate multiple reivews of the same title, I've seperated the title and the reviews in to two different tables, while the authors and the images (several images can be associated with a single title) are also contained in individual tables.

Now, for the site's index I want to be able to display a list of the five latest reviews along with the author's name, the date of the review and a few lines of text from the review. I also want to display one image along with all this info, which I'm grabbing from the images table.

This works fine until mySQL hits the image table - because several images can be associated with the same title it will return a new row for each image, giving me more rows than I need if there's more than one image.

Here's my query as it stands:

$reviews = @mysql_query("
SELECT DISTINCT Areview.ID, TID, LEFT(Areview,200), Arname, Areviewdate, name, imagename 
FROM images, authors, Atitlelu, Areview 
WHERE visible='Y' AND AID=authors.ID AND ARID=Areview.ID AND IID=TID 
ORDER BY Areviewdate 
LIMIT 5
"); 

The table 'Atitlelu' contains both the review ID and the title ID, so there's no real need to reference the 'Atitle' table itself. The last entry into the WHERE clause is what I presumed would solve my problem (IID, the image ID, is indentical to the title ID with which the image is associated and TID, title ID, is used in the table Titlelu along with ARID, anime review ID) but obviously doesn't.

Where am I going wrong? Do I need to add a new column to the images table that would let me single out one image?

Any help on this matter would be fantastic!

John

[frasernm]

The easiest way would be to use a second query to fetch the image to select all the images for that one film.

You could finish the query "LIMIT 1" to only return a single image, or perhaps select a row at random to vary the image.

[Huxley]

The easiest way would be to use a second query to fetch the image to select all the images for that one film.

You could finish the query "LIMIT 1" to only return a single image, or perhaps select a row at random to vary the image.

Ahh, thanks a lot for the reply! I tried calling the images in a separate query but it wouldn't work because there's no way for mySQL to know which images are associated with which review without referencing the review and title tables. In the end I solved my problem by adding a new column to the images table that singles out one image per title with Y/N. I don't know if this is the most efficient way to solve the problem but it will have to do for now

Anyway, thanks for the help!

Out of interest, how would I select a row at random?

[r937]

In the end I solved my problem by adding a new column to the images table that singles out one image per title with Y/N.

excellent solution

as for random, add this to your query --

ORDER BY RAND() LIMIT 1

[jrickards]

I ran into the same problem recently where I was joining two tables and the results were multiplied. One table was a list of professors and the other table was the list of courses currently offered, within which was a foreign key to the professor table such as:

Table: professor
ProfID
Name

Table: courses
CourseID
Code
Title

Table: schedule
SchedID
ProfID_fk
CourseID_fk

On the faculty page, I listed the professors (no problem) but then I also, in a second query, queried the schedule table, found what courses the current professor was teaching and listed them. If one professor taught 5 courses, each course was listed 5 times. If another taught 3 courses, all three courses were listed 3 times. I solved the problem using DISTINCT but I felt that was a cheat because it wasn't that there were multiple records, just multiple results. Any suggestions as to what I should be looking for? My SQL included WHERE schedule.ProfID_fk = professor.ProfID AND courses.CourseID = schedule.CourseID_fk. I tried an INNER LEFT JOIN but I must have coded it incorrectly because it failed and then the MySQL documentation indicated that NATURAL JOINs were the same as WHERE so I tried it and, with the exception of the multiple results, it worked.

[r937]

jules, i feel like i know you from somewhere

you have a many-to-many relationship, with the schedule table as the relationship or intersection or linking or junction table (it has many names)

the natural join blew up because it expects identically-named columns, and your join didn't have any

holler if this explanation was too brief

best regards

[jrickards]

jules, i feel like i know you from somewhere

We have communicated back and forth a couple of times on this and a couple of other db forums - but not face-to-face. My sisters and parents live in North York/Toronto/Mississauga and you probably saw that I am about 4 hrs north of you.

you have a many-to-many relationship, with the schedule table as the relationship or intersection or linking or junction table (it has many names)

the natural join blew up because it expects identically-named columns, and your join didn't have any

I didn't realize that they had to be the same field/column names. The database design software I used to layout the tables uses _pk and_fk to identify primary/foreign keys which I thought was quite useful but obviously, it has created a problem with natural joins.

holler if this explanation was too brief

I understand what you have said and I will give it a whirl but if it fails, expect to hear from me.

Good book by they way!! I have referred to it a few times over the last few months.