SQL连接PHP中的多个Postgres表

I have a PHP script that reads a user input which in turn returns a JSON array of data based on the value they enter.

我有一个PHP脚本，它读取用户输入，然后根据输入的值返回JSON数据数组。

The idea is to join two Postgres tables together, and then select all entries where the user value equals that of the uprn column (where this column is an integer).

我们的想法是将两个Postgres表连接在一起，然后选择用户值等于uprn列的所有条目（此列为整数）。

I have a similar PHP script which works ok so PHP etc is set up correctly, but when trying this code it returns a 500 - Internal Server Error.

我有一个类似的PHP脚本工作正常，所以PHP等设置正确，但尝试此代码时，它返回500 - 内部服务器错误。

Have I correctly assigned the single quotes in the right positions for both my query entry, and my array rows?

我是否在我的查询条目和数组行的正确位置正确分配了单引号？

<?php

if (isset($_GET['query'])) {
    // Connect to our database
    $conn = pg_connect("host=myhost port=myport dbname=mydb user=myuser password=mypass");

    // Retrieve the query
    $query = $_GET['query'];

    // Search the database for all similar items
    $sql = pg_query($conn, "SELECT l.uprn, l.ward, r.ward, r.name FROM addresses.temp_addresses_plus_wards AS l LEFT JOIN council.councillors AS r ON l.ward = r.ward WHERE l.uprn = {$query}");
    $array = array();

    while ($row = pg_fetch_array($sql)) {
        $address = array(
        'ward' => $row['r.ward'],
        'name' => $row['r.name']
        );
        array_push($array, $address);
    }

    sort($array);

    $jsonstring = json_encode($array);

    // Return the json array
    echo $jsonstring;
}

?>

1 个解决方案

#1

I realised that I just needed to remove the table alias for my array and it now works!

我意识到我只需要删除数组的表别名，它现在可以工作了！