Cannot insert into MySQL database using PDO....No errors

Question

I have a problem where I cannot insert anything into a MySQL database using PDO.

I get no errors but whenever I check the database if the row has been inserted, the table is empty.

I know I have a connection to the database as I am able to select but not insert.

Here is my class that entends PDO

class Database extends PDO
{

    public function __construct($DB_TYPE, $DB_HOST, $DB_NAME, $DB_USER, $DB_PASS)
    {
        parent::__construct($DB_TYPE.':host='.$DB_HOST.';dbname='.$DB_NAME, $DB_USER, $DB_PASS);

        //parent::setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTIONS);
    }

    /**
     * select
     * @param string $sql An SQL string
     * @param array $array Paramters to bind
     * @param constant $fetchMode A PDO Fetch mode
     * @return mixed
     */
    public function select($sql, $array = array(), $fetchMode = PDO::FETCH_ASSOC)
    {
        $sth = $this->prepare($sql);
        foreach ($array as $key => $value) {
            $sth->bindValue("$key", $value);
        }

        $sth->execute();
        return $sth->fetchAll($fetchMode);
    }

    /**
     * insert
     * @param string $table A name of table to insert into
     * @param string $data An associative array
     */
    public function insert($table, $data)
    {
        /*ksort($data);

        $fieldNames = implode('`, `', array_keys($data));
        $fieldValues = ':' . implode(', :', array_keys($data));

        $sth = $this->prepare("INSERT INTO $table (`$fieldNames`) VALUES ($fieldValues)");

        foreach ($data as $key => $value) {
            $sth->bindValue(":$key", $value);
        }*/

        $sth = $this->prepare("INSERT INTO user (`login`, `password`, `role`) VALUES (:login, :password, :role)");

        $sth->bindValue(':login', 'username');
        $sth->bindValue(':password', 'password');
        $sth->bindValue(':role', 'owner');

        $sth->execute();

        /*if ($sth->errorCode() != 0) {

            $arr = $sth->ErrorInfo();
            throw new Exception('SQL failure:'.$arr[0].':'.$arr[1].':'.$arr[2]);
        }*/

        $sth->debugDumpParams();
    }

Here is my table structure (kept it simple for debugging).

CREATE TABLE IF NOT EXISTS `user` (
  `userid` int(11) NOT NULL AUTO_INCREMENT,
  `login` varchar(25) NOT NULL,
  `password` varchar(64) NOT NULL,
  `role` enum('default','admin','owner') NOT NULL DEFAULT 'default',
  PRIMARY KEY (`userid`)
) ENGINE=InnoDB

EDIT:

I found out where the problem lies. The problem is with the data array. If I use $data['first_name'] when calling the insert() function, it fails but if I replace all those $data[] values with hard codes values, it works so the problem lies with $data[]

I create an array of POST variables

// get all the post data from the registration form
$data = array();        
$data['first_name'] = $_POST['first_name'];
$data['last_name'] = $_POST['last_name'];
$data['gender'] = $_POST['gender'];
$data['email'] = $_POST['email'];
$data['interests'] = $_POST['interests'];
$data['username'] = $_POST['username'];
$data['password'] = $_POST['password'];
$data['newsletter'] = $_POST['newsletter'];
$data['terms'] = $_POST['terms'];
$data['captcha'] = $_POST['captcha'];

This gets passed to the create function

public function create($data) {
   $this->db->insert('users', array(
    'first_name' => $data['first_name'],       //this won't work
    'first_name' => 'whatever the name is',    //this will work
    'last_name' => $data['last_name'],
    'email' => $data['email'],
    'username' => $data['username'], 
    'password' => $password,
    'user_salt' => $user_salt,
    'sex' => $data['gender'],
    'interests' => $data['interests'],
    'signup_date' => date('Y-m-d H:i:s'), 
    'verification_code' => $code
   ));

and this is where it gets inserted

public function insert($table, $data)
{
    ksort($data);

    $fieldNames = implode('`, `', array_keys($data));
    $fieldValues = ':' . implode(', :', array_keys($data));

    $sth = $this->prepare("INSERT INTO $table (`$fieldNames`) VALUES ($fieldValues)");

    foreach ($data as $key => $value) {
        $sth->bindValue(":$key", $value);
    }

    $sth->execute();
}

How would I find out? I've just tried doing an insert using another database class and it inserts fine so I don't see what the problem is — AdRock
– AdRock, Commented Oct 7, 2013 at 10:13
And you are getting no error? Usually that always means the INSERT itself was really successful. You may want to make sure your code doesn't delete the row you just insert it or drop and recreate the table after your insert. You should also make sure you are actually selecting the right database. Or you could be using the wrong HOST. — Attila Szeremi
– Attila Szeremi, Commented Oct 7, 2013 at 11:09
None of your DB calls are checking for error conditions, and you've DISABLED exception mode, so you're simply assuming you're connecting and/or actually executing queries. — Marc B
– Marc B, Commented Oct 7, 2013 at 14:45

Faruq · Accepted Answer · 2013-10-07 15:31:24Z

0

 $fieldValues = implode(':,', array_keys($data));

edited Oct 7, 2013 at 15:31

answered Oct 7, 2013 at 14:30

Faruq

501 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rohan Kumar · Accepted Answer · 2013-11-14 04:51:02Z

0

Try this,

$fieldNames = implode(', ', array_keys($data));
$fieldValues = ':' . implode(', :', array_values($data));

$sth = $this->prepare("INSERT INTO $table ($fieldNames) VALUES ($fieldValues)");

answered Nov 14, 2013 at 4:51

Rohan Kumar

40.7k11 gold badges81 silver badges111 bronze badges

1 Comment

Mike S. Over a year ago

I tried this, but I'm getting the error Connection failed: SQLSTATE[HY093]: Invalid parameter number: no parameters were bound1. My function can be found in this paste: pastie.org/8612517. Any ideas why?

Collectives™ on Stack Overflow

Cannot insert into MySQL database using PDO....No errors

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related