Dealing with multiple rows on a LEFT JOIN

[stuartriches]

Hi

 

I am new to PHP / mySQL although not to programming and am trying to work out how to deal with the following situation:

 

I have a form for a user to add a book to a database, along with the genres that the book belongs to. Each book can have a number of genres

.

 

There are 3 tables, books, genres and bg_xref  in the following formats

table: books

fields: id, title, author, date published

 

table: genres

fields: id, genre

 

table: bg_xref

fields: id, book_id, genre_id

 

I am now trying to build the form that allows the user to update an existing entry for a book. the genres are selectable from a multi-selection list which I am populating from all the possible values in the genres table.

 

Currently I have the following database selections:

mysql_select_db($database_conn, $conn1);

$query_rs_getBooks = "SELECT books.id, books.title, books.author, bg_xref.genre_id

FROM books LEFT JOIN bg_xref ON books.id = bg_xref.book_id";

$rs_getBooks = mysql_query($query_rs_getBooks, $conn1) or die(mysql_error());

$row_rs_getBooks = mysql_fetch_assoc($rs_getBooks);

 

mysql_select_db($database_conn, $conn1);

$query_rs_getGenre = "SELECT * FROM book_genres ORDER BY genre ASC";

$rs_getGenre = mysql_query($query_rs_getGenre, $conn1) or die(mysql_error());

$row_rs_getGenre = mysql_fetch_assoc($rs_getGenre);

 

I have then created a form that the user uses to update the book, but am trying to work out how to prepopulate the selection list based on teh results of the join e.g.

 

<form method="POST" action="<?php echo $editFormAction; ?>" name="update_book">

<fieldset class="full">

<legend>Enter book details below</legend>

<table>

<tr><td>Title: </td><td><input type="text" size="50" value="<?php echo htmlentities($row_rs_getBooks['title'], ENT_COMPAT, 'iso-8859-1'); ?>" name="title"></td></tr>

<tr><td>Author: </td><td><input type="text" size="30" value="<?php echo htmlentities($row_rs_getBooks['author'], ENT_COMPAT, 'iso-8859-1'); ?>" name="author_surname"></td></tr>

 

<tr><td>Genre(s)</td><td><select name="genre" multiple size="4">

  <?php do {  ?>

    <?php if (($row_rs_getBooks['genre_id']) != $row_rs_getGenre['id']) { ?>

      <option value="<?php echo $row_rs_getGenre['id']?>"><?php echo $row_rs_getGenre['description']?></option>

<?php } else { ?>

    <option value="<?php echo $row_rs_getGenre['id']?>" selected><?php echo $row_rs_getGenre['description']?></option>

    <?php }

    } while ($row_rs_getGenre = mysql_fetch_assoc($rs_getGenre));

$rows = mysql_num_rows($rs_getGenre);

if($rows > 0) {

mysql_data_seek($rs_getGenre, 0);

$row_rs_getGenre = mysql_fetch_assoc($rs_getGenre);

  }

?>

</select></td></tr>

</table>

</fieldset>

<input type="hidden" name="id" value="<?php echo $row_rs_getBooks['id']; ?>">

<input type="submit" value="Update book details">

<input type="hidden" name="MM_update" value="update_book">

</form>

 

Book id = 1 is classified against genres 1 and 5, so my SQL query returns 2 rows:

book.id = 1 + bg_xref.genre_id = 1

book.id = 1 + bg_xref.genre_id = 5

 

At the moment the form is generated for the first row of the results set with

<tr><td>Genre(s)</td><td><select name="genre" multiple size="4">

              <option value="5">Humour</option>

          <option value="4">Non Fiction</option>

          <option value="2">Novel</option>

          <option value="3">Picture Books</option>

          <option value="1" selected>Poetry</option>

         

with a second entry in the results set  for book 1 and genre 5.

 

What I need to end up with is a second occurence of book 1 with both genres 1 and 5 set in the form.

 

Thanks

 

Stuart

 

[stuartriches]

Last line should read:

What I need to end up with is a single occurence of book 1 with both genres 1 and 5 set in the form.