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- gt prepare  INSERT INTO testtable name  lastname  age          VALUES  Bob   Desaunois   18            statement- gt 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

User · Answer

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.

User · Answer

You should be using it like so   lt  php  dbhost    localhost    dbname    pdo    dbusername    root    dbpassword    845625     link   new PDO  mysql host  dbhost dbname  dbname    dbusername   dbpassword     statement    link- gt prepare  INSERT INTO testtable  name  lastname  age      VALUES   fname   sname   age       statement- gt execute        fname    gt   Bob        sname    gt   Desaunois        age    gt   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- gt prepare  INSERT INTO testtable  name  lastname  age      VALUES                statement- gt 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

User · Answer

Thanks to Novocaine88 s answer to use a try catch loop I have successfully received an error message when I caused one        lt  php      dbhost    localhost        dbname    pdo        dbusername    root        dbpassword    845625         link   new PDO  mysql host  dbhost dbname  dbname    dbusername   dbpassword        link- gt setAttribute PDO  ATTR ERRMODE  PDO  ERRMODE EXCEPTION        try            statement    link- gt prepare  INERT INTO testtable name  lastname  age              VALUES                     statement- gt execute array  Bob   Desaunois  18          catch PDOException  e            echo  e- gt getMessage                gt    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

User · Answer

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- gt getMessage       n       exit

[php] PDO with INSERT INTO through prepared statements

Examples related to php

Examples related to mysql

Examples related to sql

Examples related to pdo