エラーメッセージ
状態:-
閲覧数:2,786
投稿日:2014-10-22
更新日:2014-10-22
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1052 Column '★★' in where clause is ambiguous' in /〇〇.php:△△
エラー内容
整合性制約違反
・WHERE条件が曖昧なため何を参照すれば良いか分からない、と言う致命的なエラー
・★★カラムがどのテーブルに所属しているか不明な場合等に発生
実際に遭遇した例
エラーメッセージ
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'public' in where clause is ambiguous' in /〇〇.php:42
エラー発生箇所
public function fetchAllElem()
{
$sql = "
SELECT a.*, u.display_name, u.site_id, s.situation, s.color, z.*,e.elem_title,e.elem_url
FROM entry a
LEFT JOIN user u ON a.user_id = u.id
LEFT JOIN state s ON a.state_id = s.state_id
LEFT JOIN(
SELECT
a.category_id, a.category, a.parent_id,
CASE WHEN a.parent_id = 0
then a.fonticon
else b.fonticon
end AS fonticon,
CASE WHEN a.parent_id = 0
then a.color
else b.color
end AS color,
CASE WHEN a.parent_id = 0
then a.category
else b.category
end AS parent_category,
CASE WHEN a.parent_id = 0
then a.category_id
else a.parent_id
end AS parent_id2
FROM
category a
LEFT JOIN
category b on a.parent_id = b.category_id
AS z ON a.category_id = z.category_id
RIGHT JOIN elem e ON a.id = e.entry_id
WHERE public = 1
ORDER BY GREATEST(a.`created_at`,a.`modified`) DESC
";
return $this->fetchAll($sql);
}
エラー原因
・publicカラムがどのテーブルに所属しているか不明
エラー修正
所属テーブル名付与
・a.public
public function fetchAllElem()
{
$sql = "
SELECT a.*, u.display_name, u.site_id, s.situation, s.color, z.*,e.elem_title,e.elem_url
FROM entry a
LEFT JOIN user u ON a.user_id = u.id
LEFT JOIN state s ON a.state_id = s.state_id
LEFT JOIN(
SELECT
a.category_id, a.category, a.parent_id,
CASE WHEN a.parent_id = 0
then a.fonticon
else b.fonticon
end AS fonticon,
CASE WHEN a.parent_id = 0
then a.color
else b.color
end AS color,
CASE WHEN a.parent_id = 0
then a.category
else b.category
end AS parent_category,
CASE WHEN a.parent_id = 0
then a.category_id
else a.parent_id
end AS parent_id2
FROM
category a
LEFT JOIN
category b on a.parent_id = b.category_id
AS z ON a.category_id = z.category_id
RIGHT JOIN elem e ON a.id = e.entry_id
WHERE a.public = 1
ORDER BY GREATEST(a.`created_at`,a.`modified`) DESC
";
return $this->fetchAll($sql);
}
Invalid parameter number: parameter was not defined
Fatal error: require_once(): Failed opening required 'XML/RSS.php'