Page 1 of 1

I think I need help writing a query...among other things

Posted: Sat May 20, 2006 7:54 am
by mjmacarty
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.

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 />&nbsp; &nbsp; Author: ' . $row['author'];
  echo '<br />&nbsp; &nbsp; ISBN: ' . $row['isbn'];
  echo '<br />&nbsp; &nbsp; Price: ' . $row['price'];
	}


echo "<h3 align=\"center\"><a href=\"search.htm\">Return to the search page</h3>";





?>

Posted: Sat May 20, 2006 7:45 pm
by GeXus
If you group by authors i think that will work..

Posted: Sun May 21, 2006 3:41 am
by timvw
It all depends on what you exactly want to do... If you do want to show all the authors, you'll have to keep track of the last book_id... And as long as the book_id is the same, you're still displaying an author that belongs to that book...

Posted: Sun May 21, 2006 11:53 am
by mjmacarty
It all depends on what you exactly want to do... If you do want to show all the authors, you'll have to keep track of the last book_id... And as long as the book_id is the same, you're still displaying an author that belongs to that book...
That's what I thought too. But I tried all sorts of loops to evaluate when books.isbn was the same as author.isbn, and mostly end up with infinite loops. I found a function in MySQL (GROUP_CONCAT) that does exactly what is asked for. Here is a revised query for anyone interested :

Code: Select all

SELECT books.*, author.isbn AS auth_isbn, GROUP_CONCAT(author) FROM books, author, category WHERE books.isbn = category.isbn AND books.isbn = author.isbn AND  category.category = 'beginner'  GROUP by title ORDER BY books.title;

Posted: Sun May 21, 2006 10:07 pm
by dibyendrah
Your table design is ok if you think one author write many book but doesn't solve problem for book having many authors. Also, you are storing the isbn with category name in category table. That will create the redundancy of category name.

My approach :

Code: Select all

Books(isbn, title, price,...)
Book_Category(book_cat_rel_id, isbn, cat_id)
Category (cat_id, category)
Book_Author(book_author_rel_id, isbn, author_id)
Author(author_id, author_name, ...other author info)
Book_Category is the relationship table which stores the key of both book and category table thinking one book has many category and one category will be referenced by many books. Similarly, Book_Author is also a relationship table storing key of book and author table thinking one author writes many books and one books is written by many authors.

Cheers,
Dibyendra

Posted: Mon May 22, 2006 12:25 pm
by mjmacarty
Hmmm. Interesting. I figured since the Isbn is unique I could use it in conjunction with say "Category" to create a composite primary key in the CATEGORY table. This allows a book to belong to many categories. I guess the big downfall to my design is redundancy of Category everytime a specific book falls into a specific category. I think this should be the case with authors as well. Combine Author and Isbn for the primary key. Do you see potential data anomalies?

By the way, in my original post, underlined attributes are primary and italicized attributes are foreign.