php mysqli insert data into two tables

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, May 30 '17 at 08:10
the syntax is good, have you checked the keys spelling is ok for the second request? — Kaddath, May 30 '17 at 08:11
use prepared statements. Also post your database schema. do a `$mysqli->error` on the second query. You'll find out the reason — Rotimi, May 30 '17 at 08:14
There could be multiple reasons for that - and with the information provided it is hard to guess. Try something like `if (!$mysqli_query($con, $sql1)) { printf("Errormessage: %s\n", $mysqli_error); }` — gus27, May 30 '17 at 08:14
yes spelling is ok but 2nd table is associated with other columns is it a problem? — Kanna, May 30 '17 at 08:15
@Kanna There could be other problems like that some columns are defined `NOT NULL` and so have to filled... Please post the table structure! — gus27, May 30 '17 at 08:16

score 1 · Accepted Answer · edited May 30 '17 at 11:28

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";
}
?>

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. — Kanna, May 30 '17 at 09:38

score -3 · Answer 2 · answered May 30 '17 at 08:18

-3

Try this. It could be because you have the same variable name for two different actions. I've just rename the second result as $result1

$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);
$result1 = mysqli_query($con, $sql1);

answered May 30 '17 at 08:18

hans-könig

553
8
10

1

The variable name has no effect here. OP does not check the variables or use them. You could have atleast improved OP's code to prepared statements. – Rotimi May 30 '17 at 08:20
I have already tried like that, but it won't work that way either. – Kanna May 30 '17 at 08:23

php mysqli insert data into two tables

2 Answers2