php mysqli insert data into two tables [duplicate]

Question

I'm trying to insert data into 2 tables in php mysqli but it inserts data in the first table fine but not inserting anything in the second where there are other columns as well in the second table.

Here is my code:

$sql = "INSERT INTO socio (name, age, dob, gender, year, stgroup, stadd) VALUES ('$stnam', '$stage', '$stdob', '$stgen', '$styer', '$stGr', '$stadd')";
$sql1 = "INSERT INTO parta (name, stgroup, year) VALUES ('$stnam', '$stGr', '$styer')";

$result = mysqli_query($con, $sql);
$result = mysqli_query($con, $sql1);

Is anything wrong in the above code? Any suggestions?

No all data is inserted in first table only, not in second table — Kanna
– Kanna, Commented May 30, 2017 at 8:10
the syntax is good, have you checked the keys spelling is ok for the second request? — Kaddath
– Kaddath, Commented May 30, 2017 at 8:11
use prepared statements. Also post your database schema. do a $mysqli->error on the second query. You'll find out the reason — Rotimi
– Rotimi, Commented May 30, 2017 at 8:14

Funk Forty Niner · Accepted Answer · 2017-05-30 11:28:46Z

1

I can't really see what's exactly wrong with your code, it might happen that some of the columns on the parta table are not supposed to be null. With the code you provided, it's hard to tell, as there's no error handling at all. You might want to use transactions, then use proper error handling and also use prepared statements.

Try with this code that I have prepared for you.

<?php
$con = new mysqli("..."); // you should know this part already

$success = false;
try {

    $con->autocommit(FALSE);

    $con->begin_transaction();

    if ($sql = $con->prepare("INSERT INTO socio (name, age, dob, gender, year, stgroup, stadd) VALUES (?,?,?,?,?,?,?)")) {
        $sql->bind_param('sissss', $stnam, $stage, $stdob, $stgen, $styer, $stGr, $stadd);
        if (!$sql->execute()) {
            throw new Exception($sql->error);
        }

        if ($sql_two = $con->prepare("INSERT INTO parta (name, stgroup, year) VALUES (?,?,?)")) {
            $sql_two->bind_param('sss', $stnam, $stGr, $styer);
            if (!$sql_two->execute()) {
                throw new Exception($sql_two->error);
            }
        }
    }

    if ($con->commit()) {
        $success = true;
    } else {
        throw new Exception('Transaction commit failed...');
    }
}catch (Exception $ex) {
    try {
        // something went wrong,rollback and display message
        $con->rollback();
        echo $ex->getMessage();
    }
    catch (Exception $e) {
        echo $e->getMessage();
    }
}
$con->autocommit(TRUE);

if ($success) {
    echo "data successfully inserted";
}
?>

edited May 30, 2017 at 11:28

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

answered May 30, 2017 at 9:03

Masivuye Cokile

4,7733 gold badges22 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kanna Over a year ago

Hey you are right its because I didn't mention other fields value as null that's why data is not inserted. I changed it to null and it works for now.

Collectives™ on Stack Overflow

php mysqli insert data into two tables [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related