Retrieving and using information from MySQL database, using PHP - help :)

[hawkontvn]

Hey, im new to coding in php and mysql so need some help here.

Making a book library for this project of mine. And right now im trying to figure out how to output a table with book title and author name. I have three tables:

 

*book - bookID, bookTitle, bookISBN, bookYear etc...

*author - authorID, authorName

*book_has_author - book_bookID, book_bookISBN, author_authorID

 

Book_has_author is the n:m relation table for book --> author.

 

Now, how do I make a table which displays all the books by their title (book.bookTitle) together with the author.authorName?

 

Like:

 

Title | Author |

XXX |    YYY    |

ZZZ |  WWW |

etc...

 

I tried doing some code myself, but as you'll probably figure out im not very good - at all.

 

Code:

 

<?php
$con = mysql_connect ("localhost","user","password");
if (!$con)
    {
    die('Could not connect to the database: ' . mysql_error());
    }

$select = mysql_select_db("biblio", $con);
if (!$select)
    {
    die('Could not select the database: ' . mysql_error());
    }

$list_books = mysql_query("SELECT * FROM book UNION SELECT * FROM author");

                                    echo "<table border='1'>
                                        <tr>
                                        <th>Title</th>
                                        <th>Author</th>
                                        </tr>";

                                    while($row = mysql_fetch_array($list_books))
                                        {
                                        echo "<tr>";
                                        echo "<td>" . $row['bookTitle'] . "</td>";
                                        echo "<td>" . $row['authorName'] . "</td>";
                                        echo "</tr>";
                                        }
                                    echo "</table>";

?>

[Mchl]

You need JOINs not UNION

 

SELECT 
  b.bookTitile, a.authorName 
FROM 
  books AS b 
INNER JOIN 
  book_has_author AS ba ON b.bookID = ba.book_bookID
INNER JOIN 
  author ON ba.author_authorID = a.authorID

[hawkontvn]

Ok, thanks. Fixed it. But how do I display it through PHP after I fixed the query to include JOIN instead of UNION? Thanks.

[Mchl]

What you have now should work fine.

[hawkontvn]

Well, with my current PHP code the Author column ends up empty.

The title column is ok though.

 

Something wrong with the following? Thought 'authorName' should do it.

 

while($row = mysql_fetch_array($list_books))
                                        {
                                        echo "<tr>";
                                        echo "<td>" . $row['bookTitle'] . "</td>";
                                        echo "<td>" . $row['authorName'] . "</td>";
                                        echo "</tr>";
                                        }
                                    echo "</table>";

[hawkontvn]

oh,figured it out... lol.

 

"SELECT bookTitle, bookYear, authorName AS author..."

and in the php code further down I used 'authorName', instead of 'author'..