Page 1 of 1

Handling multiple rows from an join

Posted: Sun Sep 12, 2010 11:15 am
by stuartr
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:

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);
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.

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 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

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

Re: Handling multiple rows from an join

Posted: Mon Sep 13, 2010 6:52 pm
by mecha_godzilla
I don't quite understand what you're trying to achieve - when the user edits the book do you just want the categories it's been assigned to displayed in the form, or do you need all the categories to be returned and some way to indicate which ones have already been set?

A quick way to prepopulate your selection would be to get all the information in one query by using another JOIN statement. I'm not sure whether this code will work but it could look something like this:

Code: Select all

mysql_select_db($database_conn, $conn1);
$query_rs_getBooks = "
    SELECT books.id, books.title, books.author, bg_xref.genre_id, genres.genre
    FROM books
    JOIN bg_xref ON books.id = bg_xref.book_id
    JOIN genres ON bg_xref.genre_id = genres.id";
$rs_getBooks = mysql_query($query_rs_getBooks, $conn1) or die(mysql_error());
$row_rs_getBooks = mysql_fetch_assoc($rs_getBooks);
You'd need to add a WHERE statement in there to select the book the user is editing, then you can just loop through this list (you might want to ORDER BY the results as well of course).

You could (as you say) also do this with an array but this approach might be simpler.

HTH,

Mecha Godzilla

Re: Handling multiple rows from an join

Posted: Tue Sep 14, 2010 4:01 am
by stuartr
Thanks for the response, you're right in what I am trying to achieve - the update form for a book will have a selected list of the possible genres (populated by a query on the genre table) with the genres currently assigned to the book pre-selected. This would allow the user to simply make any amendments to the genres assigned to the book along with other changes they may wish to make.

I eventually worked a way of doing this with arrays. Essentially I have removed the table that hold the relationship between books and genres from the query on the books, and replaced this with 2 queries, one on the books table for the book ID passed through to the update page, and one on the book/genre xref table using the same ID. This returns two datasets - one with the details of the book, and one with 0 or more rows that details of all the genres assigned to that book.

I am then using the array_push feature to bring these together in a single array, that I can query to determine whether each option in the select list should be preselected.

e.g. Book Table returns a single row for book_id = 1
e.g. Book/Genre Xref retruns 2 rows for book_id = 1 (Genres 2 & 3)

These are merged into an array $book_details : array 0 (Title => Title1, Author => author1, array1(2, 3))

Having done that I now have a question of database design vs efficient code, having realised that within the update form the user could effectively perform an add or delete function against the book/genre xref table e.g. they deselect genre 2 and select genre 1. This is in effect a delete operation on the xref table for bookID = 1, genreID =2, and a second operation, an insert on bookID = 1, genreID = 1. Unless there anyone knows of a decent function to assist in this, the coding is going to become more complex as I will probably need to compare the before and after selection of genres, and determine whether I need to perform any inserts and/or deletes. The only other alternative from a coding perspective seems to be to delete all existing book/genre xrefs for the book in question and recreate them for the new returned dataset, which I could finesse slightly by only doing it if there was a change in any of the selected genres, but it still seems wrong to delete records that are not changing and then to recreate them as well as possible creating unecessary wasted space in the db.

Another way of achieveing this that I can think of is to change the db structure to hold the selected genres in the books tables as a SET datatype with the values being defined as the genre IDs. This would then make the php code much simpler as I would simply treat the changes in selected genres as an update to the SET datatype field (subject to a bit of formatting). However this goes against good db normalisation practise and could cause potential problems in the future (e.g. making a query against all books in a specific genre more difficult), as well as other limits with SET that shouldn't give me an issue (e.g. being limited to 64 elements)

So before I head uneasily down either the set route or the complete deletion/recreation of xrefs for the book in question, if anyone has knowledge of a function to ease the updating of the xref table, that would be great :D

In summary I will have 3 linked tables
1) Books: fields = BookID, title, author, publisher etc.
2) Genres: fields = GenreID, description (e.g. fantasy. horror, novel, poetry, non-fiction)
3) Book/Genre Xref: fields = XrefID, BookID, GenreID

e.g. Book 1 belongs to genres 2 and 3 and Book/Genre Xref therefore has 2 rows
XrefID=1, BookID = 1, GenreID =2
XrefID=2, BookID = 1, GenreID =3

The user may then change the selection of genres via the book update form to 1 and 3. How do I manage this update as effectively 3 things have happened within the update form:
BookID1/GenreID2 is no longer relevant and needs to be deleted (XrefID 1)
BookID1/GenreID1 is a new cross reference and needs to be created (new XrefID)
BookID1/GenreID3 is left unchanged (no change required to XrefID2).

Any advice on pros/cons of the possible methods, or other ideas/comments gratefully received.

Re: Handling multiple rows from an join

Posted: Tue Sep 14, 2010 3:58 pm
by mecha_godzilla
One thing that I've done before (not sure whether this is appropriate for what you want) is to use a comma-separated list of values that can be stored in a single field - this means you only have to have one cross-referenced record for your books.

The kind of query you could use for this might look like

Code: Select all

$sql = "SELECT * FROM products WHERE category_id IN ($category_list)";
and $category_list looks like

16,17,18

I'd stay away from trying to delete the cross-referenced records as that definitely would make things more complicated. The original approach that you went for (with the three tables) makes sense if you're offering a product that has lots of variations (IE like a business that sells pizzas, because they not only sell different types of pizzas but within the same type there are differences in price relating to the size of the pizza, etc.) but here you only need one record per book and within this record you can save the categories. If you don't like the idea of saving the values in CSV format, how about something like this:

00001 - This book is in category 5
10001 - This book is in categories 1 and 5

This would be very easy to parse (IE look at the position of the string to see whether the category has been selected or not).

HTH,

Mecha Godzilla