Nested Queries with Many-to-Many Relationships

MCSPHPFAN

PHPBuilder Query - Request For Help

I started learning PHP and MySQL 6 weeks ago as a hobby that would some day lead to business applications. I have completed my first hobby project in its new born 1.0 version. I invite anyone here to look at it and comment.

It is a PHP/MySQL online database demo that is a list of 200+ computer-related books owned by me for use as a demonstration of the abilities of PHP and MySQL technologies. The current version is available for viewing, searching and sorting only at:

http://www.databasedrivenwebsite.com/booklist
(just click "view" rather than "login")

I am having some difficulties with some PHP code. I am hoping for the insightful and wise assistance of one of the great PHP Masters in this community like I got from "TheDefender" June 29, 2004 on my first post for help. I would greatly appreciate any assistance...from anyone.

The booklist table currently lists the information for each book (title, publisher, copyright, edition, etc.) with the exception of the authors associated with each book. The MySQL database consists of four (4) tables: "books", "publishers", "authors", and "author_lookup". The "publishers"/"books" relationship is a simple many-to-one, but the "author_lookup" table facilitates the "books"/"authors" many-to-many relationship. It is the many-to-many relationship that is giving me some trouble.

I would like my book listing to include not only a list of titles with copyright, publisher, isbn, and edition information from the "books" and "publishers" table as it now does, but I also want to include in the html table returned to the browser (via HTML embedded PHP code) a list of associated author(s) for each book listed (from the "authors" and "author_lookup" tables). It's important that I be able to list multiple authors for each line (title) listing without having to list the titles multiple times. I'm looking into "nested queries" (with which I have little experience) as a possible solution, but every time I try to create multiple select queries referencing different tables to be displayed my line listing, I break the page.

I inserted the select part of the query early in my page as follows:

mysql_select_db($pll_mysql_db, $connect);
$query_nested = "SELECT first_name, last_name FROM authors WHERE authorID=(SELECT l_authorID FROM author_lookup WHERE l_titleID=$pll_field_8)";
$nested = mysql_query($query_nested, $connect) or die(mysql_error());

I inserted the following within the html table code as follows:

while ($row_nested = @mysql_fetch_array($nested))
{
$pll_field_f = row_nested['first_name'];
$pll_field_l = row_nested['last_name'];

echo "<td>$pll_field_f $pll_field_l<br></font></td>\n";}

Any help would be greately appreciated.

Thank you in advance!

MCSPHPFAN

a.k.a. Mark

sidney

your query_nested looks a little strange

try somthing like this guessing as i dont know the lay out of you tables

 "SELECT authors.first_name, authors.last_name  

FROM authors, author_lookup  WHERE author_lookup.l_titleID=$pll_field_8 and authors.authorID= author_lookup.l_authorID"

MCSPHPFAN

Thank you Sidney for pointing out that more information is required for my question to be better answered. Here is additional information regarding my PHP & MySQL project. Please re-read my original request for help as well as the following additional information. Any and all help is very much appreciated. Thanks to all in advance.

$pll_field_8 is a variable used in a loop to create my HTML display table, set to the value returned for each books.bookid of my "books" table select statement. To be perhaps more precise than I was before, my webpage (http://www.databasedrivenwebsite.com/booklist) displays a list of books based so far only on a select statement that returns all fields in the "books" table. I have yet to be able to display ALL "associated" authors (many-to-many relationship) and a single publisher (one-to-many relationship) FOR EACH BOOK in the listing generated from my "books" select statement (obviously requiring a loop in my PHP and in the case of the authors, some sort of nested loop). Any suggestions? Examples?

author_lookup (table with the following columns)
l_titleID (int(11), PRIMARY (dual))
l_authorID (int(11), PRIMARY (dual))

authors (table with the following columns)
authorID (int(11), PRIMARY, auto_increment)
first_name (char(30))
last_name (char(30))

books (table with the following columns)
title (varchar(200))
year (varchar(10))
publisher (int(11))
edition (tinyint(4), default=1)
isbn (varchar(20))
bookid (int(11), PRIMARY, auto_increment)

publishers (table with the following columns)
l_pubID (smallint(6), PRIMARY, auto_increment)
publisher (char(80))
city (char(80))
state (char(80))

sidney

below is an example of how to nest a query within an query
its not a full script but should help you i think

 mysql_select_db($pll_mysql_db, $connect); 
$booksquery="SELECT * FROM books ORDER BY title";
$bookssql=mysql_query($booksquery);
// start table
while($booksrow=mysql_fetch_assoc($bookssql))
	{
	// start table row
	//display books info here
	$pll_field_8=$booksrow['bookid'];
	$query_nested = "SELECT authors.first_name, authors.last_name, author_lookup.l_authorID 
	FROM authors, author_lookup  WHERE author_lookup.l_titleID=$pll_field_8 
	and authors.authorID=author_lookup.l_authorID";
	$nested = mysql_query($query_nested); 
	while ($row_nested = mysql_fetch_array($nested)) 
		{
		//if you want each author on new rows start new table row here 
		$pll_field_f = row_nested['first_name']; 
		$pll_field_l = row_nested['last_name']; 
		//display author info
		//end table row for author  not required if authors not on new row
		}
	//display more book info here if required 
	//end table row
	}
//end table