SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast Huxley's Avatar
    Join Date
    Mar 2004
    Location
    England
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Red face Query returning too many results...

    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:

    PHP Code:
    $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

  2. #2
    SitePoint Zealot
    Join Date
    Sep 2001
    Location
    glasgow
    Posts
    104
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Enthusiast Huxley's Avatar
    Join Date
    Mar 2004
    Location
    England
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 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.
    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?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Huxley
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot jrickards's Avatar
    Join Date
    May 2003
    Location
    Sudbury
    Posts
    131
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot jrickards's Avatar
    Join Date
    May 2003
    Location
    Sudbury
    Posts
    131
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    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.

    Quote Originally Posted by r937
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •