Link to home
Start Free TrialLog in
Avatar of BrianCW

asked on

Linking HTML Form (MySQL & PHP/PDO) to a dependent table with multiple entries

I am working on a webpage that interfaces with a mySQL database that has 4 tables:
*tbMain (Fields: ID, Title, Content, etc.)
*tbTags (Fields: ID, Tag)
*tbBooks (Fields: ID, Publisher, Title)
     Fields:  +MainID (the ID of the record in tbMain)
                  +LinkID (the ID of the Tag or Publisher/Book in either taboos or tbTags)
                  +LinkTableName (either tbTags or tbBooks depending on which table LinkID corresponds to)

The webpage displays a form with all the fields from tbMain, and each record in tbMain may have multiple Tags and/or Books associated with it.
Where I'm hitting a wall is trying to display (and allow updating of) all the Publishers/Books and/or Tags corresponding to a particular entry in the Main table (essentially what would be a Book SubForm and a Tag SubForm in MS Access if that helps clarify).

The page is written in HTML using PDO to interface with the mySQL database.  
Currently I have a <form> with each element from tbMain set up similar to this one:
Title:<input name="Title" type="text" value="<?php echo $result['Title']?>"></input>

Open in new window

Then below those elements, still within the <form> tag, I have this code trying to create a table for Book Entries. But it does not populate with anything.
Book Reference(s):
	$subBook = mysql_query("SELECT b.ID, b.Publisher, b.Title FROM tbBooks b JOIN tbLinkMain L ON b.ID=L.LinkID WHERE L.MainID = $ID AND LinkTableName=\"tbBooks\"", $db);
	while ($rBook = mysql_fetch_object($subBook)) {
		echo "<tr><td>".rBook.Publisher."</td>";
		echo "<td>".rBook.Title."</td></tr>";
	echo "</table>";	

Open in new window

*I should note that the $ID variable is set to tbMain.ID for the corresponding 'main' record.

Ultimately, I would like for the 'Submit' button on the Form to insert/update any fields in tbMain (which it's already doing), as well as any changes or additions in the tbTags or tbBooks tables. I was able to set a single input text field on the form for a Tag and have a second UPDATE/INSERT query run that did write the MainID, LinkID and LinkTableName to tbLinkMain, but that was limited to one single tag per main record.

I hope I am including enough information to make sense. This is my first posting on EE, and my prior professional experience was in MS-Access development, so I apologize if my terminology is incorrect for HTML or PHP. I am taking web development classes right now, but this is still pretty new to me. Thank you to anyone who can help or point me in the right direction.
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of BrianCW


Thank you, Ray. I did go back and rewrite that code section with PDO syntax rather than mysqli per your suggestion. And thank you for the links. The first one has been extremely helpful. Haven't checked out the second one yet, but looking forward to doing so. Appreciate the help!
Glad it's headed in th eright direction!  As a general rule, you can make multiple connections to the database server (in different PHP variables) and you can convert the database part of things one query at a time.  I've found this to be helpful when refactoring older code sets.