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