robbyrice Posted September 12, 2008 Share Posted September 12, 2008 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. Link to comment https://forums.phpfreaks.com/topic/123972-search-help/ Share on other sites More sharing options...
fenway Posted September 12, 2008 Share Posted September 12, 2008 Why not just UNION the two search results? Link to comment https://forums.phpfreaks.com/topic/123972-search-help/#findComment-639976 Share on other sites More sharing options...
Mchl Posted September 12, 2008 Share Posted September 12, 2008 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 Link to comment https://forums.phpfreaks.com/topic/123972-search-help/#findComment-639980 Share on other sites More sharing options...
robbyrice Posted September 12, 2008 Author Share Posted September 12, 2008 Not quite sure what you mean. Which two search results would I be UNIONing? Link to comment https://forums.phpfreaks.com/topic/123972-search-help/#findComment-639981 Share on other sites More sharing options...
robbyrice Posted September 12, 2008 Author Share Posted September 12, 2008 @Mchl - I'll sure take a look into that function, thanks. Link to comment https://forums.phpfreaks.com/topic/123972-search-help/#findComment-639984 Share on other sites More sharing options...
fenway Posted September 12, 2008 Share Posted September 12, 2008 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. Link to comment https://forums.phpfreaks.com/topic/123972-search-help/#findComment-640145 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.