PHP insert multiple tables with more records in one table MySQL

I have two tables, one is users, and second is family_list,

Tables:

users > id, name,surname,dateofbirth,address,contact  (id is autoincrement and primary key)
family_list > id, name, dateofbirth, user_id

I made relation, id primary key with user_id foreign key.

Now, I need php form to store data to multiple tables, where I can add user to table users and for family_list table I need to can add more than one record for one user. So, one user can have more childern in family _list table. I am using bootstrap. How to do that, PHP and Jquery?

If there is some tutorial for beginner.

Thank you
Žan AnđićAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
You have multiple parts to your question and it will be best to start with just one.

First, bootstrap and jquery are for the front end view and do not pertain to inserting data on the back end.  Do you need help with the front end as far as how to lay out a form and make it look good?

Part of your puzzle is coming up with the right database structure.  Are you 100% comfortable with what you have or do you need assistance with designing database tables?  

The back end functions can be broken down to how to accept data from a form and how to insert data to your database.

Which of these options do you need help with? or multiple/all?
0
mohan singhWeb developerCommented:
Hi @ Žan Anđić
 
You can insert Many data in multiple table

If you want to insert some data in two table you can do it like this

  You have to fire 2 query and check at your end which should be insert in which table

if you have dynamic field than inset like this
//suppose this is your table and you have more input field
<input type='name[]' name='name'>


<?php
if(isset($_POST['submit'])){

            $name = $_POST['name'];
            $mob = $_POST['mobile'];            
            $count = count($name);
            for($i=0; $i<$count; $i++) {
            if(trim($_POST['name'][$i] && $_POST['mobile'][$i] !='')) {
            $Query = "INSERT INTO Taable (name,mobile) VALUES(".$_POST['name'][$i]."','".$_POST['mobile'][$i]."')";
            $Result6 = mysql_query($conn, $Query);    
                   }    
            }   
}
?>

Open in new window

0
Julian HansenCommented:
The previous post uses MySQL - this library has been deprecated and removed in later versions of PHP - rather use MySQLi or PDO.

As Scot said - Bootstrap is not a factor here.

The procedure for inserting multiple child records is fairly straight forward
1. Insert a record in the parent
2. If the insert is successful obtain the insert_id and save it
3. Loop through each child record and insert the child data using the parent_id in each insert.

This can be something like this
<?php

// An object to return the status of the script
$result = new stdClass;

// Assume the worse
$result->status = false;

// CHANGE THIS TO FIT YOUR ENVIRONMENT
$db = new mysqli('server','user','password','database');

// Check we connected else bug out
if (!$db) {
  $result->message = 'Failed to connect to Database. Error:' . $db->error;
  die(json_encode($result));
}

// Get our data - this is where you would 
// put your code to get your insert data

// NB THIS IS TEST DATA - REPLACE WITH YOUR OWN
$name     = 'John';
$surname  = 'Smith';
$dob      = '1987-11-09';
$address  = '1 Elm Street, Testville';
$contact  = '0019987765';
$children = array(
  array('name' => 'Bob',  'dob' => '2006-10-29'),
  array('name' => 'Mary', 'dob' => '2008-03-18')
);

try {
  // Start a transaction
  $db->query("START TRANSACTION");

  $query = <<< QUERY
INSERT INTO users (name,surname,dateofbirth,address,contact) 
  VALUES (?,?,?,?,?)
QUERY;

  // Prepare the statement to insert the user
  $stmt = $db->prepare($query);

  // If this fails bug out
  if (!$stmt) throw new Exception("User prepare failed");
  
  // Bind the parameters for the insert
  $stmt->bind_param("sssss", $name, $surname, $dob,  $address, $contact);
  
  // If execute faisl bug out
  if (!$stmt->execute()) throw new Exception('User insert failed');
  
  // Save the insert_id for child inserts
  $user_id = $db->insert_id;
  
  // Done with this process so close the statement
  $stmt->close();
  
  // Create the query for the child inserts
  $query = <<< QUERY
INSERT INTO family_list(name, dateofbirth, user_id) VALUES (?,?,?)
QUERY;
  
  // Prepare it
  $stmt = $db->prepare($query);
  if (!$stmt) throw new Exception('Prepare failed on family_list. Error:' . $db->error);

  // Initialise the insert variables
  $name = $dob = '';
  
  // Bind the insert variables to the statement
  $stmt->bind_param("ssd", $name, $dob, $user_id);
  
  // iterate over the children
  foreach($children as $child) {
    // bind the child record values to the insert variables
    $name=$child['name'];
    $dob = $child['dob'];
    if (!$stmt->execute()) throw new Exception('Insert of child record failed. Error: ' . $db->error);
   }
   $result->status = true;
}
catch(Exception $e) {
  $result->message = $e->getMessage();
  $db->query('ROLLBACK');
}

// All good so commit the data
$db->query('COMMIT');

