Table problem

[benign]

Hi:I want to make a list of publishers. Each publisher has 5-10 or more books. I have a table for publisher and a table for books.I pasted all the script and the console view down but problem is:I made a form for the publisher registery and I have a filed that I can insert the publisher. But I need to enter many publishers name at the same time. I need a way to have several publisher text fields in my form that I can enter them at the same time for one book. If I can increase the fields (the way you can do it when you want to attach files into yahoo mail and you want to add more than 5 and down there there is a link "Attach More Files") by clicking on a link it will be ideal for me. Please please let me know how I can do such a thing.ThanksBenignMysql DB console view:mysql> use publishers;Database changedmysql> show tables;+----------------------+| Tables_in_publishers |+----------------------+| books || publisher |+----------------------+2 rows in setmysql> show columns from books;+-------------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------------+-------------+------+-----+---------+----------------+| BookID | tinyint(4) | NO | PRI | NULL | auto_increment || BookName | varchar(50) | YES | | NULL | || PublisherID | tinyint(4) | NO | MUL | | |+-------------+-------------+------+-----+---------+----------------+3 rows in setmysql> show columns from books;+-------------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------------+-------------+------+-----+---------+----------------+| BookID | tinyint(4) | NO | PRI | NULL | auto_increment || BookName | varchar(50) | YES | | NULL | || PublisherID | tinyint(4) | NO | MUL | | |+-------------+-------------+------+-----+---------+----------------+3 rows in setMysql DB scrip of my tables:CREATE DATABASE `publishers`CHARACTER SET 'latin1'COLLATE 'latin1_swedish_ci';## Structure for the `books` table : #CREATE TABLE `books` ( `BookID` tinyint(4) NOT NULL auto_increment, `BookName` varchar(50) default NULL, `PublisherID` tinyint(4) NOT NULL, PRIMARY KEY (`BookID`), KEY `PublisherID` (`PublisherID`), CONSTRAINT `PublisherID` FOREIGN KEY (`PublisherID`) REFERENCES `books` (`PublisherID`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=latin1;## Structure for the `publisher` table : #CREATE TABLE `publisher` ( `PublisherID` tinyint(4) NOT NULL, `PublisherName` varchar(50) default NULL, PRIMARY KEY (`PublisherID`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;

[justsomeguy]

If you have a relationship between books and publishers that is a "many-to-many" relationship, where many books can be associated with one publisher, and many publishers can be associated with one book, instead of foreign keys you will want to use a third table to link the other two. So you have your books table, with bookID, and your publishers table, with publisherID, you will want a table called something like book_publishers with only two columns, bookID and publisherID. If you have a row in there where bookID=7 and publisherID=13, that means that publisher 13 published book 7. That way, you can have several rows for book 7 so that several publishers can be assigned to it, or several rows for publisher 13, so that you can look up all the books they have published.As far as the interface goes to add a new item, the slickest way you can do that is with a little javascript. It won't work if javascript isn't on, but I guess that's the price you pay for not using javascript. You will have to customize this code a lot to suit your own needs, but this is what I use for something. I would like to show you an example, but it's in a password-protected system.This is the main javascript function to do the dirty work:

function setOuterHTML(element, str){  if (typeof(element.outerHTML) != 'undefined')	element.outerHTML = str;  else  {	var range = document.createRange();	range.setStartBefore(element);	element.parentNode.replaceChild(range.createContextualFragment(str), element);  }}

This function replaces the HTML surrounding an element, so you can have an element on the page to hold your text boxes, and use this function to add another textbox. This is what the form looks like that I want to duplicate:

	<textarea name="sched_items[]" class="input_text" style="text-align: left;" cols="35" rows="2"></textarea>	   Nightly<select name="nightly[]" class="input_text">	  <option value="false">No</option>	  <option value="true">Yes</option>	</select>	   Weekly<select name="weekly[]" class="input_text">	  <option value="false">No</option>	  <option value="true">Yes</option>	</select>	   Monthly<select name="monthly[]" class="input_text">	  <option value="false">No</option>	  <option value="true">Yes</option>	</select>	   Quarterly<select name="quarterly[]" class="input_text">	  <option value="false">No</option>	  <option value="true">Yes</option>	</select>

Note that each field name has the square brackets after it, meaning that PHP will treat is an array, so that it will hold all of the values that were filled out. After that, I have my empty element to use for the outerHTML function, and a link to execute the function:

<span id="new_itemlist"></span><br><a href="java script:add_item('new_itemlist'); void(0);">[add another task]</a>

(I have no idea why there is a space in "javascript" above, but I can't remove it. Make sure there is no space there)The last piece is the add_item function:

function add_item(elId){  var el = document.getElementById(elId);  var str = "";  str += "<br><textarea name=\"sched_items[]\" class=\"input_text\" style=\"text-align: left;\" cols=\"35\" rows=\"2\"></textarea>";  str += "   Nightly<select name=\"nightly[]\" class=\"input_text\"><option value=\"false\">No</option><option value=\"true\">Yes</option></select> ";  str += "   Weekly<select name=\"weekly[]\" class=\"input_text\"><option value=\"false\">No</option><option value=\"true\">Yes</option></select> ";  str += "   Monthly<select name=\"monthly[]\" class=\"input_text\"><option value=\"false\">No</option><option value=\"true\">Yes</option></select> ";  str += "   Quarterly<select name=\"quarterly[]\" class=\"input_text\"><option value=\"false\">No</option><option value=\"true\">Yes</option></select> ";  str += "<span id=\"" + elId + "\"></span>";  setOuterHTML(el, str);}

You will need to modify that a lot to include your own form information, and make sure to put the <span> back in there at the end like I did, or else you will only be able to add one more item.Even though I showed these in a strange order, in actuality you will need both javascript functions defined before you write the HTML form out, so make sure in your source that both functions are defined before the form.

[benign]

Hi:As a matter of fact I have One-To-Many relationship. A publisher can have several books but a book has one publisher. All I need is a way I can add more than one book to a publisher at the same time. I mean several text fields in my form that all send the Book names into the Book table.Unfortuantly I do not know java script well and that is a bad thing I know... But is there any easier code for what I said.You know for examplePublisher A has published 3 books: Book1,Book2,Book3and I want a form that gives me way that while I add the publisher name at the same time I can add 3 books and they three go to the Book table with the FK of PublisherID for Publisher A.thanksBenign

[justsomeguy]

Well, for a really easy way, you can just have 10 text fields, and then you can enter up to 10 books at a time. You might have a field for a publisher, and then all of your book fields, and you would just have to loop through and add each one to the database separately.

$sql = "";for ($i = 1; $i <= 10; $i++){  $book = $_POST['book' . $i];  if ($sql == "")	$sql = "INSERT INTO books (bookname, publisherid) VALUES ";  if ($book != "")  {	if ($i > 1)	  $sql .= ", ";	$sql .= "('" . mysql_real_escape_string($book) . "', {$pub})";  }}mysql_query($sql);