时间戳

首页 > 解决方案 > pg_prepare 和 pg_fetch_object

php - pg_prepare 和 pg_fetch_object

问题描述

我正在使用 postgres 扩展在 php 中准备一个语句。

然后我尝试使用pg_fetch_object.

从准备好的语句中没有返回任何行，但它应该。

我还收到以下警告：

警告：pg_prepare(): Query failed: ERROR: Prepared statement "parking" already exists in C:\xampp\htdocs\map\MapMarkers.php on line 33

$devices_query = pg_query($conn, "SELECT applications.name category, devices.* FROM app_as.application applications, app_as.device devices WHERE applications.id = devices.application_id");

//variables are bound in the loop
$parking_pst = pg_prepare($conn, "parking", "SELECT distinct on (name) name,application_name,longitude,latitude,parking_car_status status,received_at FROM V_DEVICE_PARKING WHERE name = $1");



while ($devices = pg_fetch_object($devices_query)) {

        pg_execute($conn, "parking",[$devices->name]);

        while ($parking = pg_fetch_object($parking_pst)) {
            $devices->parking_car_status = $parking->parking_car_status;
        }

    $data['DEVICES'][] = $devices;
}

标签： phppostgresql

解决方案

您收到此错误/警告是因为您正在pg_fetch_object()通话pg_prepare()。本质上，每次在while()循环中迭代时，您都会再次调用pg_prepare()，无意中尝试创建一个新的准备好的语句，称为“停车”。

您真正要做的是获得结果，pg_execute()然后使用以下方法提取结果pg_fetch_object()：

$devices_query = pg_query($conn, "SELECT applications.name category, devices.* FROM app_as.application applications, app_as.device devices WHERE applications.id = devices.application_id");

$prepare_result = pg_prepare($conn, "parking", "SELECT distinct on (name) name,application_name,longitude,latitude,parking_car_status status,received_at FROM V_DEVICE_PARKING WHERE name = $1");

// Maybe process $prepare_result as needed here

while ($devices = pg_fetch_object($devices_query)) {

        $execute_result = pg_execute($conn, "parking", [$devices->name]);

        while ($parking = pg_fetch_object($execute_result)) {
            $devices->parking_car_status = $parking->parking_car_status;
        }

    $data['DEVICES'][] = $devices;
}

推荐阅读