PHP PDO - INSERT INTO with bindParam does not work

Question

I stucked at this little piece of code:

$fruit_object = new fruit(1234, 'apple', 'red apple');    

try {
    $dbh = new PDO('mysql:host=localhost;charset=utf8;dbname=database', 'user', 'password');
    $exc = $dbh->prepare("INSERT INTO fruit( type, name) VALUES (:type, :name);");                               
    //$exc->bindParam(':id',   $fruit_object->id,   PDO::PARAM_INT);
    $exc->bindParam(':type', $fruit_object->type, PDO::PARAM_STR);
    $exc->bindParam(':name', $fruit_object->name, PDO::PARAM_STR);
    $exc->execute();
    $dbh = null;
    $exc = null;
} catch (PDOException $e) {
    //this function just do an echo with static content
    $this->error_database($e);
    $dbh = null;
    $exc = null;
    die();
}

I already used PDO for SELECTING things from my database, but with INSERTING something, it does not work. The user has the access to only INSERT stuff - i already tried that on my backend successfully.

So here is the error:

Fatal error: Uncaught Error: Cannot access private property fruit_object::$type

This is my fruit_object class:

<?php


class fruit
{
private $id;
private $type;
private $name;

function __construct($id, $type, $name)
{
    $this->id = $id;
    $this->type = $type;
    $this->name = $name;

}

function __toString()
{
    return $this->name;
}
}

For martin:

INSERT INTO fruit (id, type, name) VALUES (DEFAULT, 'apple', 'red apple');

My database is running on a MySQL server - is this the cause ? Do i have to work with questionmarks (?)?

Thank you, Louis

Answer 1

You are missing closing brace for prepare and semi colon

$exc = $dbh->prepare("INSERT INTO fruit(id, type, name) VALUES (:id, :type, :name)");

if not working then add this line to check error

print_r($exc->errorInfo());

Answer 2

Answering Question 1:

PHP PDO - INSERT INTO with bindParam does not work

If you're inserting an ID into an auto increment field and you've already inserted then it will cause a MySQL error (dupliate A_I field value) – Martin

---

Yeah i know that, I am using the DEFAULT keyword in my real statement. – louis12356

---

explain; default keyword for what? – Martin

---

There is a SQL keyword 'DEFAULT' which automaticly counts the ID up. – louis12356

---

You should not be supplying a value for your Auto Increment (id) column. What it looks like is that you're giving a MySQL Instruction via PDO variable which will Never Work. This is because PDO uses Prepared Statements and so variables are only ever going to be variables and can never be instructions.

The Default keyword in MySQL is an instruction to the MySQL program. This instruction will be ignored, not only because it is disallowed but also because you're passing a STRING value to PDO INSERT which claims it should be an INT :

 $exc->bindParam(':id',   $fruit_object->id,   PDO::PARAM_INT);

If $fruit_object->id == "DEFAULT" this is NOT AN INTEGER; so therefore PDO will not run the query.

Solution

Auto Increment values simply don't need to be inserted, ignore them:

try {
    $dbh = new PDO('mysql:host=localhost;charset=utf8;dbname=database', 'user', 'password');
    $exc = $dbh->prepare("INSERT INTO fruit( type, name) VALUES ( :type, :name);");                               
    // $exc->bindParam(':id',   $fruit_object->id,   PDO::PARAM_INT);
    $exc->bindParam(':type', $fruit_object->type, PDO::PARAM_STR);
    $exc->bindParam(':name', $fruit_object->name, PDO::PARAM_STR);
    $exc->execute();
    $dbh = null;
    $exc = null;
}

---

Example of what you're trying to run:

 INSERT INTO fruit (id, type, name) VALUES (DEFAULT, 'apple', 'red apple');

But due to the security constraints of PDO (ignoring the String/Int data type issue) what is actually being run is:

INSERT INTO fruit (id, type, name) 
     VALUES ( <int var> "DEFAULT", <str var> "apple", <str var> "red apple"); 

So you're trying to insert the string variable "Default" into an integer column in MySQL

---

Answering Question 2:

 Fatal error: Uncaught Error: Cannot access private property fruit_object::$type

This is due to your class setting the value of this type to being Private rather than Public, which means the value can not be shown outside of the class (this is a slight over simplification but time is pressing me!)

What you need to do is either:

• Set your values accessability to being public
• OR, build setter and getter methods into your class so you can whip out these private values as often as you want (oh matron!!).

So:

class fruit
{
    private $id;
    private $type;
    private $name;

    /***
     * Getter
     ***/ 
    function getType()
    {
        return $this->type;
    }

    /***
     * Setter
     ***/
    function setType($value){
         $this->type = $value;
    }
}

Then in your PDO:

$exc->bindParam(':type', $fruit_object->getType(), PDO::PARAM_STR);

This will output your value to the script.

If you want a much simpler approach you can simply replace private $name; with public $name; and then the named variables value will be accessible from outside the class :

 $exc->bindParam(':name', $fruit_object->name, PDO::PARAM_STR);

• Read more about Class value access levels