可以'；t在postgresql-pdo中插入和读取数据 - can't insert and read data in postgresql pdo

can't insert and read data in postgresql pdo

我刚刚将数据库从mysql迁移到postgresql。以及我使用PDO的代码。我已经搜索了如何使用PDO连接到postgresql，它是有效的。但当调用函数创建数据和读取数据时，我遇到了麻烦。我的代码中总是出现"无法创建数据"的警告。

但当我调用search函数时，postgresql中的数据就出来了。请帮我解决这个问题。

这是代码：

class barang.php 
<?php
class Barang
{
    // database connection and table name
    private $conn;
    private $table_name = "barang";
    // object properties
    public $id_barang;
    public $nama_barang;
    public $harga_satuan;
    public function __construct($db){
        $this->conn = $db;
    }
    // create barang
    function create(){
        // to get time-stamp for 'created' field
        //write query
        $query = "INSERT INTO 
                    " . $this->table_name . " 
                    id_barang = ?, nama_barang = ? , harga_satuan = ?";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(1, $this->id_barang);
        $stmt->bindParam(2, $this->nama_barang);
        $stmt->bindParam(3, $this->harga_satuan);
        if($stmt->execute()){
            return true;
        }else{
            return false;
        }
    }
    // read products
    function readAll($page, $from_record_num, $records_per_page)
    {
        $query = "SELECT 
                    id_barang, nama_barang, harga_satuan
                FROM 
                    " . $this->table_name . " 
                ORDER BY 
                    id_barang 
                LIMIT 
                    {$from_record_num}, {$records_per_page}";
        $stmt = $this->conn->prepare( $query );
        $stmt->execute();
        return $stmt;
    }
    function Search()
    {
        $query = "SELECT 
                    id_barang, nama_barang, harga_satuan
                FROM 
                    " . $this->table_name . " 
                WHERE 
                    id_barang LIKE :search OR nama_barang LIKE :search ";
        $stmt = $this->conn->prepare($query);
        $search = $_GET['search'];
        $stmt->bindValue(':search','%' . $search . '%', PDO::PARAM_STR);
        $stmt->execute();
        return $stmt;
    }
}
?>

警报无法在此代码上创建数据捕获：

$database = new Database();
$db = $database->getConnection();
// if the form was submitted
if($_POST)
{
    // instantiate barang object
    include 'objects/barang.php';
    $barang = new Barang($db);
    // set barang property values
    $barang->id_barang = $_POST['id_barang'];
    $barang->nama_barang = $_POST['nama_barang'];
    $barang->harga_satuan = $_POST['harga_satuan'];
    // create the barang
    if($barang->create()){
        echo "<div class='"alert alert-success alert-dismissable'">";
            echo "<button type='"button'" class='"close'" data-dismiss='"alert'" aria-hidden='"true'">&times;</button>";
            echo "Data barang was created.";
        echo "</div>";
    }
    // if unable to create the barang, tell the user
    else{
        echo "<div class='"alert alert-danger alert-dismissable'">";
            echo "<button type='"button'" class='"close'" data-dismiss='"alert'" aria-hidden='"true'">&times;</button>";
            echo "Unable to create barang.";
        echo "</div>";
    }

column=value系列的insert语句不是标准SQL，它是MySQL特定的语法，PostgreSQL不支持这种语法。您应该使用ANSI-SQL语法：

$query = "INSERT INTO 
            " . $this->table_name . " 
            (id_barang, nama_barang , harga_satuan) VALUES (?, ?, ?)";

编辑：

MySQL使用的limit子句也不是标准的。在PostgreSQL中，语法略有不同：

$query = "SELECT 
                id_barang, nama_barang, harga_satuan
            FROM 
                " . $this->table_name . " 
            ORDER BY 
                id_barang 
            LIMIT 
                {$records_per_page}"
            OFFSET
                {$from_record_num};