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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mjmacarty
Forum Commoner
Posts: 37
Joined: Tue Feb 21, 2006 3:20 pm

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

Post 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>";





?>
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post by GeXus »

If you group by authors i think that will work..
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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...
mjmacarty
Forum Commoner
Posts: 37
Joined: Tue Feb 21, 2006 3:20 pm

Post 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;
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post 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
mjmacarty
Forum Commoner
Posts: 37
Joined: Tue Feb 21, 2006 3:20 pm

Post 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.
Post Reply