PDO with INSERT INTO through prepared statements

Question

On my adventure through the jungles of PHP: Data Objects I've encountered a problem with executing MySQL queries through prepared statements.

Observe the following code:

$dbhost = "localhost";
$dbname = "pdo";
$dbusername = "root";
$dbpassword = "845625";

$link = new PDO("mysql:host=$dbhost;dbname=$dbname","$dbusername","$dbpassword");

$statement = $link->prepare("INSERT INTO testtable(name, lastname, age)
        VALUES('Bob','Desaunois','18')");

    $statement->execute();

This is me, and I want to be in my database. However I keep getting lost in.. well.. I don't know! According to google this is the way to do it, though my database stays empty.

Am I missing something here? Because I've been stuck for a good hour now and would like to continue studying PDO!

@ÁlvaroG.Vicario this is likely an error reporting matter. Bob, are you familiar with error reporting in PHP? Are you able to see an error if occurred? — Your Common Sense, Sep 06 '13 at 11:00

score 112 · Accepted Answer · edited Aug 09 '23 at 08:34

112

You should be using it like so

<?php
$dbhost = 'localhost';
$dbname = 'pdo';
$dbusername = 'root';
$dbpassword = '845625';

$link = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbusername, $dbpassword);
$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$statement = $link->prepare('INSERT INTO testtable (name, lastname, age)
    VALUES (:fname, :sname, :age)');

$statement->execute([
    'fname' => 'Bob',
    'sname' => 'Desaunois',
    'age' => '18',
]);

Prepared statements are used to sanitize your input, and to do that you can use :foo without any single quotes within the SQL to bind variables, and then in the execute() function you pass in an associative array of the variables you defined in the SQL statement.

You may also use ? instead of :foo and then pass in an array of just the values to input like so;

$statement = $link->prepare('INSERT INTO testtable (name, lastname, age)
    VALUES (?, ?, ?)');

$statement->execute(['Bob', 'Desaunois', '18']);

Both ways have their advantages and disadvantages. I personally prefer to bind the parameter names as it's easier for me to read.

edited Aug 09 '23 at 08:34

Your Common Sense

156,878
40
214
345

answered Sep 06 '13 at 10:37

Novocaine

4,692
4
44
66

Is this also a "good" way to do this? $statement = $link->prepare("INSERT INTO testtable(name, lastname, age) VALUES(?, ?, ?)"); $statement->execute(array($this->name, $this->lastname, $this->age)); – Bob Desaunois Sep 06 '13 at 10:44
Yep, that's a good way to do it. – Novocaine Sep 06 '13 at 10:46
I just tried BOTH methods, the one with the question marks earlier because I stumbled upon it, both were to no avail. I'm starting to worry the error is somewhere else, I will try to rewrite what I have now. – Bob Desaunois Sep 06 '13 at 10:47
3

try adding `$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);` directly after the `$link = new PDO...` line – Novocaine Sep 06 '13 at 10:49
Would you please remove quotes from around variables in connection code? – Your Common Sense Sep 06 '13 at 10:51
Removing quotes did not work, and the errormode code line didn't do anything, I'm now rewriting to make -SURE- there is nothing wrong. – Bob Desaunois Sep 06 '13 at 10:53
Edited: added try/catch code and removed quotes in connection code as per @YourCommonSense's correction – Novocaine Sep 06 '13 at 10:54
-1 for adding try catch. – Your Common Sense Sep 06 '13 at 10:57
1

How else are errors in the query caught if you don't? – Novocaine Sep 06 '13 at 11:09
you don't need to catch this one. Most of time errors have to be reported, not caught. And PHP already excellent with it, no your help required. Catching has **absolutely different** purpose. – Your Common Sense Sep 06 '13 at 11:11
@YourCommonSense PHP documentation explicitly instructs developers to catch database connection errors in production code because the default error action is to display a backtrace that reveals database connection details. http://php.net/manual/en/pdo.connections.php. -1 – vlasits Apr 06 '17 at 14:15
3

@vlasits it's a pity but that page is a complete bullshit. [There is no such "default action" in PHP](https://phpdelusions.net/pdo#reporting_errors). – Your Common Sense Apr 06 '17 at 14:27
1

Fun trick: if you don't want to hard code the column names, you can `$parsed = json_decode($myJson, true); $fields=array_keys($order); $fieldlist=implode(',',$fields); $valuelist=':'.implode(', :',$fields); $sql="INSERT INTO `orders`($fieldlist) VALUES ($valuelist)";` – Ray Foss Feb 03 '18 at 20:27

score 5 · Answer 2 · answered Sep 06 '13 at 10:58

5

I have just rewritten the code to the following:

    $dbhost = "localhost";
    $dbname = "pdo";
    $dbusername = "root";
    $dbpassword = "845625";

    $link = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbusername, $dbpassword);
    $link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $statement = $link->prepare("INSERT INTO testtable(name, lastname, age)
        VALUES(?,?,?)");

    $statement->execute(array("Bob","Desaunois",18));

And it seems to work now. BUT. if I on purpose cause an error to occur, it does not say there is any. The code works, but still; should I encounter more errors, I will not know why.

answered Sep 06 '13 at 10:58

Bob Desaunois

746
2
6
11

`error_reporting(E_ALL); ini_set('display_errors',1);` – Your Common Sense Sep 06 '13 at 11:02
Hm. It looks like [you knew it already](http://stackoverflow.com/questions/17560144/mysqli-error-does-not-work#comment25546310_17560277) but for some reason still don't use. – Your Common Sense Sep 06 '13 at 11:08
1

I honestly completely forgot about init_set(); My sincere apologies. – Bob Desaunois Sep 06 '13 at 12:16
Hard-Coding the inserted Values in there and also not explaining why you are using PDO and what you did is really not going to help the thread creator. – Altay Akkus Jul 24 '18 at 20:08

score -1 · Answer 3 · answered Sep 06 '13 at 10:45

-1

Please add try catch also in your code so that you can be sure that there in no exception.

try {
    $hostname = "servername";
    $dbname = "dbname";
    $username = "username";
    $pw = "password";
    $pdo = new PDO ("mssql:host=$hostname;dbname=$dbname","$username","$pw");
  } catch (PDOException $e) {
    echo "Failed to get DB handle: " . $e->getMessage() . "\n";
    exit;
  }

answered Sep 06 '13 at 10:45

rohitcopyright

362
1
2
9

2

One can be sure even without catch. Uncaught exception produces a fatal error, you know. – Your Common Sense Sep 06 '13 at 10:49
I don't know about uncaught exception produces a fatal error....if you have any link please share with me...for the pdo I have read it from here http://php.net/manual/en/pdo.connections.php – rohitcopyright Sep 06 '13 at 10:58
I can't find any fatal error....if you have any example please explain? – rohitcopyright Sep 06 '13 at 11:17
You need to enable error reporting in PHP and cause exception to throw. – Your Common Sense Sep 06 '13 at 11:19
Thank you so much sir for all your valuable information. I will definitely try it in my spare time.. :) – rohitcopyright Sep 06 '13 at 11:29
Always glad to help. Feel free to ask more. – Your Common Sense Sep 06 '13 at 11:31

score -2 · Answer 4 · edited Jun 08 '16 at 10:43

-2

Thanks to Novocaine88's answer to use a try catch loop I have successfully received an error message when I caused one.

    <?php
    $dbhost = "localhost";
    $dbname = "pdo";
    $dbusername = "root";
    $dbpassword = "845625";

    $link = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbusername, $dbpassword);
    $link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    try {
        $statement = $link->prepare("INERT INTO testtable(name, lastname, age)
            VALUES(?,?,?)");

        $statement->execute(array("Bob","Desaunois",18));
    } catch(PDOException $e) {
        echo $e->getMessage();
    }
    ?>

In the following code instead of INSERT INTO it says INERT.

this is the error I got.

SQLSTATE[42000]: Syntax error or access violation: 1064 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 'INERT INTO testtable(name, lastname, age) VALUES('Bob','Desaunoi' at line 1

When I "fix" the issue, it works as it should. Thanks alot everyone!

edited Jun 08 '16 at 10:43

RJParikh

4,096
1
19
36

answered Sep 06 '13 at 11:01

Bob Desaunois

746
2
6
11

1

Don't you think there are a bit too much answers to this question? – Your Common Sense Sep 06 '13 at 11:04
1

Not to mention this is a wrong one – Your Common Sense Sep 06 '13 at 11:04
I'm sorry to see that the amount of answers have made a mess of this issue, however instead of acknowledging that the answer is invalid I would very much like you to elaborate why this is a wrong answer. – Bob Desaunois Sep 06 '13 at 12:19
1

because you should use ini_set(); instead of a "try catch loop" – Your Common Sense Sep 06 '13 at 12:29

PDO with INSERT INTO through prepared statements

4 Answers4

Linked

Related