On Sat, April 8, 2006 10:12 am, David Doonan wrote: > I'm having trouble getting the correct results on a list page. > > The first query is pulling the name of active authors from the d/b > and linking to a list page that is supposed to return essay titles by > the requested author. The list page however is displaying essay > titles by all authors. > > No doubt something is wrong with the where clause in the List page > query. I've tried countless variations on the syntax for Author.ID = > Author.ID without success. > > Sorry for so basic a question. > > --------------------- > author page query = > > $query_GetAuthors = "SELECT Distinct Author.Autholr_Name, Author.ID, > Writings.Writings_Author FROM Author, Writings > WHERE Author.Autholr_Name = Writings.Writings_Author and > Author.ID = Author.ID"; This gets every author several times over, then throws away the duplicates. Not what you want. First thing: WHERE Author.ID = Author.ID This is just silly -- Author.ID will ALWAYS equal Author.ID It's a tautology, like, WHERE 1 = 1 Get rid of it. Next, you really should NOT be storing the Author Name in both tables. Suppose somebody gets married? Suppose Cassius Clay changes his name to Mohammed Ali. Suppose Madonna writes for you. You should store an Author_ID field ni Writings so that you are comparing the ID Numbers, not names that might change tomorrow. Finally, you are JOINing the Author table and Writings table here, and then throwing away all the info from the Writings table, just to get the Names. Either use JUST the author table to get JUST the names, or get BOTH their Writings *AND* their names. > $GetAuthors = mysql_query($query_GetAuthors, $connDerbyTrail) or die > (mysql_error()); > $row_GetAuthors = mysql_fetch_assoc($GetAuthors); > $totalRows_GetAuthors = mysql_num_rows($GetAuthors); > > -------------------- > author page link = > <a href="author.php?ID=<?php echo $row_GetAuthors['ID']; ?>"><?php > echo $row_GetAuthors['Autholr_Name']; ?></a> > > > > --------------------- > List page query = > > $query_GetAuthorList = "SELECT Writings.Writings_Author, > Writings.Writings_Title, DATE_FORMAT(Writings_Date, '%M %D, %Y') as > Writings_Date, Writings.Writings_Text, Writings.ID, > Author.Autholr_Name, Author.ID FROM Writings, Author > WHERE Writings.ID = Writings.ID AND > Author.Autholr_Name = Writings.Writings_Author AND > Author.ID = Author.ID > ORDER BY Writings.Writings_Date desc"; Again, Writings.ID will ALWAYS equal Writings.ID Author.ID will ALWAYS equal Author.ID Matching up the names SHOULD get you just one of each, if your data is not messed up... > $GetAuthorList = mysql_query($query_GetAuthorList, $connDerbyTrail) > or die(mysql_error()); > $row_GetAuthorList = mysql_fetch_assoc($GetAuthorList); > $totalRows_GetAuthorList = mysql_num_rows($GetAuthorList); -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
