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

Expand|Select|Wrap|Line Numbers

 
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

Expand|Select|Wrap|Line Numbers

 
<?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:

Expand|Select|Wrap|Line Numbers

 
// 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.

Expand|Select|Wrap|Line Numbers

 
SELECT at.authorName

FROM authorTable AS at

INNER JOIN authorArticleList AS aal

ON at.aID=aal.aID

WHERE aal.articleID = {$articleRow['articleID']}

Grouping results with PHP

re: Grouping results with PHP

re: Grouping results with PHP

re: Grouping results with PHP

Similar PHP bytes

/bytes/development

/bytes/it

/bytes/about