Retrieving and using information from MySQL db, using PHP :)

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
hawkontvn
Forum Newbie
Posts: 4
Joined: Tue May 11, 2010 12:46 pm

Retrieving and using information from MySQL db, using PHP :)

Post by hawkontvn »

Hey! I am quite new to this stuff so please don't judge me too harsh =D
Making a website for a book library for this project of mine... but I have some trouble with some basic code.

Trying to output a table with book titles and their respective authors.

I have these 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:

Code: Select all

<?php
$con = mysql_connect ("localhost","root","power4life");
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 bookTitle, bookYear, authorName AS Author
                                    FROM book AS b LEFT JOIN book_has_author AS bha ON b.bookID=bha.book_bookID
                                    LEFT JOIN author AS a ON bha.author_authorID=a.authorID
                                    ORDER BY BookTitle;");

                                    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>";
                                    ?>
The JOIN seems ok, but the table ends up with Book titles ok, but empty author column. Please help :) Thanks in advance!
hawkontvn
Forum Newbie
Posts: 4
Joined: Tue May 11, 2010 12:46 pm

Re: Retrieving and using information from MySQL db, using PH

Post by 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'.. :)
Post Reply