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 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;

}