// Report what happend
die(json_encode($result));

Open in new window


Note: this solution uses TRANSACTIONS. This is important in this type of solution to ensure that you don't end up with orphan records. If your script crashes before completing you could end up with a parent and only a partial insert of the children. By using TRANSACTIONS the entire process is treated as an atomic operation.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Žan AnđićAuthor Commented:
Thank you for answers.

Do you need help with the front end as far as how to lay out a form and make it look good?

I want to make it look good, I want in frontend to be available to add more fields for childrens with + button.

Part of your puzzle is coming up with the right database structure.  Are you 100% comfortable with what you have or do you need assistance with designing database tables?  
And I will have two more table similar family_list, so there will be 4 tables final.

USERS |FAMILY_LIST | ASSETS | UNIT

ASSETS > id, type, value, users_id
UNIT > id, unit_name,period, users_id

relations.jpg
So, I need to make one form, maybe is good to make step1 step2 step3 step4... where I will be able to add new user, and add records to parent tables of user. When I adding records to parent tables (family_list, assets, unit) I need to be able to click on button + (add row), if I need to add more than one record.
0
Julian HansenCommented:
If you have no development experience then this is not really the forum to be asking this question. Either you need to open a Gig and pay someone to do it for you or you need to enroll in some development courses to get you up to speed with the technologies that you need to use.

I would build your front end as an SPA (single page application) that captures all the data to common model - when you are done you submit the model to a Service via AJAX - the service validates the data and adds it to the database (using a process similar to the one shown above) and then returns a status.

For this you would need the following skills

JavaScript
jQuery / Angular / React
HTML
CSS
PHP
MySQL

Other options are to use a PHP Framework like Laravel to build the application and have the screens run from the server - this makes (in my view) the model a bit more difficult as you have to store the progressive parts in a session.
0
Žan AnđićAuthor Commented:
Thank you Julian,

I want to learn...and this is exercise for me, before I was made with PHP, HTML and CSS no relational database.

I was made form in HTML, CSS and PHP, one file named adduser.php and after submit is done redirect to step2.php where I can add record in second table, using mysqli_insert_id... is there some tutorial for beginners for inserting data into relational tables?

Thank you again
0
Julian HansenCommented:
The step approach has problems especially in situations where a parent / child exists. For instance, if user completes step 1 and User record is created - but then abandon's the process - do you want to keep the half completed record or discard it - how do you know when you should do that.
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Žan,

My suggestion to help you is to pick one thing to work on for this thread and once answered, move on to the next.

Get the code to work that Julian provided here to run in your own environment. You may run into errors just doing this and need help to make it work.  Stick with that for now and he made it easy as he provided test data that is hard coded. Make sure you understand it and then close out this thread.

The next thing you want to work on is creating a basic form without any fancy formatting that can post to the code provided and add data that way.  You will want to make a separate question just for that.

Lastly, create a question on making the form/page work using bootstrap front end css.  Before attempting that, I suggest spending a day going through an easy course over at https://www.codecademy.com/catalog/language/html-css.   Start with the basic html, then css then responsive.   You can wrap up by viewing the docs at https://getbootstrap.com/docs/4.0/getting-started/introduction/ and looking at examples such as https://getbootstrap.com/docs/4.0/examples/checkout/ and try on your own to make it what you need. Then come back here to get help.
0
Julian HansenCommented:
I would add to Scott's post - try to get your thinking in terms of client and service. Your client (the browser) collects information and sends it to a service which processes it. By keeping a distinct separation between the code that does the view and the code that interacts with the database it is much easier to test and complete the various steps.

For instance - if you are going to go the AJAX route you would create a script that accepts the data from the forms and then runs in a similar fashion to the script I posted earlier. The script takes data in and returns a response indicating the result of the operation - that is all - no HTML or other logic is mixed up in that process.

This allows you to test that service on its own without your interface - you can setup a simple form with the relevant fields on that posts to the service. Once the service is working you can tick that box and move to the next phase knowing that as long as you send the right data to the service it will do what it is supposed to do.

You will see many PHP coding examples that are a mashup of PHP code and HTML - this is very difficult to
a) Debug
b) Extend
c) Read

Where possible compartmentalise your functionality into services and / or classes that perform specific tasks - these can be independently tested and then used with confidence when they are working.
0
Žan AnđićAuthor Commented:
Thank you for your answers. I start with learning, I created basic form which post data into tables,
0
Julian HansenCommented:
Oh dear, I object to this proposal very strongly. The code given is problematic on many levels

1. It uses unsanitized data straight from $_POST
2. It suggests the mysql deprecated library

And lastly it does not answer the question.

The question was how to do an insert into a parent / child relationship - this requires getting the insert_id from the parent insert and using that in the child - not even touched on in the recommended answer.

If this question has to be closed I would leave the recommended answer out of the points as it simply does not address the question asked.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.