pg_prepare 和 pg_fetch_object

Question

我正在使用 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;

}

Answer 1

您收到此错误/警告是因为您正在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 herewhile ($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;}