SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Jun 2013
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Inserting values in multiple tables by single HTML form submit

    Hi folks,

    I am making a mailing list with PHP and MySQL. I have two questions. Here's the table I have normalized:



    Question 1 - How can I enter values in 4 different tables (Client, Product, Department and Area) using a single HTML input form? For example, when I enter data for my client "Andrew" into the client table, related information of Product, Department and Area table should be populated as well through a single SQL INSERT query ? How to achieve that?

    Question 2 - Is my normalization ok? I am asking because I have made composite primary key in Product, Department and Area tables, using same foreign key (email). Is this correct?

    Thanks again.

  2. #2
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,260
    Mentioned
    196 Post(s)
    Tagged
    2 Thread(s)
    To do INSERT in more than one table in one query MySQL lets you create a TRIGGER
    http://www.sitepoint.com/how-to-create-mysql-triggers/
    http://dev.mysql.com/doc/refman/5.0/...er-syntax.html

    Sorry I can't help with normalization, I'm still fairly new to it myself, though if you search for posts made by @r937 ; you should find something helpful.

  3. #3
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,869
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    You could just run the inserts one after another after the form is submitted.

    If you want to ensure that database integrity is maintained then set up the multiple inserts as a transaction so that you can COMMIT if they all run successfully and ROLLBACK if any of them fail for any reason.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by media9 View Post
    Question 1 - How can I enter values in 4 different tables
    with 4 INSERTs... use START TRANSACTION and COMMIT as stephen suggested

    Quote Originally Posted by media9 View Post
    Question 2 - Is my normalization ok?
    yup, composite PKs are fine
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Nov 2013
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Instead of using the email as your foreign key why dont you generate a sequence number in client table and use that column in the child tables. This way you can save the storage.

  6. #6
    SitePoint Member
    Join Date
    Jun 2013
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you so much everyone for your insights. I appreciate this forum. Got a lot of information from you guys.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •