dongsu0308 2017-08-11 11:08
浏览 216
已采纳

PHP PDO - 使用bindParam INSERT INTO不起作用

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

  • 写回答

3条回答 默认 最新

  • duanlvxing7707 2017-08-11 11:32
    关注

    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);
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥15 往复密封问题的两个问题
  • ¥15 DAC函数和STM32
  • ¥15 任务是接收数据并把数据写入DAC7311,这些代码能实现此功能吗
  • ¥15 分析FP -Growth代码运行内存太大而无法运行的原因
  • ¥20 qtcreat 使用msvc编译器开发软件运行时字体锯齿感严重
  • ¥15 为何显示keyerror fruit
  • ¥15 imageware粗糙度表面
  • ¥15 python使用pulp线性优化时报错
  • ¥15 为什么我的uibot导入py模块出错呀。py文件放在了uibot里对应的python文件夹了,卸了重安也不行
  • ¥15 开源或低价数据中台哪个最好