AngularJS, PHP, and MySQL: How to insert variables correctly using PDO?

Question

I'm trying to add data to my database, but don't know how to do this properly with PDO prepare/execute statements.

In my html file, I have this button call:

<form name="addRecord" method="POST">
    <button data-ng-click="addNewRecord()" name="add">Add Record</button>
</form>

Which goes to my controller:

app.controller('DateHoursController', function ($scope, $http) {
    var date = 456;
    var hours = 5;
    var minutes = 45;
    var cid = 'jk7814982';
    var em = 'email@email.com';
    var versionN = 0;

    $scope.addNewRecord = function () {
        var today = Date.now();

        $http.post("server/insert.php", { 'id': cid, 'createdon': today, 'email': em, 'date': date, 'hour': hours, 'minute': minutes, 'version': versionN })
            .success(function (data, status, headers, config) {
                console.log("inserted Successfully");
            });
    };
});

Which calls my PHP file:

<?php
    if(isset($_POST['add']))
    {
        try {
            $db = new PDO('mysql:host=localhost;dbname=myDBNAME;charset=utf8',
                            'myDBUSER',
                            'myDBPASS');
            $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
            $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        }
        catch(PDOException $ex) {
            echo "did not connect...";
        }

        $sth = $db->prepare("INSERT INTO my_db_table 
                            (tcode, created_on, email, move_in_date, move_in_hour, move_in_minute, version)
                            VALUES (?, ?, ?, ?, ?, ?, ?)");

        $data = json_decode(file_get_contents("php://input"));
        $sth->bindValue(1, $data->id);
        $sth->bindValue(2, $data->createdon);
        $sth->bindValue(3, $data->email);
        $sth->bindValue(4, $data->date);
        $sth->bindValue(5, $data->hour);
        $sth->bindValue(6, $data->minute);
        $sth->bindValue(7, $data->version);

        $success = $sth->execute();
        Print $success;
    }
?>

In the console, I see "inserted Successfully", and checking the browser network, if I click "insert.php" I see status 200, but an empty response.

Am I forgetting something here? I don't understand what's making this fail.

@don'tpanic because of this answer: http://stackoverflow.com/questions/20004325/inserting-data-from-front-end-to-mysql-db-in-angularjs — LatentDenis, Jul 11 '16 at 19:46
@Sirko can you please demonstrate? If it works with my solution I'll accept your answer. — LatentDenis, Jul 11 '16 at 19:46

Don't Panic · Accepted Answer · 2016-07-11T20:22:31.547

2

Do not use mysql_real_escape_string, or any other escaping functions, before binding your values.

Prepare the statement, with placeholders where you are currently inserting your values:

$sth = $db->prepare("INSERT INTO my_db_table 
             (tcode, created_on, email, move_in_date, move_in_hour, move_in_minute, version)
             VALUES (?, ?, ?, ?, ?, ?, ?)");

Bind the values to the prepared statement:

$sth->bindValue(1, $data->id);
$sth->bindValue(2, $data->createdon);
$sth->bindValue(3, $data->email);
$sth->bindValue(4, $data->date);
$sth->bindValue(5, $data->hour);
$sth->bindValue(6, $data->minute);
$sth->bindValue(7, $data->version);

Execute the prepared statement.

$success = $sth->execute();

$success will indicate whether or not the execute was successful.

edited Jul 11 '16 at 20:22

answered Jul 11 '16 at 19:56

Don't Panic

41,125
10
61
80

1

This is a good demonstration, but it's worth mentioning that named placeholders like `:tcode` are *super* useful and make this a lot less messy. – tadman Jul 11 '16 at 19:57
I went ahead and used your solution, but Although I'm getting a success message in console, no data is being added to my database. – LatentDenis Jul 11 '16 at 20:08
Don't forget to [enable PDO exceptions](http://php.net/manual/en/pdo.error-handling.php) to have errors bubble up automatically. – tadman Jul 11 '16 at 20:13
@VolcovMeter Sorry, I just noticed that I had forgotten to add the parameter numbers in the `bindValue` statements. I agree with tadman about named placeholders. If you want to try them instead of `?` placeholders, you can see examples for how to use them in [the pdo manual](http://php.net/manual/en/pdostatement.bindvalue.php#refsect1-pdostatement.bindvalue-examples) – Don't Panic Jul 11 '16 at 20:25
Just as a sidenote: one can pass the parameters also as an array to the `execute()` function. then no call to `bindValue()` is needed. – Sirko Jul 11 '16 at 20:54
I just edited my question with your code. For some reason it won't add data to my DB. – LatentDenis Jul 11 '16 at 20:57
@Sirko any thoughts on why no data is actually being passed into my db? – LatentDenis Jul 11 '16 at 20:58
Oh hey, @Sirko I figured out the problem. I got rid of the if statement in my `insert.php` and everything works great now. But why was that the problem? – LatentDenis Jul 11 '16 at 21:06
If `if(isset($_POST['add']))` returned `false`, then `$_POST['add']` was not set. I think just because your button is named "add" does not mean that it will be part of the post that its attached event performs. – Don't Panic Jul 11 '16 at 21:10
@VolcovMeter You can see some examples of what Sirko was referring to [here](http://php.net/manual/en/pdostatement.execute.php#refsect1-pdostatement.execute-examples). – Don't Panic Jul 11 '16 at 21:19

AngularJS, PHP, and MySQL: How to insert variables correctly using PDO?

1 Answers1