mySQL data insertion - getting an error when everything looks fine

Question

This is my code, we have database called "our_new_database".

The connection is fine, as well as the HTML Form and credentials and I still cannot insert information into the database.

Table is created, I can see the columns and lines in XAMPP / phpMyAdmin.

The only error I'm getting is the last echo of the If/Else Statement - "Could not register".

Tried everything I can and still cannot make this insertion to work normally.

Can someone advise me something?

<?php

include "app".DIRECTORY_SEPARATOR."config.php";
include "app".DIRECTORY_SEPARATOR."db-connection.php";
include "app".DIRECTORY_SEPARATOR."form.php";

$foo_connection = db_connect($host, $user_name, $user_password, $dbname);

$sql = "CREATE TABLE user_info(
    user_name_one VARCHAR(30) NOT NULL,
    user_name_two VARCHAR(30) NOT NULL,
    user_email VARCHAR(70) NOT NULL UNIQUE
)";

if(mysqli_query($foo_connection, $sql)){
    echo "Table created successfully";
}
else {
    echo "Error creating table - table already exist.".mysqli_connect_error($foo_connection);
}

if($_SERVER['REQUEST_METHOD'] == 'POST'){

    $user_name_one = $_POST["userOne"];
    $user_name_two = $_POST["userTwo"];
    $user_email = $_POST["userEmail"];

    $sql = "INSERT INTO user_info (userOne,userTwo,userEmail) VALUES('".$_POST['userOne']."',('".$_POST['userTwo']."',('".$_POST['userEmail']."')";
    if(mysqli_query($foo_connection,$sql))
    {
        echo "Successfully Registered";
    }
    else
    {
        echo "Could not register";
    }
}

$foo_connection->close();

thats why you always turn on mysqli errors, `echo $foo_connection->error` to find out why — Kevin, Mar 20 '18 at 08:52
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1.. What does it mean? — inSee, Mar 20 '18 at 08:53
You are trying to insert values for the columns `userOne,userTwo,userEmail`, but they don't exist. they are called `user_name_one, user_name_two, user_email ` — kscherrer, Mar 20 '18 at 08:57
[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Mar 20 '18 at 12:05

ScaisEdge · Answer 1 · 2018-03-20T09:07:54.283

5

You should avoid the direct use of variables in SQL statements, instead, you should use parameterized queries.

This also should avoid the need to string concatenation and manipulation problems.

$stmt = $foo_connection->prepare("INSERT INTO user_info 
      (user_name_one,user_name_two,user_email)) 
    VALUES(?,?,?)");
$stmt->bind_param('sss', $user_name_one, $user_name_two, $user_email );
$stmt->execute();

edited Mar 20 '18 at 09:07

answered Mar 20 '18 at 08:58

ScaisEdge

131,976
10
91
107

thank you for your answer, I will keep in mind that. Thanks ones again. – inSee Mar 20 '18 at 09:00
Now after the column-names edit this answer is definitely the best, using a prepared query and all.. +1 – kscherrer Mar 20 '18 at 09:11

score 0 · Answer 2 · answered Mar 20 '18 at 08:53

0

$sql = "INSERT INTO user_info (userOne,userTwo,userEmail) VALUES('".$_POST['userOne']."','".$_POST['userTwo']."','".$_POST['userEmail']."')";

answered Mar 20 '18 at 08:53

Sagar Jajoriya

2,377
1
9
17

Could not registerUnknown column 'userOne' in 'field list'..This is what I'm getting when I paste your answer my friend. Any suggestions? – inSee Mar 20 '18 at 08:55

score 0 · Answer 3 · answered Mar 20 '18 at 08:57

I reckon your parentheses on this line:

$sql = "INSERT INTO user_info (userOne,userTwo,userEmail) VALUES('".$_POST['userOne']."',('".$_POST['userTwo']."',('".$_POST['userEmail']."')";

Do not match, it should look like something like this:

$sql = "INSERT INTO user_info (userOne,userTwo,userEmail) VALUES('".$_POST['userOne']."','".$_POST['userTwo']."','".$_POST['userEmail']."')";

Cause for know your query is:

"INSERT INTO user_info (userOne,userTwo,userEmail) VALUES('value',('value1',('value2')"

As said above you might use: echo $foo_connection->error

To see some errors displayed

$sql = "INSERT INTO user_info (user_name_one,user_name_two,user_email) VALUES ('".$_POST['userOne']."', '".$_POST['userTwo']."', '".$_POST['userEmail']."')"; This is the correct code, the problem was in the syntax and the wrong column names.. thank you as well. — inSee, Mar 20 '18 at 09:01

Rahul · Accepted Answer · 2018-03-21T04:46:26.260

0

You need to change

 $sql = "INSERT INTO user_info (userOne,userTwo,userEmail) VALUES('".$_POST['userOne']."',('".$_POST['userTwo']."',('".$_POST['userEmail']."')";

To

$sql = "INSERT INTO `user_info`(`user_name_one`,`user_name_two`,`user_emai`l) VALUES ('$user_name_one','$user_name_two','$user_email')";

remember you should use prepared query

$sql= $foo_connection->prepare("INSERT INTO user_info 
      (user_name_one,user_name_two,user_email)) 
    VALUES(?,?,?)");
$sql->bind_param('sss', $user_name_one, $user_name_two, $user_email );
$sql->execute();

edited Mar 21 '18 at 04:46

answered Mar 20 '18 at 08:57

Rahul

1,617
1
9
18

$sql = "INSERT INTO user_info (user_name_one,user_name_two,user_email) VALUES ('".$_POST['userOne']."', '".$_POST['userTwo']."', '".$_POST['userEmail']."')"; This is the correct code, the problem was in the syntax and the wrong column names.. thank you as well. – inSee Mar 20 '18 at 09:01
@inSee why are you using '".$_POST['userOne']."' like that – Rahul Mar 20 '18 at 09:05
you have already declared variable use it if you dont want to use it why did you declared here $user_name_one = $_POST["userOne"]; $user_name_two = $_POST["userTwo"]; $user_email = $_POST["userEmail"]; – Rahul Mar 20 '18 at 09:07
@inSee always remember one thing some one spends time on your question.if code works for you then need to do accept or upvoted it.i am not saying for me.i am saying for all – Rahul Mar 20 '18 at 09:12
I clicked UPVote on every question, am I missing something. I'm kinda new in the website? – inSee Mar 20 '18 at 09:21
Now I see I did not answer here, excuse me about this. It's a missclick really, excuse me once again. – inSee Mar 20 '18 at 09:27
Gotta be ok now my friend? I click accept right? :) – inSee Mar 20 '18 at 09:29
Turn the tide against teaching/propagating sloppy and dangerous coding practices. If you post an answer without prepared statements [you may want to consider this before posting](http://meta.stackoverflow.com/q/344703/). Additionally [a more valuable answer comes from showing the OP the right method](https://meta.stackoverflow.com/a/290789/1011527). – Jay Blanchard Mar 20 '18 at 12:06

mySQL data insertion - getting an error when everything looks fine

4 Answers4