CODEWITHSUNDEEP
phpmysql
Memar
Memar

Reputation: 1

I want to insert a data from a form into a table, and select another data from another table and insert into third table using PHP

I have three related tables “users”, “category” and “interest_area”; and I want to insert a data from a form into “users” table, and select another data from “category” table and insert into “interest_area” table using PHP.

The error it shows is:

Error: INSERT INTO users(user_id, first_name, last_name, higher_education, user_name, pass_word) VALUES('' , '87878787' , 'iuiu' , 'iuiu' , 'root' , '');INSERT INTO interest_area (category_id) SELECT category_id FROM category WHERE category_name = 'ASP'; Erreur de syntaxe pr�s de 'INSERT INTO interest_area (category_id) SELECT category_id FROM category ' � la ligne 2

My PHP code is:

<?php
   if (isset($_POST["interest_area"])){             
   $f_name = $_POST["firstname"];
   $l_name = $_POST["last_name"];
   $h_education = $_POST["higher_education"];
   $i_area = $_POST["interest_area"];
   $email = $_POST["email"];
   $u_name = $_POST["user_name"];
   $p_word = $_POST["pass_word"];

  $sql = "INSERT INTO users(user_id, first_name, last_name,   higher_education, user_name, pass_word)
  VALUES('' , '$f_name' , '$l_name' , '$h_education' , '$username' ,  '$password');";

  $sql .= "INSERT INTO interest_area (category_id)
    SELECT category_id FROM category
    WHERE category_name = '$i_area';";

if ($conn->query($sql) === TRUE) {
echo "New record created successfully";} 
else { echo "Error: " . $sql . "<br>" . $conn->error;} 
}
?>

Upvotes: 0

Views: 325

Answers (4)

Pradeep
Pradeep

Reputation: 9717

You have to run two mysqli_query for insertion

mysqli_query

Better use prepare statement while insertion of data

prepare statement

$f_name = $_POST["firstname"];
$l_name = $_POST["last_name"];
$h_education = $_POST["higher_education"];
$i_area = $_POST["interest_area"];
$email = $_POST["email"];
$u_name = $_POST["user_name"];
$p_word = $_POST["pass_word"];
$user_id = $_POST["user_id"];

$user_id should not be blank if it ur primary key then data can't be inserted;

 $sql1 = "INSERT INTO users(user_id, first_name, last_name,   higher_education, user_name, pass_word)
      VALUES('$user_id' , '$f_name' , '$l_name' , '$h_education' , '$u_name' ,  '$p_word')";

 $sql2 = "INSERT INTO interest_area (category_id)
     SELECT category_id FROM category WHERE category_name = '$i_area'";

 mysqli_query($con,$sql1);
 mysqli_query($con,$sql2) 
 mysqli_close($con);

Upvotes: 1

Rylab
Rylab

Reputation: 1295

You simply need to run your two INSERT statements as separate $conn->query calls, rather than concatenating them into a single call.

Upvotes: 0

S. Chowdhury
S. Chowdhury

Reputation: 11

You need to use multi_query for multiple queries. 

$sql = "INSERT INTO users(user_id, first_name, last_name, higher_education, user_name, pass_word)VALUES('' , '$f_name' , '$l_name' , '$h_education' , '$username' , '$password');";

$sql .= "INSERT INTO interest_area (category_id)
     SELECT category_id FROM category WHERE category_name = '$i_area'";

 mysqli_multi_query($con,$sql);
 mysqli_close($con);

Upvotes: 0

Tim
Tim

Reputation: 119

The Syntax error is here:

$sql .= "INSERT INTO interest_area (category_id)
SELECT category_id FROM category
WHERE category_name = '$i_area';";

should be in curly...

$sql .= "INSERT INTO interest_area (category_id)
SELECT category_id FROM category
WHERE category_name = {$i_area};";

And two separate queries as stated...

Upvotes: 0

Related Questions