SitePoint Sponsor |
|
User Tag List
Results 1 to 7 of 7
-
Mar 2, 2004, 14:28 #1
- Join Date
- Mar 2004
- Location
- England
- Posts
- 51
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
");
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
-
Mar 3, 2004, 06:28 #2
- 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.
-
Mar 4, 2004, 07:10 #3
- Join Date
- Mar 2004
- Location
- England
- Posts
- 51
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by frasernm
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?
-
Mar 4, 2004, 07:41 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Originally Posted by Huxley
as for random, add this to your query --
ORDER BY RAND() LIMIT 1
-
Jun 17, 2004, 13:35 #5
- 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.Jules
jrickards.ca
-
Jun 17, 2004, 17:04 #6
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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
-
Jun 18, 2004, 04:47 #7
- Join Date
- May 2003
- Location
- Sudbury
- Posts
- 131
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by r937
Originally Posted by r937
holler if this explanation was too brief
Good book by they way!! I have referred to it a few times over the last few months.Jules
jrickards.ca
Bookmarks