Fetching All Rows for Specific Field

[EternalSorrow]

I have a simple database set up for story awards given out by different groups.  Here are some of the fields I'm using:

 

Author

Title

Year

Summary

 

Some of the rows are nearly identical with the same Author and Title, but sometimes a different year or or others have a Summary for the story where otherwise it would be empty for a different row.  The problem I'm having is I want to fetch the summary of the story from all rows where the Author and Title are identical.  Currently the code is fetching a random row with the same Author and Title, but won't show the Summary if that specific row doesn't have the summary filled in (and where other rows of nearly identical information would have the summary).

 

Here's an example of nearly identical rows:

 

'Boring Title', 'Boring Author', '', '2012'

'Boring Title', 'Boring Author', 'Boring Summary', '2012'

 

How would I go about fetching all rows with the identical Author and Title and then showing the Summary even if only one of those rows has information in the Summary field?  Much obliged for any help.

 

Here's the code I'm working with:

 

<?php
mysql_connect(localhost,db_user,pw);
@mysql_select_db(db_database) or die( "Unable to select database");

$quer="SELECT * FROM archives ORDER BY RAND() DESC LIMIT 1";
$re = mysql_query($quer);

while ($ro = mysql_fetch_array($re))
{
extract($ro);

$please = '';
if (strlen($ro[summary]) == 0) {
$please = "The summary is empty.  Please help the Archives by <a href=\"edit.php?cmd=edit&author=$author&title=$title\">filling out the summary</a>."; 
}

$select_author = mysql_query("SELECT DISTINCT author FROM archives WHERE `title` = '" . mysql_real_escape_string($title) . "' ") or die (mysql_error());

$aut = "";
while ($row2 = mysql_fetch_array($select_author)) {
   $aut .= "<a href=\"author.php?author={$row2[author]}\">$row2[author]</a> & ";
}

$aut = substr($aut,0,-3);

echo '<div style="margin: 5px;">
'.$summary.''.$please.'

<p style="float: right; width: 100%; margin-top: 10px; text-align: right;"><i> ~ <a href="info.php?author='.$author.'&title='.$title.'">'.$title.'</a> by <a href="author.php?author='.$author.'">'.$aut.'</a></i>
</div>';

}
?>

[requinix]

Why are these nearly identical rows even allowed in the database?

[EternalSorrow]

Since I can't seem to edit my original post to expand the 'nearly identical' rows to actually reflect how different they can be, I'll post a larger view here:

 

'Boring Title', 'Boring Author', 'Empty Summary', '2010', 'Group A', 'Category A', 'Rating', 'Nominated'

'Boring Title', 'Boring Author', 'Boring Summary', '2012', 'Group B', 'Category F', 'Rating', 'Not Nominated'

 

Hopefully this will avoid further confusion, and maybe garner some assistance.

[requinix]

One more question: what if there's only one row and it doesn't have a summary? Should it be listed with an empty summary or not listed at all?

[batwimp]

It seems like you could make this a lot more efficient by adding more tables and joining them (relational database). Are you familiar with this concept?

[EternalSorrow]

@requinix  By default the rows do not start out with a summary.  I'm not sure what you're referring to when you say 'listed,' but the ORDER is RAND(), so with a refresh of the page a row with an empty summary may appear.

 

 

@batwimp Yes, I'm familiar with joining tables, and yes, I am very much wanting to avoid creating more than one table.  Unless, of course, you can convince me it will be less code/work than the current setup (I tried multiple tables in the past and found the setup infinitely more troublesome than having a single file).

[xyph]

It depends on how static your data is.

 

Using relationships allows you to change things later with ease. If your data is static, or probably not going to change, you won't 'gain' much by moving to a multi-table set up.

Relationships also save you a bit of space. This means little for a few hundred rows, but when you get into 6-digit row counts, a kilobyte difference in row size could hurt, even when properly indexed.

 

To summarize, it's more work if your data follows static patterns, and won't change often. It's less work if or when you need to make changes.

 

Also, avoid using ORDER BY RAND() if your database gets beyond a couple hundred rows. It forces MySQL to pretty much replicate your entire table before returning results. There's tons on Google about this, and many different solutions that apply to different specific problems.