How can I insert into 2 table in PHP?

Question

I have 3 tables in MySQL :

country
language
country_language (it has the other 2 table's ids)

I want to insert the name and description into the country table, the country id and langid to the country_language table.

How can I do this?

It's working in the update section, but when I want to add a new country, it didn't insert the 2 ids into the country_language, just the name and the description.

php

$name = mysqli_real_escape_string($connect, $_POST["name"]);  
$description = mysqli_real_escape_string($connect, $_POST["description"]); 
$id=$_POST["country_id"];

if($id != '')  
{  
    // Update query     
    $message = 'Data Updated';    

}  
else  
{  
    mysqli_query($connect, "START TRANSACTION");
    mysqli_query($connect, "INSERT INTO country(name, description) VALUES('$name', '$description')");
    if(is_array($_POST["language"])) {
        $values = Array();
        foreach($_POST["language"] as $c2_id) $values[] = "($id, $c2_id)";

        mysqli_query($connect, "INSERT INTO country_language(country_id, language_id) VALUES ".implode(",", $values));
    }
    mysqli_query($connect, "COMMIT");        
    $message = 'Data Inserted';

}

you are wide open to SQL injections : you **MUST NOT** use raw user input in your queries. have a look at prepared statements — ᴄʀᴏᴢᴇᴛ, Jul 05 '18 at 12:26
Wht is [the manual](http://php.net/manual/en/mysqli.begin-transaction.php) such a mystery to so many peopple — RiggsFolly, Jul 05 '18 at 12:32
Please have a look at PHP PDO and prepared statements. These kind of coding is too outdated now. — Krishnadas PC, Jul 05 '18 at 12:59
Using multiple comma separated values lists is not standard SQL and does not really have any benefit over doing separate queries. If you do regular inserts then the code will look a lot cleaner and be more portable. Also check your `$_POST` array to make sure that `$_POST["language"]` is indeed an array — apokryfos, Jul 05 '18 at 13:05

score 1 · Accepted Answer · answered Jul 05 '18 at 12:28

Actually during the update you are getting $id as country id & may be in your table country_language ; country_id is NOT NULL, thats why for update its working

Coming on to the insert part or the else part of your code, $id holds nothing & you are also not assigning value of newly inserted country id to $id, hence making the code do nothing

What you can do is just get the last inserted id of the row inserted in country table by using code below before the if condition for checking $POST['language']

$id = mysqli_insert_id($connect);

And then use this $id to get inserted into country_language table, and it will work

score 1 · Answer 2 · answered Jul 05 '18 at 12:56

// Using prepared statements you dont need to do this, and its safer    
// $name = mysqli_real_escape_string($connect, $_POST["name"]);  
// $description = mysqli_real_escape_string($connect, $_POST["description"]); 

$id = isset($_POST["country_id"]) ? $_POST["country_id"] : '';

if($id != '') {  
    // Update query   
    . . .

    $message = 'Data Updated';    
} else {  
    $connect->begin_transaction();
    $sql = "INSERT INTO country (name, description) 
                VALUES(?,?)");
    // prepare the query and bind paramters to the ?
    $ins = $conect->prepare($sql);
    $ins->bind_params('ss', $_POST["name"], $_POST["description"]);
    // execute the query
    $res = $ins->execute();

    // test to see if insert worked
    if ( ! $res ) {
        // insert failed.
        echo $connect->error;
        // no rollback required as nothing has been updated anyway
        exit;
    }


    // capture the new id created by above INSERT
    $new_ctry_id = $connect->insert_id;

    // Using a foreach loop so it will run 
    // for each occurance in $_POST["language"]
    // so only need to check it exists

    if(isset($_POST["language"])) {
        // prepare the statement outside the loop 
        // and execute it with new params as many times as you like
        $sql = "INSERT INTO country_language 
                    (country_id, language_id) VALUES(?,?)";
        $ins2 = $connect->prepare($sql);

        foreach($_POST["language"] as $lang) {
            // bind the new parameters each time round the loop
            $ins2->bind_params('is', $new_ctry_id, $lang);

            $res = $ins2->execute();

            // test to see if insert worked
            if ( ! $res ) {
                echo 'Insert of languages failed with ' . $connect->error;
                // run rollback to remove the insert of the country
                $connect->rollback();
                exit;
            }
        }
    }

    // if we get here, all is well and we must commit the changes we made
    $connect->commit();

    $message = 'Data Inserted';
}

How can I insert into 2 table in PHP?

php

2 Answers2