Handling multiple rows from an join
Posted: Sun Sep 12, 2010 11:15 am
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:
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.
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
with a second entry in the results set for book 1 and genre 5.
What I need to end up with is a single occurence of book 1 with both genres 1 and 5 set in the form, so I guess I am looking at using an array, but not sure quite where to start....
Thanks
Stuart
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:
Code: Select all
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);
/* Select all genres to create select list */
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);Code: Select all
<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 + 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
Code: Select all
<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 single occurence of book 1 with both genres 1 and 5 set in the form, so I guess I am looking at using an array, but not sure quite where to start....
Thanks
Stuart