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 Link to comment https://forums.phpfreaks.com/topic/213147-dealing-with-multiple-rows-on-a-left-join/ 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. Link to comment https://forums.phpfreaks.com/topic/213147-dealing-with-multiple-rows-on-a-left-join/#findComment-1109932 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.