PHP MySQL inserting information from one form into multiple tables

2.9k Views Asked by At

So I have form1 that contains information from multiple tables in a database. I've got listboxes and textboxes within this form that have that information. So all I'm trying to do is insert whatever information the user submits back into the database and have it outputted on form2. I've got my INSERT INTOs on my output page. I know you can't use one INSERT INTO query, so I was wondering how to use multiple INSERTS and submit that information back into the database.

The variables created below come from the previous page and all of the values are there.

if (isset($_POST['n_submit'])){
    $oid = $_POST['oid'];
    $odate = $_POST['odate'];
    $ostatus = $_POST['ostatus'];

    $cfname = $_POST['cfname'];
    $cname = $_POST['clname'];
    $efname = $_POST['efname'];
    $elname = $_POST['elname'];
    echo "New record created successfully";
    $db = mysqli_connect('127.0.0.1:3307', 'mysql_user', 'mysql_password') or die ("I cannot connect to the database because: ".mysqli_connect_error());

    $query = "select status_id from ostatus where status_type = '$ostatus'";
    $result = mysqli_query($db, $query) or die("Error in SQL statement:" .mysqli_error());
    $row = mysqli_fetch_array($result);

    $statusid = $row[0];

    $query1 = "insert into cust ('c_fname', 'c_lname') values ('$cfname',    $clname)";
    $result1 = mysqli_query($db, $query1) or die("Error in SQL statement:" .mysqli_error());


    $query2 = "insert into employed  ('e_fname', e_lname) values ('$efname', '$elname')";
    $result2 = mysqli_query($db, $query1) or die("Error in SQL statement:" .mysqli_error());


    $query3 ="INSERT INTO sorder (o_id, o_date, s_id) VALUES ('{$oid}', '{$odate}', '{$statusid}')";
    $result3 = mysqli_query($db, $query3);
}
1

There are 1 best solutions below

0
On BEST ANSWER

First of all your query is vulnerable to SQL injection. I am not going to fix that.

Second, you should Google how to handle forms properly. And you should consider starting SQL transaction if you really care about the data to go into all the tables for sure.

Third, you should be able to use multiple inserts like you are doing in your code. but you need to correct your syntax errors.

Try this code (I also removed the select code are based on your question it is not needed)

if (isset($_POST['n_submit'])){
    $oid = $_POST['oid'];
    $odate = $_POST['odate'];
    $ostatus = $_POST['ostatus'];
    $cfname = $_POST['cfname'];
    $cname = $_POST['clname'];
    $efname = $_POST['efname'];
    $elname = $_POST['elname'];

    $db = mysqli_connect('127.0.0.1:3307', 'mysql_user', 'mysql_password') or die ("I cannot connect to the database because: ".mysqli_connect_error());


    $query1 = "insert into cust (c_fname, c_lname) values ('".$cfname."',    '".$clname."')";
    $result1 = mysqli_query($db, $query1) or die("Error in SQL statement:" .mysqli_error());


    $query2 = "insert into employed  (e_fname, e_lname) values ('".$efname."', '".$elname."')";
    $result2 = mysqli_query($db, $query2) or die("Error in SQL statement:" .mysqli_error());


    $query3 ="INSERT INTO sorder (o_id, o_date, s_id) VALUES ('".$oid."', '".$odate."', '".$statusid."')";
    $result3 = mysqli_query($db, $query3);

  if($result1 && $result2 && $result3)
     echo 'New record created successfully';
  else
     echo 'something did not work';
}