Ajax / PHP / MySQL: Inserting data into db not working with Ajax and PHP

Question

I am new to PHP and MySQL and am looking for help with the following.

I would like to store a users email and selected language in a db on button click.
My thought was I could use Ajax for this so I put the below Ajax call in my JS functions file to pass the data to a separate Ajax file (ajax.php) which then inserts it into the db ("Users").

When I alert the values in JS they show correct so my input variables are ok and also the Ajax call returns "success" after passing the data but I can't get this to insert anything in my db.
When I run the same INSERT manually in the db it works fine.

Can someone tell me what I am doing wrong here ?

My Ajax call (in my functions file):

$('#btnID').on('click', function(){
    var email = $.trim( $('#email').val() ).toLowerCase();
    var lang = $.trim( $('#lang').val() );

    $.ajax({
        url: "ajax.php",
        type: "post",
        cache: "false",
        data: {
            email: email,
            lang: lang
        },
        success: function(data) {
            console.log(data);
            alert("success");
        },
        error: function(){
            alert("failure");
        }
    });
});

My PHP (in my ajax.php file):

$conn = new mysqli($servername, $username, $password, $dbname);
$conn->set_charset("utf8");
if($conn->connect_error){
    die("Connection failed: " . $conn->connect_error);
}
$email = $_POST["email"];
$lang = $_POST["lang"]; 
$sql = "INSERT INTO Users (email, lang) VALUES ('" . $email . "', '" . $lang . "')";
$conn->close();

Many thanks in advance, Mike

Answer 1

You're just preparing the query and not triggering it

$conn->query($sql)

So, You should have something like

$conn = new mysqli($servername, $username, $password, $dbname);
$conn->set_charset("utf8");
if($conn->connect_error){
    die("Connection failed: " . $conn->connect_error);
}
$email = $_POST["email"];
$lang = $_POST["lang"]; 
$sql = "INSERT INTO Users (email, lang) VALUES ('" . $email . "', '" . $lang . "')";
if ($conn->query($sql)) 
{ 
echo 'success'; 
} 
else 
{ 
echo 'failure'; 
}
$conn->close();

Warning :

1. You're not sanitize your input (Never trust your input)

2. You shall prefer Prepared statements PDO to make it better

Update :

As the OP needs to know more about cleaning the input

There are severals steps or approaches available

1. If you are getting the integer as input then make it as integer.

$id = $_GET['id'];
settype($id, 'integer');

2. Use mysql_real_escape_string() for escaping function for query variables

3. Use strip_tags() to remove the unwanted unwanted html

4. If you have embeddedstring then do htmlspecialchars

5. Input might be shellcommand to burst everything escapeshellcmd

Answer 2

You are not actually inserting anything. For that, you need to call $conn->query($sql) after building your SQL.

$conn = new mysqli($servername, $username, $password, $dbname);
$conn->set_charset("utf8");
if($conn->connect_error){
    die("Connection failed: " . $conn->connect_error);
}
$email = $_POST["email"];
$lang = $_POST["lang"]; 
$sql = "INSERT INTO Users (email, lang) VALUES ('" . $email . "', '" .      $lang . "')";
$conn->query($sql); //check the return value here and do what you want
$conn->close();