0

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;} } ?>
Improve this question
2
  • you can't have multiple statements in a single query call. It's a basic defense mechanism against one form of sql injection attack. You have to have TWO $sql variables, and call query() separately for each. Commented Jul 19, 2016 at 19:00
  • What about mysqli_multi_query() @MarcB? Commented Jul 20, 2016 at 12:57

4 Answers 4

Reset to default
1

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);
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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...

Improve this answer

3 Comments

Outright FALSE. There's nothing wrong with OP's string syntax. That's perfectly valid PHP
Sorry... our shop uses curly for more clarity... single quote is valid...
removing the quotes will just make it an bare string, and probably a syntax error as well.
0

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);
Improve this answer

1 Comment

Make sure you have the syntax right - each query needs its own semi-colon on the SQL statement.
0

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

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.