CODEWITHSUNDEEP
phpmysql
bael
bael

Reputation: 29

How can I insert into 2 table in PHP?

I have 3 tables in MySQL :

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';

}

Upvotes: 1

Views: 151

Answers (2)

RiggsFolly
RiggsFolly

Reputation: 94682

// 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';
}

Upvotes: 1

Jaya Parwani
Jaya Parwani

Reputation: 167

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

Upvotes: 1

Related Questions