PHP执行PDO插入语句报people.addressId列不存在但表实际存在怎么解决

问题原因 #

错误核心是反引号使用规则错误，以及SELECT子句多余的括号问题：

1. 带表名的列名加反引号时，需要分别包裹表名和列名，不能把表名.列名整体包裹：
   • 你写的`people.addressId`会被MySQL识别为一个名为people.addressId的独立字段，而不是people表下的addressId字段，自然会提示字段不存在
   • 正确的写法应该是`people`.`addressId`，表名和列名分别用反引号包裹
2. SELECT后面的字段列表不需要额外加外层括号，加括号后会被MySQL识别为行构造器返回单个复合值，不符合INSERT要求的多列字段匹配规则。

你直接在MySQL中执行SQL正常，大概率是你直接运行时没有给people.addressId整体加反引号，所以能正常识别表和字段的关联关系。

修正后代码 #

$incomeMonthly = $_POST['incomeMonthly'];
$isIncomeSourceSalary = $_POST['isIncomeSourceSalary'];
$isIncomeSourceBusiness = $_POST['isIncomeSourceBusiness'];
$isIncomeSourceRemittance = $_POST['isIncomeSourceRemittance'];

$sql = "INSERT INTO `households` (
    `incomeMonthly`, 
    `isIncomeSourceBusiness`, 
    `isIncomeSourceSalary`,
    `isIncomeSourceRemittance`, 
    `addressId`, 
    `personId`, 
    `householdName`, 
    `householdHeadName`
)
SELECT 
    :a,
    :b,
    :c,
    :d,
    `people`.`addressId`,
    `personId`,
    `lName`,
    `fName`
FROM `people` 
INNER JOIN `addresses` ON `people`.`addressId` = `addresses`.`addressId`
WHERE `isHeadOfFamily`=1 AND `barangay`='San Jose'";
$stmt = $db->prepare($sql);
$stmt->bindParam(":a", $incomeMonthly);
$stmt->bindParam(":b", $isIncomeSourceBusiness);
$stmt->bindParam(":c", $isIncomeSourceSalary);
$stmt->bindParam(":d", $isIncomeSourceRemittance);
$stmt->execute();

内容的提问来源于stack exchange，提问作者inNeedOfHelp