PHP PDO函数更新值插入错误

Question

我正在尝试创建一个名为 update 的函数来更新信息。但我在插入数据的更新查询语句部分搞砸了。它不返回任何内容。数据库表名称=“test_table”。这是我的 table 的照片，

function update( $con, $tbl_name, $colName, $colValue, $whereWhat, $equalsThis) {

    if( count($colName) > 0 && count($colValue) > 0 ) {
        if( count($colName) === count($colValue) ) {
            if( mb_strlen($tbl_name) > 0 ) {
                if( mb_strlen($whereWhat) > 0 ) {
                    if( mb_strlen($equalsThis) > 0){

                        for( $i = 0; $i < count($colName); $i++ ) {
                            if( mb_strlen($colName[$i]) > 0 && mb_strlen($colValue[$i]) > 0 && strpos($colName[$i], "=") !== false && strpos($colName[$i], "?") !== false ) {
                                $colName[$i]  = trim($colName[$i]);
                                $colValue[$i] = trim($colValue[$i]);
                            } else {
                                return "Doesn't contain = or wrong length";
                            }
                        }

                        $query = sprintf("UPDATE %s SET %s WHERE %s = %s",
                                 trim($tbl_name),
                                 array_values($colName),
                                 trim($whereWhat),
                                 trim($equalsThis)
                        );

                        $q = $con->prepare($query);
                        $q->execute(array_values($colValue) );

                        return "Worksz";

                    } else {
                        return "equalsThis identifier Required "; 
                    }
                } else {
                    return "whereWhat identifier Required "; 
                }
            } else {
                return "Table name required";
            }           
        } else {
            return "Require Same Arr len";
        }
    } else {
        return "wrong arr";
    }
}// end of function update();

这会调用该函数。格式应如下所示!

//UPDATE test_table SET col1 = ?, col2 = ? WHERE col2 = 1b;
echo update( $con, "test_table", array("col1 = ?,", "col2 = ?"), array("col1Val", "colVal2"), "col2", "1b");

$whereWhat 是由列名表示的行的指示符。$equals这是该列的值。

Answer 1

我认为您尝试做的事情会更好地使用类与错误处理函数相结合，如下所示(希望您对类有点熟悉，但如果您愿意，您可以摆弄这个):

<?php
    // This function purely returns errors
    function error_handler($error = false) {
            switch($error) {
                    case ('001'):
                        return "No values exist";
                    case ('002'):
                        return "Table name required";
                    case ('003'):
                        return "Where values required";
                }
        }

    // This engine does the sql assembly/execution and can be expanded on.
    class QueryEngine
        {
            public      $error;
            public      $statement;

            protected   $con;
            protected   $sql;

            public  function __construct($con = false)
                {
                    // It is presumed you are using an object connection
                    if(!is_object($con))
                        die("Invalid database connection.");
                    // Assign connection variable
                    $this->con  =   $con;
                }

            public  function update($table = false)
                {
                    $this->error    =   false;
                    $table          =   trim(preg_replace('/[^a-zA-Z0-9\_\-\.]/',"",$table));
                    $this->sql      =   array();

                    if($table == false || empty($table)) {
                            $this->error['table']   =   error_handler('002');
                            return $this;
                        }

                    $this->sql[]    =   "update";
                    $this->sql[]    =   "`$table`";

                    return $this;
                }

            public  function set($array = false)
                {
                    if(!is_array($array) || (is_array($array) && empty($array))) {
                            $this->error['values']  =   error_handler('001');
                            return $this;
                        }

                    $this->sql[]    =   "set";

                    foreach($array as $key => $value) {
                            $this->bind[":".$key]   =   $value;
                            $setvals[]              =   "`$key` = :$key";
                        }

                    if(isset($setvals))
                            $this->sql[]    =   implode(", ",$setvals);

                    return $this;
                }

            public  function where($array = false, $oper = "and", $comp = "=")
                {
                    if(!is_array($array) || (is_array($array) && empty($array))) {
                            $this->error['values']  =   error_handler('003');
                            return $this;
                        }

                    if(!in_array("where",$this->sql))
                        $this->sql[]    =   "where";

                    $i = 0;
                    foreach($array as $key => $value) {

                            $this->bind[":where".$i]    =   $value;
                            $whereset[]                 =   "`$key` ".$comp." :where".$i;
                            $i++;
                        }

                    if(isset($whereset))
                        $this->sql[]    =   trim(implode($oper." ",$whereset));

                    return $this;
                }

            public  function Combine()
                {
                    $this->statement    =   implode(" ",$this->sql);
                    return $this;
                }

            public  function write()
                {
                    $this->Combine();

                    if($this->error == false) {
                            $query      =   $this->con->prepare($this->statement);
                            $query->execute($this->bind);
                        }

                    return $this;
                }
        }

使用:

// Create a new instance. $con is your PDO connection (obviously)
$engine =   new QueryEngine($con);
// Assign the bulk of the method chain to $query. You could chain it all together
// but this way you can do quick error check before trying the sql execute`
$query  =   $engine ->update("test_table")
                    ->set(array("col1"=>"value1","col2"=>"value2"))
                    ->where(array("col1"=>"value3"));

// If the error variable is still false, try inserting into database
if($query->error == false) {
        // This is just a direct access to the final query statement
        //echo $query->Combine()->statement;
        // Will try and write to database
        $query->write();
    }
// If there is an error, show the error.
// The errors can be expanded out.
else
    print_r($query->error);
?>

sql组装成功:

// SQL statement
update `test_table` set `col1` = :col1, `col2` = :col2 where `col1` = :where0
// Bind array
Array
(
    [:col1] => value1
    [:col2] => value2
    [:where0] => value3
)

执行失败(缺少信息):

Array
(
    [table] => Table name required
)