search help

[robbyrice]

MySQL version 5.0.67

 

 

Let me start off by saying that I am fairly new to databases and data-driven programs/websites.  With that said, I have taken on the project of creating a simple LAN-based search program for my school library.  For the moment I am working essentially with two tables "books" and "authors" (which should seem pretty logical ).  The structure of the tables is as follows (although I don't include all of the columns from each table).

 

DROP TABLE IF EXISTS `test`.`authors`;
CREATE TABLE  `test`.`authors` (
  `author_ID` int(10) unsigned NOT NULL auto_increment,
  `last_name` varchar(50) NOT NULL,
  `first_name` varchar(50) default NULL,
  `middle_name` varchar(50) default NULL,
  PRIMARY KEY  (`auteur_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `test`.`books`;
CREATE TABLE  `test`.`books` (
  `book_ID` int(10) unsigned NOT NULL auto_increment,
  `title` varchar(150) NOT NULL,
  `lang` varchar(1) NOT NULL,
  `copy` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY  (`livre_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

 

Having done some research already I know that this is a many to many relationship because a book can have 1 or more authors, and author could be associated with one or more books.  Because of this, I also have a link table.

 

DROP TABLE IF EXISTS `test`.`link_table`;
CREATE TABLE  `test`.`link_table` (
  `author_ID` int(10) unsigned NOT NULL,
  `book_ID` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`auteur_ID`,`livre_ID`),
  UNIQUE KEY `Index_2` (`livre_ID`,`auteur_ID`),
  CONSTRAINT `FK_link_table1` FOREIGN KEY (`author_ID`) REFERENCES `authors` (`author_ID`),
  CONSTRAINT `FK_link_table_2` FOREIGN KEY (`book_ID`) REFERENCES `books` (`book_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

In this table the primary key is a composite key made up of the author_ID and book_ID.

 

Now, here comes the part I'm not getting.  What I want is for somebody to be able to do a search on the books in the library on either the author or book title.  I want to display this on a web page in such a way that if they searched for a book title, then it would show the title with a list of all the authors of that book (provided there was more than one), and if they searched by author, then it would show the author with a list of all the books associated with that author.  For the moment I've got this as a query,

 

SELECT b.title, a.last_name
FROM (authors AS a LEFT JOIN link_table AS t USING (author_ID)
LEFT JOIN books AS b USING (book_ID))

 

but it is not giving me what I want.  What I am looking for is a result where each unique book (identified by book_ID) has its own row and the rest of the columns of that row are the authors for that book (1 or more).  I know that the query just above is incapable of doing that, but I don't know how to make it do what I want.  I hope this makes sense.

[fenway]

Why not just UNION the two search results?

[Mchl]

You might want to look at GROUP_CONCAT() mysql function

 

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

[robbyrice]

Not quite sure what you mean.  Which two search results would I be UNIONing?

[robbyrice]

@Mchl - I'll sure take a look into that function, thanks.

[fenway]

Not quite sure what you mean.  Which two search results would I be UNIONing?

The search on books by title + by name -- then join this back to whatever other tables yo uwant.