I think I need help writing a query...among other things
Posted: Sat May 20, 2006 7:54 am
I am running a query that references three tables in a MySQL database, and it almost works fine.
BOOKS(isbn, title, price,...)
CATEGORY (isbn, category)
AUTHOR(isbn, author)
The output is a list of books from a specific category and some of their attributes. The problem is some books have more than one author, and when this is the case I get more than one result for a particular title. I am wondering if anyone sees a way to loop through authors where there are more than one, each title displayed only once. This may be a job for the MySQL board... Thanks for looking.
BOOKS(isbn, title, price,...)
CATEGORY (isbn, category)
AUTHOR(isbn, author)
The output is a list of books from a specific category and some of their attributes. The problem is some books have more than one author, and when this is the case I get more than one result for a particular title. I am wondering if anyone sees a way to loop through authors where there are more than one, each title displayed only once. This may be a job for the MySQL board... Thanks for looking.
Code: Select all
<?php
$category = $_POST['category'];
if ($category == 'none') {
echo "<p>Please select a category</p>
<a href=\"search.htm\">Return to the search page</a>";
exit;
}
echo "<h2>Results for $category Books:</h2>" ;
$conn = mysql_connect('localhost', 'root', '');
mysql_select_db('iz', $conn);
$query = "SELECT books.*, author.author FROM books, author, category WHERE books.isbn = category.isbn AND books.isbn = author.isbn AND category.category = '$category'";
$result = mysql_query($query, $conn) or die(mysql_error());
$num_results = mysql_num_rows($result);
echo"<p>Results matching your query: $num_results</p>";
for ( $i=0; $i<$num_results; $i++){
$row = mysql_fetch_array($result);
$title = $row['title'];
echo '<p><b>' .($i+1). '. Title: ';
echo "<i>$title</i></b>";
echo '<br /> Author: ' . $row['author'];
echo '<br /> ISBN: ' . $row['isbn'];
echo '<br /> Price: ' . $row['price'];
}
echo "<h3 align=\"center\"><a href=\"search.htm\">Return to the search page</h3>";
?>