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

score 1 · Answer 1 · answered Jun 21 '15 at 12:06

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();

score 1 · Accepted Answer · edited May 23 '17 at 12:14

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 :

You're not sanitize your input (Never trust your input)
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

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

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

Use mysql_real_escape_string() for escaping function for query variables
Use strip_tags() to remove the unwanted unwanted html
If you have embeddedstring then do htmlspecialchars
Input might be shellcommand to burst everything escapeshellcmd

edited May 23 '17 at 12:14

Community

1
1

answered Jun 21 '15 at 12:06

Sulthan Allaudeen

11,330
12
48
63

Thanks a lot. This works great ! Can you explain what you mean by "cleaning" ? – keewee279 Jun 21 '15 at 12:08
1

You're welcome .. It means sanitizing. You can do it by [escaping](http://de1.php.net/manual/en/mysqli.real-escape-string.php) , [filtering](http://php.net/manual/en/function.filter-var.php) etc., – Sulthan Allaudeen Jun 21 '15 at 12:10
Thanks - I thought the $.trim would be enough here ? I just started programming, could you maybe give me some examples or typical approach for this ? I do validate the email input separately when submitting this (just didn't add this here). – keewee279 Jun 21 '15 at 12:11
1

Sure ... Updating my answer :) – Sulthan Allaudeen Jun 21 '15 at 12:12
Got it - thanks a lot, this is great and really helpful ! Will definitely apply and will accept the answer as soon as I can. :) – keewee279 Jun 21 '15 at 12:15
Welcome. +1 for your eagerness about cleaning input :) – Sulthan Allaudeen Jun 21 '15 at 12:19
Thanks. I thought if I learn it I better start right from the beginning. :) – keewee279 Jun 21 '15 at 12:20
Thanks. I am sorry, I thought this was working but was looking at the wrong things. It seems it still doesn't do the Insert. Is there anything else that could be missing here, maybe on the SQL side ? – keewee279 Jun 21 '15 at 12:25
1

Its okay, Can you do `echo $sql` and put it inside phpmyadmin and see what happens ? – Sulthan Allaudeen Jun 21 '15 at 12:27
Thanks a lot, I am really sorry. I have the PHP saved in a separate file called ajax.php. Currently there is nothing else in there as this is for testing. Where would I echo the $sql then ? – keewee279 Jun 21 '15 at 12:31
1

Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/81114/discussion-between-sulthan-allaudeen-and-keewee279). – Sulthan Allaudeen Jun 21 '15 at 12:32
1

As discussed through the chat his is now working with your updated solutions - thanks so much for that ! – keewee279 Jun 21 '15 at 13:00
You're welcome, Also don't forget to remove the alert in the ajax function:) Because it will print or alert the query or success everytime – Sulthan Allaudeen Jun 21 '15 at 13:01
Sure - will do that. :) – keewee279 Jun 21 '15 at 13:01
Sure do, If you got it trouble you shall ping me any time ;) – Sulthan Allaudeen Jun 21 '15 at 13:03
I will definitely do that - thanks again for all your help ! I am sure I will stumble across the next question some time soon. ;-) – keewee279 Jun 21 '15 at 13:04

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

2 Answers2