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 — ᴄʀᴏᴢᴇᴛ
– ᴄʀᴏᴢᴇᴛ, Commented Jul 5, 2018 at 12:26
Please have a look at PHP PDO and prepared statements. These kind of coding is too outdated now. — Krishnadas PC
– Krishnadas PC, Commented Jul 5, 2018 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
– apokryfos, Commented Jul 5, 2018 at 13:05

Jaya Parwani · Accepted Answer · 2018-07-05 12:28:33Z

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

RiggsFolly · Accepted Answer · 2018-07-05 12:56:36Z

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

Collectives™ on Stack Overflow

How can I insert into 2 table in PHP?

php

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

php

2 Answers 2

Comments

Comments

Linked

Related