I currently have the SELECT using PDO but I am having this error message
我现在有选择使用PDO，但我有这个错误消息。
SELECT a.*, UNIX_TIMESTAMP(a.createdon) AS timestamp, ct.cityname, ct.SeoCityName, scat.subcatname, scat.SeoSubcatName,
COUNT(*) AS piccount, p.picfile, UNIX_TIMESTAMP(feat.featuredtill) AS featuredtill
FROM prefix_ads a
INNER JOIN prefix_cities ct ON a.cityid = ct.cityid
INNER JOIN prefix_subcats scat ON a.subcatid = scat.subcatid
LEFT OUTER JOIN prefix_adxfields axf ON a.adid = axf.adid
LEFT OUTER JOIN prefix_adpics p ON a.adid = p.adid AND p.isevent = '0'
LEFT OUTER JOIN prefix_featured feat ON a.adid = feat.adid AND feat.adtype = 'A'
WHERE a.catid = '10'
AND a.enabled = '1' AND a.verified = '1' AND a.expireson >= NOW()
GROUP BY a.adid
ORDER BY a.createdon DESC
LIMIT 0, 18
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'xxxx' for key 'group_key'
SQLSTATE[23000]:完整性约束违反:密钥“group_key”的重复条目“xxxx”
xxxx = used to be if of the listing
如果是在列表中，则使用xxxx =。
Can someone please help?
有人能帮忙吗?
EXPLAIN
解释
Array
(
[id] => 1
[0] => 1
[select_type] => SIMPLE
[1] => SIMPLE
[table] => scat
[2] => scat
[type] => ALL
[3] => ALL
[possible_keys] => PRIMARY
[4] => PRIMARY
[key] =>
[5] =>
[key_len] =>
[6] =>
[ref] =>
[7] =>
[rows] => 152
[8] => 152
[filtered] => 100.00
[9] => 100.00
[Extra] => Using temporary; Using filesort
[10] => Using temporary; Using filesort
)
Array
(
[id] => 1
[0] => 1
[select_type] => SIMPLE
[1] => SIMPLE
[table] => a
[2] => a
[type] => ref
[3] => ref
[possible_keys] => subcatid,cityid,verified,enabled,expireson,catid,ct_ads
[4] => subcatid,cityid,verified,enabled,expireson,catid,ct_ads
[key] => subcatid
[5] => subcatid
[key_len] => 2
[6] => 2
[ref] => DB.scat.subcatid
[7] => DB.scat.subcatid
[rows] => 150
[8] => 150
[filtered] => 100.00
[9] => 100.00
[Extra] => Using where
[10] => Using where
)
Array
(
[id] => 1
[0] => 1
[select_type] => SIMPLE
[1] => SIMPLE
[table] => ct
[2] => ct
[type] => eq_ref
[3] => eq_ref
[possible_keys] => PRIMARY
[4] => PRIMARY
[key] => PRIMARY
[5] => PRIMARY
[key_len] => 2
[6] => 2
[ref] => DB.a.cityid
[7] => DB.a.cityid
[rows] => 1
[8] => 1
[filtered] => 100.00
[9] => 100.00
[Extra] =>
[10] =>
)
Array
(
[id] => 1
[0] => 1
[select_type] => SIMPLE
[1] => SIMPLE
[table] => axf
[2] => axf
[type] => ref
[3] => ref
[possible_keys] => adid
[4] => adid
[key] => adid
[5] => adid
[key_len] => 4
[6] => 4
[ref] => DB.a.adid
[7] => DB.a.adid
[rows] => 1
[8] => 1
[filtered] => 100.00
[9] => 100.00
[Extra] => Using index
[10] => Using index
)
Array
(
[id] => 1
[0] => 1
[select_type] => SIMPLE
[1] => SIMPLE
[table] => p
[2] => p
[type] => ref
[3] => ref
[possible_keys] => adid
[4] => adid
[key] => adid
[5] => adid
[key_len] => 5
[6] => 5
[ref] => DB.a.adid,const
[7] => DB.a.adid,const
[rows] => 1
[8] => 1
[filtered] => 100.00
[9] => 100.00
[Extra] =>
[10] =>
)
Array
(
[id] => 1
[0] => 1
[select_type] => SIMPLE
[1] => SIMPLE
[table] => feat
[2] => feat
[type] => eq_ref
[3] => eq_ref
[possible_keys] => adid
[4] => adid
[key] => adid
[5] => adid
[key_len] => 7
[6] => 7
[ref] => DB.a.adid,const
[7] => DB.a.adid,const
[rows] => 1
[8] => 1
[filtered] => 100.00
[9] => 100.00
[Extra] =>
[10] =>
)
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE scat ALL PRIMARY NULL NULL NULL 152 Using temporary; Using filesort 1 SIMPLE a ref subcatid,cityid,verified,enabled,expireson,catid,c... subcatid 2 DB.scat.subcatid 150 Using where 1 SIMPLE ct eq_ref PRIMARY PRIMARY 2 DB.a.cityid 1 1 SIMPLE axf ref adid adid 4 DB.a.adid 1 Using index 1 SIMPLE p ref adid adid 5 DB.a.adid,const 1 1 SIMPLE feat eq_ref adid adid 7 DB.a.adid,const 1
Regards G
认为G
1 个解决方案
#1
0
Your query isn't correct.
You shouldn't use that many select fields and group by just properly.
您的查询并不是正确的。您不应该仅仅正确地使用许多选择字段和组。
For example:
例如:
SELECT
UNIX_TIMESTAMP(a.createdon) AS timestamp,
ct.cityname,
ct.SeoCityName,
scat.subcatname,
scat.SeoSubcatName,
p.picfile,
UNIX_TIMESTAMP(feat.featuredtill) AS featuredtill,
COUNT(*) AS piccount
FROM
prefix_ads a
INNER JOIN prefix_cities ct ON a.cityid = ct.cityid
INNER JOIN prefix_subcats scat ON a.subcatid = scat.subcatid
LEFT OUTER JOIN prefix_adxfields axf ON a.adid = axf.adid
LEFT OUTER JOIN prefix_adpics p ON a.adid = p.adid AND p.isevent = '0'
LEFT OUTER JOIN prefix_featured feat ON a.adid = feat.adid AND feat.adtype = 'A'
WHERE
a.catid = '10'
AND a.enabled = '1'
AND a.verified = '1'
AND a.expireson >= NOW()
GROUP BY
1,2,3,4,5,6,7
ORDER BY
a.createdon DESC
LIMIT 0, 18
Since COUNT is a grouping function, it doesn't go on 'group by' clause
因为COUNT是一个分组函数，所以它不按“group by”子句进行。
#1
0
Your query isn't correct.
You shouldn't use that many select fields and group by just properly.
您的查询并不是正确的。您不应该仅仅正确地使用许多选择字段和组。
For example:
例如:
SELECT
UNIX_TIMESTAMP(a.createdon) AS timestamp,
ct.cityname,
ct.SeoCityName,
scat.subcatname,
scat.SeoSubcatName,
p.picfile,
UNIX_TIMESTAMP(feat.featuredtill) AS featuredtill,
COUNT(*) AS piccount
FROM
prefix_ads a
INNER JOIN prefix_cities ct ON a.cityid = ct.cityid
INNER JOIN prefix_subcats scat ON a.subcatid = scat.subcatid
LEFT OUTER JOIN prefix_adxfields axf ON a.adid = axf.adid
LEFT OUTER JOIN prefix_adpics p ON a.adid = p.adid AND p.isevent = '0'
LEFT OUTER JOIN prefix_featured feat ON a.adid = feat.adid AND feat.adtype = 'A'
WHERE
a.catid = '10'
AND a.enabled = '1'
AND a.verified = '1'
AND a.expireson >= NOW()
GROUP BY
1,2,3,4,5,6,7
ORDER BY
a.createdon DESC
LIMIT 0, 18
Since COUNT is a grouping function, it doesn't go on 'group by' clause
因为COUNT是一个分组函数，所以它不按“group by”子句进行。