Distinct statement

lynettes

I was trying to get only one author to show; however, it is showing each author although there may be repeats. What did I do wrong?

//********************************************************************************************
//GETS DATA
//********************************************************************************************
$SQL = "SELECT DISTINCT record_num, author_last_name, author_first_name FROM $DB_TABLE ORDER BY author_last_name, author_first_name";
$RESULT = mysql_query($SQL, $CONNECTION) OR die("Query failed."); 
$NUM = mysql_num_rows($RESULT);
//********************************************************************************************
//********************************************************************************************
?>

<FORM METHOD='post' ACTION='authorsearch.php'>
<SELECT NAME='authorwanted'>
<OPTION VALUE='0' SELECTED>--- Browse Authors ---</OPTION>
<?PHP
//********************************************************************************************
//POPULATES COMBO BOX
//********************************************************************************************
while ($ROW = mysql_fetch_array($RESULT))
{
$RECORD = $ROW["record_num"]; 
$LNAME = $ROW["author_last_name"];
$FNAME = $ROW["author_first_name"];
echo "<OPTION VALUE='$RECORD'>$LNAME, $FNAME</OPTION>";

}
//********************************************************************************************
//********************************************************************************************
?>

lynettes

I fixed problem only to cause new one. I was getting duplicates because I asked for distinct record nums too. Now that I have fixed that, I'm getting no books found because I have no record num to match too how do I store first and last names to match to?

 
<FORM METHOD='post' ACTION='authorsearch.php'>
<SELECT NAME='authorwanted'>
<OPTION VALUE='0' SELECTED>--- Browse Authors ---</OPTION>
<?PHP
//********************************************************************************************
//POPULATES COMBO BOX
//********************************************************************************************
while ($ROW = mysql_fetch_array($RESULT))
{
$RECORD = $ROW["record_num"]; 
$LNAME = $ROW["author_last_name"];
$FNAME = $ROW["author_first_name"];
echo "<OPTION VALUE='$RECORD'>$LNAME, $FNAME</OPTION>";

}
//********************************************************************************************
//********************************************************************************************
?>
</SELECT>
<BR><BR>
<INPUT TYPE='SUBMIT' VALUE='View the Details!'>
</FORM>

<?PHP
//********************************************************************************************
//RETURNS INFO REQUESTED
//********************************************************************************************
if(isset($_POST['authorwanted']))
{
$RECORD = $_POST['authorwanted']; 
$SQL1 = "SELECT * FROM $DB_TABLE WHERE record_num = '" . $_POST["authorwanted"] . "' ORDER BY title";
$RESULT1 = mysql_query($SQL1, $CONNECTION) OR die("Query failed." );
$NUM1 = mysql_num_rows($RESULT1);
$LNAME1 = 'author_last_name';
$FNAME1 = 'author_first_name';
if ($NUM1 == 0)
{
echo "No books found";
}
elseif ($NUM1 > 0)
{
echo "<BR><BR>";
echo "<H3><U>$FNAME1 $LNAME1</U> has $NUM1 book(s) in the collection.</H3>";
echo "<BR>";
echo "<TABLE WIDTH='500' ALIGN='center' CELLSPACING='2' CELLPADDING='2' BORDER='1' FRAME='box'>";
}
while($ROW = mysql_fetch_array($RESULT1))
{
$TITLE = $ROW["title"];
$CALL1 = $ROW["call_number_1"];
$CALL2 = $ROW["call_number_2"];
$PAGES = $ROW["number_of_pages"];
$SUBJECT = $ROW["subject"];
$ISBN = $ROW["isbn"];
$SUBHEADINGS = $ROW["subheadings"];

echo "<TR><TD COLSPAN='4'><STRONG>$TITLE</STRONG></TD></TR>";
echo "<TR><TD><STRONG>Call Number(s):</STRONG></TD><TD COLSPAN='3'>$CALL1<BR>$CALL2</TD></TR>";
echo "<TR><TD><STRONG>ISBN:</STRONG></TD><TD>$ISBN</TD><TD><STRONG>Subject:</STRONG></TD><TD>$SUBJECT</TD></TR>";
echo "<TR><TD><STRONG>Subheadings:</STRONG></TD><TD COLSPAN='3'>$SUBHEADINGS</TD></TR>";
}
echo "</TABLE>";
}
//********************************************************************************************
//********************************************************************************************
?>

Roger_Ramjet

Lots of ways to do this. I guess that the authors and titles are being stored in the same table: $DB_TABLE.

You could move the authors to a seperate table with an id column that you store in the other table with the titles. Would make filling the combo box and returning all titles for the author easy. You would have to think about how you handle authors with the same name: there are lots of them about.

With your current setup

$SQL = "SELECT DISTINCT author_last_name, author_first_name FROM $DB_TABLE ORDER BY author_last_name, author_first_name"; 

while ($ROW = mysql_fetch_array($RESULT))
{
$RECORD = $ROW["author_last_name"] . ', ' . $ROW["author_first_name"];
echo "<OPTION VALUE='$RECORD'>$RECORD</OPTION>";

} 


if(isset($_POST['authorwanted']))
{
$RECORD = explode(", ",$_POST['authorwanted']);

$SQL1 = "SELECT * FROM $DB_TABLE WHERE author_last_name = '" . $RECORD[0] . "', author_first_name = '" . $RECORD[1] . "' ORDER BY title";

Remember, somewhere in all of this you are going to have to cater for different authors with the same name, even relying on middle initials will catch you out if it is a big catalogue.