data insertion using PDO

Question

I am new in PDO. I heard that it is more friendly to the programmer to use multiple databases in a project and it is much more secure, so I want to learn PDO. I want to insert data into MySQL using PDO but I can't, and no error message comes. I used the following code:

<?php 

   class ManageUsers02 {             
     private   $db_con;
     public    $db_host  = "localhost";  // I run this code on my localhost
     public    $db_name  = "todo";       // database name
     public    $db_user  = "root";       // username
     public    $db_pass  = "";           // password

     function __construct() {           
        try {
            // connect to database using PDO
           $this->db_con = new PDO("mysql:host=$this->db_host;db_name=$this->db_name", $this->db_user, $this->db_pass);             
        } catch (PDOException $exc) {  // PDO exception handling
           echo $exc->getMessage();    
        }             
     }

     public function reg_user($username, $password, $ip_address, $reg_date, $reg_time ) {
       try {
          // preparing sql query 
          $query = $this->db_con->prepare("INSERT INTO user_reg (username, password, ip_address, reg_date, reg_time) VALUES ( ?, ?, ?, ?, ? )");  
       }
       catch( PDOException $exc )  {  // PDO exception handling
          echo $exc->getMessage();
       }

       try {
           // execute sql query
           $query->execute(array($username, $password, $ip_address, $reg_date, $reg_time)); 
       }
       catch( PDOException $exc )  {
          echo $exc->getMessage();
       }

       $counts = $query->rowCount();  // return value of affected row
       // here it should be return 1       

       echo "<br /> count :: <b> " . $counts . "</b> <br />";  // shows result 0
       // no value inserted 
     }


   }

   $user_reg = new ManageUsers02();         

   $user_reg->reg_user('pdo_name', 'pdo_password', '127.0.0.1', '2013-2-6', '4:20 am');
 ?>

Take a look at this answer - http://stackoverflow.com/a/15990858/689579 - as it addresses how to find errors when using PDO — Sean, Sep 08 '13 at 01:12
PDO won't throw exceptions by default. Check out Sean's link for how to change that. — Brad, Sep 08 '13 at 01:33
Just a guess.... 2013-2-6 maybe have to be 2013-02-06 and maybe 4:20 am has to be 04:20 — bestprogrammerintheworld, Sep 08 '13 at 01:56

score 0 · Answer 1 · answered Sep 08 '13 at 05:53

This fails because of insert a string to a mysql Time field.

$sql =  "INSERT INTO user_reg ( ... , reg_time) VALUES (..., '4:20 am');

If you want to use '4:20 am' you should use.

TIME( STR_TO_DATE( ? , '%h:%i %p' ))

like

$sql =  "INSERT INTO user_reg ( ... , reg_time) VALUES 
                ( ?, ?, ?, ?, TIME( STR_TO_DATE( ? , '%h:%i %p' )))";

and give the class a ok and a counts .

<?php 

   class ManageUsers02 {             
     ...
     public    $counts   = 0;
     public    $ok       = false;

     function __construct() {           
        try {
          $this->db_con = new PDO("mysql:dbname=$this->db_name;host=$this->db_host", $this->db_user, $this->db_pass);             
        } catch (PDOException $exc) {  // PDO exception handling
           echo $exc->getMessage();
          return;
        } 
        if (!$this->db_con) {
          return;
        }
     $this->ok = true;   
     }

     public function reg_user($username, $password, $ip_address, $reg_date, $reg_time ) {
       $this->counts = 0;
       $this->ok = false;     
       $sql =  "INSERT INTO user_reg (username, password, ip_address,
                reg_date, reg_time) VALUES 
                ( ?, ?, ?, ?, TIME( STR_TO_DATE( ? , '%h:%i %p' )))";
       try {              
        $query = $this->db_con->prepare($sql);  
       }
       catch( PDOException $exc )  {  // PDO exception handling
        echo $exc->getMessage();
        return;
       }
        if (!$query) {
          return;
        }
       try {
       $this->ok = $query->execute(array($username, $password, $ip_address, $reg_date, $reg_time));
       }
       catch( PDOException $exc )  {
          echo $exc->getMessage();
          $this->ok = false;
          return;
       }
       if ($this->ok) {
       $this->counts = $query->rowCount();  // return value of affected row
       }
     }
   }

   $user_reg = new ManageUsers02();
   if ($user_reg->ok) {
       $user_reg->reg_user('pdo_name4', 'pdo_password4',
       '127.0.0.1', '2013-2-6', '04:20 am' );
       if ($user_reg->ok) {
              echo "<br /> count :: <b> " . $user_reg->counts . "</b> <br />";
   } else { echo "Error : Insert failed";}
   } else { echo "Error : Connection failed: ";}
 ?>

