Updating multiple tables on one submit passing id from first table to second

Question

I am trying to update 2 tables via one form, the form submits to the parts table and then takes the id from the parts inserted record and creates an entry into job_parts table.

i have tried several option from various forums but not had any luck so far, my code is below along with DB structure.

Update: code edited as suggested but only posting data to 'parts' table and not too 'job_parts table'

tables: !(https://drive.google.com/file/d/11I9HZrjc834_Ft5rqZoa0uFoJyDmMWGL/view?usp=sharing)

if(isset($_POST['submitpart']))
{

    $job_id = $_POST['job_id'];
    $partName = $_POST['partName'];
    $partCost = $_POST['partCost'];
    $partRetail = $_POST['partRetail'];
    $partQuantity = $_POST['partQuantity'];

    $sql1 = "INSERT INTO parts (part_name, part_cost, part_rrp) VALUES ('$partName', '$partCost', '$partRetail');";
    $sql1 .= "SET @last_id_parts = LAST_INSERT_ID();";
    $sql1 .= "INSERT INTO job_parts (job_id, part_id, quantity) VALUES ('$job_id', @last_id_parts, '$partQuantity')";


    $outcome = mysqli_multi_query($conn, $sql1);
    if ($outcome) {
        do {
            // grab the result of the next query
            if (($outcome = mysqli_store_result($mysqli)) === false && 
 mysqli_error($mysqli) != '') {
                echo "Query failed: " . mysqli_error($mysqli);
            }
        } while (mysqli_more_results($mysqli) && 
 mysqli_next_result($mysqli)); // while there are more results
    } else {
        echo "First query failed..." . mysqli_error($mysqli);
    }

}

The simple answer is that `mysqli_query()` does not support executing multiple SQL statements which you are trying to do here. Besides the SQL isn't valid because multiple SQL statements does need to be separated with semicon `;`.. You will have to do every query separately that means a separate`mysqli_query()`.foreach query .. Besides you should also read about preventing SQL injections.. — Raymond Nijland, Feb 16 '19 at 15:20
You might want to try transaction if any of the previous queries fail. — Abu Nooh, Feb 16 '19 at 15:28
Just execute two queries using [mysqli_insert_id()](http://php.net/manual/en/mysqli.insert-id.php). — Paul Spiegel, Feb 16 '19 at 15:43

score 1 · Answer 1 · answered Feb 16 '19 at 16:32

This method should work fine

    $sql1 = "INSERT INTO parts (part_name, part_cost, part_rrp) VALUES ('$partName', '$partCost', '$partRetail')";
    $result1=mysqli_query($con,$sql1);// where $con is connection string
    $last_id = mysqli_insert_id($con);// where $con is connection string
    $sql2 = "INSERT INTO job_parts (job_id, part_id, quantity) VALUES ('$job_id', $last_id , '$partQuantity')";
  $result2=mysqli_query($con,$sql2);// where $con is connection string

vmf91 · Answer 2 · 2019-02-16T15:25:25.723

0

Try to add ; after every statement.

$sql1 = "INSERT INTO parts (part_name, part_cost, part_rrp) VALUES ('$partName', '$partCost', '$partRetail');
    SET @last_id_parts = LAST_INSERT_ID();
    INSERT INTO job_parts (job_id, part_id, quantity) VALUES ('$job_id', @last_id_parts, '$partQuantity');";

And change mysqli_query() to mysqli_multi_query().

edited Feb 16 '19 at 15:25

answered Feb 16 '19 at 15:22

vmf91

1,897
19
27

`mysqli_query()` does not support executing multiple SQL statements **ever**, Besides SQL injections are also a issue. – Raymond Nijland Feb 16 '19 at 15:23
"It works when you run mysqli_multi_query()" Yes but you really want to? Trust me you don't want to protect with `mysqli_real_escape_string()` it can be unsafe for a few reasons 1) using it wrong without using SQL qoutes around the variable and function 2) charset issues which can mess up the anti SQL injection protection and still can be injected. – Raymond Nijland Feb 16 '19 at 15:28
Yeah, you are right. But it is not related to the original question. The question is about running multiple statements with mysqli. – vmf91 Feb 16 '19 at 15:29
"Yeah, you are right. But it is not related to the original question. The question is about running multiple statements with mysqli." Yes but the comments are meant to give extra information about security which is allowed to do on Stackoverflow.. The internet is already litterd with unsafe code so don't continue the trend.. – Raymond Nijland Feb 16 '19 at 15:33
i have tried adding the extra ; to break up the query and have added the mysqli_multi_queryhowever it updates the first table but not the second. – Sean Haymer Feb 16 '19 at 15:53
regarding injection, thanks for the input but this deployment will be on a local intranet without outside connection. – Sean Haymer Feb 16 '19 at 15:54
Check this link [https://stackoverflow.com/questions/10924127/two-mysqli-queries]. It provides some code to display the errors found on any of the queries. – vmf91 Feb 16 '19 at 15:56

Paul Spiegel · Answer 3 · 2019-02-16T16:56:12.937

This is how I'd probably write it. ("Probably" because I rarely use mysqli)

$conn->begin_transaction();

$stmt1 = $conn->prepare("INSERT INTO parts (part_name, part_cost, part_rrp) VALUES (?, ?, ?");
$stmt1->bind_param("sss", $partName, $partCost, $partRetail);
$stmt1->execute();

$stmt2 = $conn->prepare("INSERT INTO job_parts (job_id, part_id, quantity) VALUES (?, ?, ?)");
$stmt2->bind_param("sis", $job_id, $conn->insert_id, $partQuantity);
$stmt2->execute();

$conn->commit();

Note that for numbers you can bind a parameter with i for integer or d for double (floats) instead of s for string. E.g. if $job_id is an integer, you would write:

$stmt2->bind_param("iis", $job_id, $conn->insert_id, $partQuantity);

Regarding error handling I suggest to read this post: how-to-get-mysqli-error-information-in-different-environments

Also note that parameterized prepared statements are not only good for security reasons. If you try to insert a valid name like "O'Conner" your way (... VALUES(.., '$lastName', ..)) your query will fail.

score 0 · Accepted Answer · answered Feb 23 '19 at 01:06

The following worked perfectly

if(isset($_POST['submitpart']))
{

    $job_id = $_GET['id'];
    $partName = $_POST['partName'];
    $partCost = $_POST['partCost'];
    $partRetail = $_POST['partRetail'];
    $partQuantity = $_POST['partQuantity'];

     $sql1 = "INSERT INTO parts (part_name, part_cost, part_rrp) VALUES ('$partName', '$partCost', '$partRetail')";
$result1=mysqli_query($conn,$sql1);// where $con is connection string
$last_id = mysqli_insert_id($conn);// where $con is connection string
$sql2 = "INSERT INTO job_parts (job_id, part_id, quantity) VALUES ('$job_id', $last_id , '$partQuantity')";

$result2=mysqli_query($conn,$sql2);// where $con is connection string

as part of the following

<script>
        function openForm() {
          document.getElementById("myForm").style.display = "block";
        }

        function closeForm() {
          document.getElementById("myForm").style.display = "none";
        }
        </script>


        <button class="open-button" onclick="openForm()">Add Part</button>

        <div class="form-popup" id="myForm">
          <form action="" class="form-container" method="POST">
            <h3>Add part</h3>



            <div class="divTable">
                <div class="divTableBody">
                    <div class="divTableRow">
                        <div class="divTableCell"><label for="name"><strong>Name/Description</strong></label></div>
                        <div class="divTableCell"><input id="partName" name="partName" required="" type="text" placeholder="Enter description" /></div>
                    </div>
                    <div class="divTableRow">
                        <div class="divTableCell"><label for="cost"><strong>Cost Price </strong></label></div>
                        <div class="divTableCell"><input id="partCost" name="partCost" required="" type="text" placeholder="Enter cost price" /></div>
                    </div>
                    <div class="divTableRow">
                        <div class="divTableCell"><label for="retail"><strong>Retail Price </strong></label></div>
                        <div class="divTableCell"><input id="partRetail" name="partRetail" required="" type="text" placeholder="Enter retail price" /></div>
                    </div>
                    <div class="divTableRow">
                        <div class="divTableCell"><label for="quantity"><strong>Quantity </strong></label></div>
                        <div class="divTableCell"><input id="partQuantity" name="partQuantity" required="" type="text" placeholder="Enter quantity" /></div>
                    </div>
                    <div class="divTableRow">
                        <div class="divTableCell"><button type="submitpart" name="submitpart" class="btn" onClick="alert('Added!')">Add</button></div>
                        <div class="divTableCell"> <button type="button" class="btn cancel" onclick="closeForm()">Close</button></div>
                    </div>
                </div>
            </div>


          </form>
        </div>

Updating multiple tables on one submit passing id from first table to second

4 Answers4