1

So me and my colleague have been assigned for making a library database as the lesson's assignement and then make an html database interface where someone would be able to perform certain tasks within the database such as inserting rows, deleting rows, finding rows etc.

We have created the database, imported it and all that but the problem is in inserting elements into it.

Well for example the books table has a many-to-many relationship with the authors table (where primary keys of each table are the ISBN and the authors ID respectively) so when we want to insert a book with it's authors in our DB we don't know exactly how to do it because they are in different tables. We are confused as to how we would put data from a single form in multiple tables with different primary keys.

We have been trying to find examples like that but to no avail.

Even links to tutorials and whatnot are mostly welcome.

Server side code is PHP.

Thank you very much.

Peter Brown
  • 50,956
  • 18
  • 113
  • 146
Aki K
  • 1,222
  • 1
  • 27
  • 49

2 Answers2

1

You can simply collect your form variables and build the desired SQL statements. I'm no PHP expert, so I'm only familiar with using the mySQL plugin for PHP.

Consider a person form with an address portion that I want to add to a separate table:

 <form action="makePerson.php" method="POST">
    First Name: <input type="text" name="firstName"/>
    First Name: <input type="text" name="lastName"/>
    Address: <input type="text" name="address"/>
    <input type="submit"/>
 </form>

Now my script (this is untested and my PHP is rusty)

 $firstName = $_POST['firstName'];
 $lastName = $_POST['lastName'];
 $address = $_POST['address'];

 $id = generateId();

 $person_insert = "insert into person values (" . $id . ", " . $firstName . ", " . $lastName . ")";
 $address_insert = "insert into address values (" . $address . ", " . $id . ")";

 mysql_execute($person_insert);
 mysql_execute($address_insert);

That's the general idea. Note, this is not production quality code, just an example.

jeff
  • 4,325
  • 16
  • 27
  • So when I add the book_isbn to the book table, I guess I will have to manually add it in the book_author table as well?If so my problem is, in that same table how to add the authors as well and at the same time adding them to the author table. My problem is if I will end up with blank fields in lines. That's what's confusing me a lot with many to many relationships – Aki K Jun 04 '12 at 17:03
  • your mapping depends on the book and the author, so you must add them first. So 1) insert book 2) insert author 3) insert book/author record – jeff Jun 04 '12 at 17:07
  • Thank you very much that makes it really clear. But the problem is that I have many authors for each book. How would I go in adding multiple authors. This is mainly an html with php issue. Do i have the user select how many authors there are and spawn the according tables to fill for him? – Aki K Jun 04 '12 at 17:14
  • oh gotcha - just make multiple input's with the same name (e.g. author) - most server side technology will accept them as an array. Haven't done it in PHP, but I have done it other places. See (http://stackoverflow.com/questions/3314567/how-to-get-form-input-array-into-php-array) – jeff Jun 04 '12 at 17:23
1

I have some suggestions for you,

  1. use drop down box for subject names and populate it thru Subject table ,so you could only select subject name which are in the DB.

  2. Upon entering the book details (all most all the values in the table and plus subject name),manually insert ISBN into Authors table.

  3. On the book user interface also use dropdownbox for publisher name (as many people don't remember publisher code) ,then on postback you might pivk value publisher code from value ="??".
    <select> <option value="Code1234">Sams Publisher</option> </select>

  4. If you add the Editor form of Author ,Publisher , Book and subject in the same page it would be too much fields for the user to enter and he might get confused ,but as it's just a homework you can ignore this point and try your best to manage them on one page.

Sample Library DB: enter image description here
Source:

DayTimeCoder
  • 4,294
  • 5
  • 38
  • 61
  • The subject name will be written by the user. But that's not the issue. The issue is with the many authors, how do I add them all at once for a given book – Aki K Jun 04 '12 at 17:17
  • You might also use ` – DayTimeCoder Jun 04 '12 at 17:20
  • the database will be empty and the interface will be there for someone to fill the database that's why I have an issue with adding many authors for a single book – Aki K Jun 04 '12 at 17:22
  • Well you might provide an Hyperlink "Add new Author" which may open another page for Author creation, every DB starts from empty then it grows to fillfull needs. – DayTimeCoder Jun 04 '12 at 17:26
  • @SilliconTouch You can stuff all the Table Editor UI on one page ,you should make different pages or you would have to use JQuery UI and AJAX stuff. – DayTimeCoder Jun 04 '12 at 17:33
  • So I guess I can't add the authors in the same form as the book form. I'll have to take the user to a different page where everytime he submits an author, that author will be assigned with the book being inserted before the hyperlink being hit. That's just a bit confusing to implement... – Aki K Jun 04 '12 at 17:48
  • How can one enter author who does not exist in DB ,user must also enter the publisher details the first time, that's how these things work ,you gotta keep the data integrity, Think of it do the Amazon.com have to enter these kinda details every time ? No b/c there DB is very much mature ! – DayTimeCoder Jun 04 '12 at 17:54
  • This is not like a regular site where a simple user uses it. This is a site so the database manager is able to manage the library database that's the idea behind this. The idea is that in a single form someone will be able to insert every single field that exists in the diagram, that's why I'm confused about the authors – Aki K Jun 04 '12 at 18:21
  • I actually figured it out now, I haven't seen what the select multiple actually does. Thank you – Aki K Jun 04 '12 at 19:41