格式化MySQL查询：SQL语法中有错误;查看与MySQL服务器版本对应的手册

I am posting here after reading many similar threads but that doesn't resolve my problem, so I need MySQL experts to help me identify what's wrong with this query:

我在阅读了很多类似的线程之后在这里发帖但是这并没有解决我的问题,所以我需要MySQL专家帮我识别这个查询的错误:

The query looks like the following:

查询如下所示:

SELECT DISTINCT(p.ID), p.*, pm.*, t.*, tt.*, tr.* 
FROM wp_posts p, wp_postmeta pm, wp_terms as t, wp_term_taxonomy as tt, wp_term_relationships as tr 
'WHERE p.ID = pm.post_id 
AND t.term_id = tt.term_id 
AND tt.taxonomy = wpsc_product_category 
AND tt.term_taxonomy_id = tr.term_taxonomy_id 
AND tr.object_id = p.ID 
AND p.post_type = wpsc-product 
AND pm.meta_key != _wpsc_price 
AND (p.post_title LIKE '%'\"some string to search\"'%' 
     OR p.post_content LIKE '%'\"some string to search\"'%' 
     OR pm.meta_value = '\"some string to search\"' 
     OR t.name LIKE '%'\"some string to search\"'%') 
GROUP BY p.ID ORDER By p.ID '

I don't what's wrong in the query format to get the following error:

我没有在查询格式中出错以获得以下错误:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''WHERE p.ID = pm.post_id AND t.term_id = tt.term_id AND tt.taxonomy = wpsc_produ' at line 1

In my query, I need to integrate strings, and I ma urgent so I cannot change completely to PDO. How can I fix that error? Thanx in advance.

在我的查询中,我需要集成字符串,我很紧急,所以我不能完全改为PDO。我该如何解决这个错误?提前完成。

PS: I am running on local server, so php info shows me this information about mySQL server, I guess it's the version:

PS:我在本地服务器上运行,所以php信息显示了我关于mySQL服务器的信息,我想这是版本:

Client API version  5.5.29

3 个解决方案

#1

According to the error message, you have an extra single quote before the WHERE clause.

根据错误消息,您在WHERE子句之前有一个额外的单引号。

As a sidenote, DISTINCT keyword will be more likely to be useless because it will only work if all values in all columns are equal on both row otherwise the row is unique even if they have the same value.

作为旁注,DISTINCT关键字更可能没用,因为只有当所有列中的所有值在两行上相等时才会起作用,否则即使行具有相同的值,该行也是唯一的。

#2

You don't need quote marks around the WHERE clause.

WHERE子句周围不需要引号。

#3

You should always keep the same convention across your query to make it more readable and less likely to get an error.

您应始终在查询中保持相同的约定,以使其更具可读性,并且不太可能出现错误。

This does not affect the outcome but if you use aliases "wp_posts p" OR "wp_posts AS p" are equivalent but you mix in this query. I have made the convention the same for you and fixed your query below.

这不会影响结果但是如果你使用别名“wp_posts p”或“wp_posts AS p”是等效的,但你混合了这个查询。我已经为您制定了相同的约定,并在下面修复了您的查询。

SELECT DISTINCT(p.ID), p.*, pm.*, t.*, tt.*, tr.* 
FROM wp_posts p, wp_postmeta pm, wp_terms t, wp_term_taxonomy tt, wp_term_relationships tr 
WHERE p.ID = pm.post_id 
AND t.term_id = tt.term_id 
AND tt.taxonomy = wpsc_product_category 
AND tt.term_taxonomy_id = tr.term_taxonomy_id 
AND tr.object_id = p.ID 
AND p.post_type = wpsc-product 
AND pm.meta_key != _wpsc_price 
AND (p.post_title LIKE "%some string to search%" 
     OR p.post_content LIKE "%some string to search%" 
     OR pm.meta_value = "some string to search" 
     OR t.name LIKE "%some string to search%") 
GROUP BY p.ID ORDER By p.ID

#1