Just need an extra few sets of eyes...

manders76

Somewhere in here is a syntax error, but I can't see it...

<?
$title = "Personal Library";
include('header.php');
$host = "localhost";
$user = "librarian";
$password = "cardcatalog";
mysql_connect($host, $user, $password);
mysql_select_db('library');

$book_to_add = $_POST['book_to_add'];
$author = $_POST['author'];

if ($book_to_add) {
   $AddQuery = "INSERT INTO books (title) VALUES ('$book_to_add')";
   if (mysql_query($AddQuery)) {
      $book_id = mysql_insert_id();
      $LinkQuery = "INSERT INTO book_author (book_id, author_id)
                    VALUES ('$book_id', '$author')";
      mysql_query($LinkQuery);
      print "The book $book_to_add was added. Thanks! <br />";
   }
      $ListQuery= "SELECT first_name, middle_name, last_name, title
                    FROM authors, books
                    WHERE book_author.author_id = authors.author_id
                    GROUP BY title";
      $ListResult = mysql_query($ListQuery);
      while ($Row = mysql_fetch_array($ListResult)) {
      print "$Row[title] by $Row[first_name] $Row[middle_name] $Row[last_name]<br/>\n";
} else {
   print "Add a book to the collection: <br />";
   print "<form method=POST>";
   print "<input type ='text' size ='50' name='book_to_add'>";
   print "<select name='author'>";
   $ListResult = mysql_query('SELECT * FROM authors');
   while ($Row = mysql_fetch_array($ListResult)) {
      print "<option value = \"$Row[author_id]\">";
      print "$Row[first_name] $Row[middle_name] $Row[last_name] </option>\n";
   }
   print "</select>";  

   print "<input type='submit' name='submit' value='submit'>";
   print "</form>";
}
mysql_close();
include('footer.php');
?>

halojoy

You should always give us a hint
on what Line xx the error is.

You have a missing: }
after this line

print "$Row[title] by $Row[first_name] $Row[middle_name] $Row[last_name]<br/>\n";

manders76

Thanks (for both the code correction and telling me to tell you about the line #).

manders76

Okay. I feel dumb...what am I screwing up here?

Code is this:

<?
$title = "Personal Library";
include('header.php');
$host = "localhost";
$user = "********";
$password = "**********";
mysql_connect($host, $user, $password);
mysql_select_db('library');

$book_to_add = $_POST['book_to_add'];
$author = $_POST['author'];

if ($book_to_add) {
   $AddQuery = "INSERT INTO books (title) VALUES ('$book_to_add')";
   if (mysql_query($AddQuery)) {
      $book_id = mysql_insert_id();
      $LinkQuery = "INSERT INTO book_author (book_id, author_id)
                    VALUES ('$book_id', '$author')";
      mysql_query($LinkQuery);
      print "The book $book_to_add was added. Thanks! <br />";
   }
      $PostQuery= "SELECT first_name, middle_name, last_name, title
                    FROM authors, books
                    WHERE book_author.author_id = authors.author_id
                    GROUP BY last_name";
      $PostResult = mysql_query($PostQuery);
      while ($Row = mysql_fetch_array($PostResult)) {
      print "$Row[title] by $Row[first_name] $Row[middle_name] $Row[last_name]<br/>\n";
   }
} else {
   print "Add a book to the collection: <br />";
   print "<form method=POST>";
   print "<input type ='text' size ='50' name='book_to_add'>";
   print "<select name='author'>";
   $ListResult = mysql_query('SELECT * FROM authors');
   while ($Row = mysql_fetch_array($ListResult)) {
      print "<option value = \"$Row[author_id]\">";
      print "$Row[first_name] $Row[middle_name] $Row[last_name] </option>\n";
   }
   print "</select>";
   print "<input type='submit' name='submit' value='submit'>";
   print "</form>";
}
}
mysql_close();
include('footer.php');
?>

Gives me this error:

"Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/spring2010/akmcclen/public_html/books.php on line 27"

What am I doing wrong?

laserlight

Get more information, e.g.,

$PostResult = mysql_query($PostQuery) or die(mysql_error());

Note that you should be using [man]mysql_real_escape_string/man on string data that is used as a value in an SQL statement.

halojoy

"SELECT first_name, middle_name, last_name, title
                    FROM authors, books
                    WHERE book_author.author_id = authors.author_id
                    GROUP BY last_name"

Something is not right with this query.
As $PostResult renders an error when you try mysql_fetch_array.

We can put in [man]mysql_error[/man] and let MySQL give more info about it.

      $PostQuery= "SELECT first_name, middle_name, last_name, title
                    FROM authors, books
                    WHERE book_author.author_id = authors.author_id
                    GROUP BY last_name";
      $PostResult = mysql_query($PostQuery);

// if $PostResult returns FALSE, display Errror information
if(!$PostResult)
    echo '<br />'.$PostQuery.'<br />'.mysql_error().'<br />';

  while ($Row = mysql_fetch_array($PostResult)) {
  print "$Row[title] by $Row[first_name] $Row[middle_name] $Row[last_name]<br/>\n";
   }

bradgrafelman

One problem I see is in the WHERE clause - you reference the table 'book_author' but never included it in the FROM clause. Perhaps you meant 'books' instead?

manders76

Okay, what I'm trying to do is find a WHERE clause that'll link the table 'authors' and the table 'books' together. Their primary keys appear in the table 'book_author', which is the only place they appear together. What I'm getting right now is a list of the stuff in 'authors' accompanied by the first entry (and the first entry only) in 'books'. I've experimented with a number of syntaxes and it gives me either that, or the error I posted earlier.

laserlight

That implies that you cannot just join books and author. You need to join books, book_author, and author, on books.id=book_author.book_id and author.id=book_author.author_id.

manders76

Okay, tried this:

      $PostQuery= "SELECT first_name, middle_name, last_name, title, books.book_id, book_author.book_id, 
authors.author_id, book_author.author_id
                    FROM authors, books, book_author
                    WHERE book_author.book_id = books.book_id AND book_author.author_id = authors.author_id
                    GROUP BY last_name";
      $PostResult = mysql_query($PostQuery);
      while ($Row = mysql_fetch_array($PostResult)) {
         print "$Row[title] by $Row[first_name] $Row[middle_name] $Row[last_name]<br/>\n";
   }

It spat out one entry from 'books' for every author. However, there are multiple entries for each author, and I'd like to get all of those.

laserlight

So, you want to find for each author all the books he/she has authored/co-authored?

One way is to use that most recent attempt, but order by the author. This will means that all the books for the same author will be clumped together in the result set, so you just need to check if the current author has changed when you iterate over the result set.

manders76

Do you mean to order by authors.author_id?

laserlight

That sound work, yes.