stuartriches Posted September 11, 2010 Share Posted September 11, 2010 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 Quote Link to comment Share on other sites More sharing options...
stuartriches Posted September 11, 2010 Author Share Posted September 11, 2010 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.