David Doonan wrote:
I'm having trouble getting the correct results on a display page.
The first query is pulling the name of active authors from the d/b and
sending a request to only 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";
$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";
$GetAuthorList = mysql_query($query_GetAuthorList, $connDerbyTrail) or
die(mysql_error());
$row_GetAuthorList = mysql_fetch_assoc($GetAuthorList);
$totalRows_GetAuthorList = mysql_num_rows($GetAuthorList);
david
David--
First of all, yours is purely a SQL problem, so would get better
response from the PHP-DB or MySQL list.
But a quick glance at your SQL shows that your second query returns all
writings by all authors because you don't include anything to narrow it
down to one author:
WHERE
Writings.ID = Writings.ID
AND
Author.Autholr_Name = Writings.Writings_Author
AND
Author.ID = Author.ID
First of all:
"Writings.ID = Writings.ID " and "Author.ID = Author.ID"
are nonsense. Every query will match those crieria. In other words,
you'd probably best take an hour or so to RTF 'Intro to SQL M.'
Here's another hint: You have no PHP variable in your SQL query. That
means you execute the same exact query every time you run it. So you
will get the same exact results every time.
So your solution is this: Include a PHP variable in your SQL query to
specify which author you want to select.
You probably want something like this:
WHERE
Author.Author_Name = Writings.Author_Name
AND
Author.ID = '$MySelectedAuthorID'
(but remember to define $MySelectedAuthorId before running it :)
Good luck and post again with your progress.
John
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