@YourCommonSense : thanks for downvote : `Insert can not fail of incorrect data` ???? Never heard that . — moskito-x, Sep 08 '13 at 05:58
@YourCommonSense, on MySQL 5.6 (or when the SQL_MODE includes STRICT_TRANS_TABLES), you get this: `ERROR 1292 (22007): Incorrect datetime value: '4:20 am' for column 'reg_time' at row 1`. — Bill Karwin, Sep 08 '13 at 06:19
@YourCommonSense : I have tried it . Without `TIME( STR_TO_DATE( ? , '%h:%i %p' ))` and a given value of `'4:20 am'` it failed. — moskito-x, Sep 08 '13 at 06:21

score 0 · Answer 2 · answered Sep 08 '13 at 06:33

0

+1 to the answer from @moskito-x for spotting the incorrect time format. But there are a couple of other functional problems with your code.

$this->db_con = new PDO("mysql:host=$this->db_host;db_name=$this->db_name", ...

You need to use dbname in the DSN, not db_name. See http://www.php.net/manual/en/ref.pdo-mysql.connection.php

$this->db_con = new PDO("mysql:host=$this->db_host;dbname=$this->db_name", ...

Also you need to enable the error mode like this:

$this->db_con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

If you don't, PDO doesn't throw exceptions on prepare() or execute(), but those functions just return false if there's an error.

answered Sep 08 '13 at 06:33

Bill Karwin

538,548
86
673
828

+1 I have seen it `db_name=$this->db_name` but forgot to mention in my answer. – moskito-x Sep 08 '13 at 06:37
@YourCommonSense : if you were to find the typos, we would have nothing to do. your answer was the first. Also the answer of Bill Karwin is correct. Why you downvote this answer to ? – moskito-x Sep 08 '13 at 06:44
@moskito-x, don't bother to feed the trolls. :-) – Bill Karwin Sep 08 '13 at 06:45
@moskito-x there are two ways to fix the program: to goggle into code hoping to spot the error (while it can be somewhere else), or just to run it once and have all the errors revealed. I merely prefer the second one. – Your Common Sense Sep 08 '13 at 06:49
Yes we could see how @sean has helped you with your `tested` code. `should it be $user_reg = new ManageUsers02($pdo); instead of $user_reg = new ManageUsers02(); ?` – moskito-x Sep 08 '13 at 07:03

Your Common Sense · Answer 3 · 2013-09-08T06:42:02.207

I don't know what's particular problem with your insert, but your implementation is just terrible. By design.

First of all you have to get rid of PDO connection code in the class. You have to create a PDO instance separately, and then only pass it in constructor.

Secondly, you have to get rid of all these try..catch which makes your code bloated with not a slightest benefit.

Also, No class method should ever output a single byte, but return only.

So, it have to be something like

<?php 

ini_set('display_errors',1);
error_reporting(E_ALL);

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$opt = array(
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
);
$pdo = new PDO($dsn, $user, $pass, $opt);

class ManageUsers02
{             
    private $db_con;

    function __construct($db_con)
    {
        $this->db_con = $db_con;
    }
    public function reg_user($username, $password, $ip_address, $reg_date, $reg_time )
    {
        $sql "INSERT INTO user_reg (username, password, ip_address, reg_date, reg_time) 
              VALUES ( ?, ?, ?, ?, ? )";
        $query = $this->db_con->prepare($sql);  
        $query->execute(array($username, $password, $ip_address, $reg_date, $reg_time)); 
        return $counts = $query->rowCount();  // return value of affected ro
    }
}
$user_reg = new ManageUsers02($pdo);         
$count = $user_reg->reg_user('pdo_name', 'pdo_password', '127.0.0.1', '2013-2-6', '4:20 am');
var_dump($count);

This setup at least will tell you if something goes wrong.

Since you are creating the PDO connection outside the class, and passing it in the constructor, should it be `$user_reg = new ManageUsers02($pdo);` instead of `$user_reg = new ManageUsers02();`? — Sean, Sep 08 '13 at 04:50
**No class method should** ever output a single byte, but return only. And what's about that? from your answer : `return $counts = $query->rowCount();` — moskito-x, Sep 08 '13 at 07:17

data insertion using PDO

3 Answers3