Grouping results with PHP

[kevenj]

Hello,

I'm a bit of a newcomer to PHP and am having an issue I can't overcome.

I've read some great posts on the issue of grouping results on this forum 1 and can happily display results under a common variable. The problem occurs as I want to display the common variable after the grouped results.

I'm attempting to write out the results of a query for journal articles in a citation format that lists authors before the journal title (common variable).

I have 3 tables.

Authors

aID | LastName
1 | Smith
2 | Jones
3 | Jacks

Pubs

pID | pTitle
1 | Article 1
2 | Article 2

AuthorsJoin
pID | aID
1 | 1
1 | 2
1 | 3

I'd like to output my results in the format

Jacks, Jones, Smith Article 1

I can currently output as the following using the code below.

Article 1 Jacks, Jones, Smith

SELECT 
  LastName,
  AuthorsJoin.aID,
  Authors.aID,
  AuthorsJoin.pID,
  Pubs.pID,
  PubTitle 
FROM Authors, AuthorsJoin, Pubs
WHERE Authors.aID=AuthorsJoin.aID
AND AuthorsJoin.pID=Pubs.pID
ORDER BY Pubs.PubTitle
 

<?PHP
$ptitle = "";
while ($row = mysql_fetch_array($result))
{
  if (strcmp( $ptitle, $row['PubTitle']))
  {
    echo "<br /><strong>$ptitle</strong> ";
    $ptitle = $row['PubTitle'];
  }
  echo $row['LastName'].', ';
}
?>
 

So the above will let me output the grouped results under a common variable, but I need something that will let me output the common variable after the grouped results... is this even possible?

Any help would be most appreciated!

Keven

[Atli]

Hi. Welcome!

As I understand the code you posted, you would probably not get the output you gave us. It would probably look a bit more like:
"Article1 Jones, Article1 Smith, Article1 Otherguy"

In any case...
The easiest way to get the output you want would probably be to use two simple queries instead of one complex query.

Consider this:

// Query all article names and ID's
$sql = "SELECT articleID, articleName FROM articleTable";
$articleResult = mysql_query($sql);
 
// Loop through all articles
while($articleRow = mysql_fetch_assoc($articleResult))
{
  // Get all authors that match the article
  $sql = "
    SELECT at.authorName 
    FROM authorTable AS at
    INNER JOIN authorArticleList AS aal
      ON aal.articleID = {$articleRow['articleID']}";
  $authorResult = mysql_query($sql);
 
  // Print the names of all authors
  while($authorRow = mysql_fetch_assoc($authorResult))
  {
    echo $authorRow['authorName'], " ,";
  }
 
  // Print the name of the article
  echo "<b>", articleRow['articleName'], "</b><br />";
}
 

This would give you a list of all authors for all articles, arranged as you suggested.
You would of course have to make this fit your code and databases.

P.S.
I've added [code] tags and re-formatted you query and code a bit to make it a little easier to read. Was careful not to change anything tho :P

[kevenj]

Thanks Atli, This will definitely give me something to play with tomorrow. I appreciate your help!

[kevenj]

I ended up adjusting the SQL a bit and it worked like a charm! Thanks again for the solution.

SELECT at.authorName
FROM authorTable AS at
INNER JOIN authorArticleList AS aal
ON at.aID=aal.aID
WHERE aal.articleID = {$articleRow['articleID']}