Jump to content

Dealing with multiple rows on a LEFT JOIN


stuartriches

Recommended Posts

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
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.