使用多个查询的复杂Wordpress查询 [英] Complex Wordpress query using multiple queries

问题描述

因此，如果我直接在MySQL中运行它，那么我的查询工作正常，但是如果通过Wordpress $ wpdb-> query()运行它，查询将失败.

So I have a query that works just fine if I run it directly in MySQL but fails if I run it through Wordpress $wpdb->query().

如果我在页面上回显$ qry并将其复制并粘贴到phpMyAdmin中，例如，我会得到所有想要的结果.但是在Wordpress中，我得到一个错误.

If I echo the $qry out to the page and copy and paste it in phpMyAdmin for example I get all the results I want. However in Wordpress I get an error.

错误: WordPress数据库错误:[您的SQL语法有误；检查与您的MySQL服务器版本相对应的手册以获取正确的语法以在'SELECT *，ROUND(3963.0 * ACOS(SIN(38.580983 * PI()/180)* SIN(lat * PI()/18)'附近使用21]

The Error: WordPress database 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 'SELECT *, ROUND( 3963.0 * ACOS( SIN( 38.580983*PI()/180 ) * SIN( lat*PI()/18' at line 21]

查询:

CREATE TEMPORARY TABLE tmp_locations_tbl
SELECT post.ID, 
        post.post_name, 
        lat_meta.meta_value AS lat, 
        lng_meta.meta_value AS lng, 
        address_meta.meta_value AS address
FROM wp_posts AS post, 
        wp_postmeta AS lat_meta, 
        wp_postmeta AS lng_meta, 
        wp_postmeta AS address_meta

WHERE post.ID = lat_meta.post_id 
AND post.ID = lat_meta.post_id 
AND post.ID = lng_meta.post_id 
AND lat_meta.meta_key = 'geo_latitude' 
AND lng_meta.meta_key = 'geo_longitude' 
AND address_meta.meta_key = 'address'

LIMIT 0, 5000;

SELECT *, 
ROUND( 3963.0 * ACOS( SIN( 38.580983*PI()/180 ) * SIN( lat*PI()/180 ) + COS( 38.580983*PI()/180 ) * COS( lat*PI()/180 ) * COS( (lng*PI()/180) - (-121.4931*PI()/180) ) ) , 1) 
AS distance
FROM tmp_locations_tbl
HAVING distance < 25
ORDER BY distance ASC
LIMIT 0, 200;

很明显，它不喜欢';' -大概我想.但是，为什么在MySQL而不是Wordpress中可以直接正常运行呢?有趣的是，如果我删除;"通过将两个查询分开的查询，Wordpress无法返回正确的结果，而MySQL通过phpMyAdmin表示这是不正确的查询.

Clearly it doesn't like the ';' - or so I presume. But why does this run fine directly in MySQL and not Wordpress. Interestingly enough if I delete the ';' from the query that separates the two queries Wordpress doesnt return the right results and MySQL, through phpMyAdmin says it is an incorrect query.

任何帮助将不胜感激.

推荐答案

您的代码显示的不是一个SQL语句，而是两个:

Your code shows not one SQL statement, but rather two:

1. CREATE TEMPORARY [...] LIMIT 0, 500;
2. SELECT *, ROUND[...] LIMIT 0, 200;

1. CREATE TEMPORARY [...] LIMIT 0, 500;
2. SELECT *, ROUND[...] LIMIT 0, 200;

据我记得， $wbdb->query() 一次仅接受一个语句(至少 codex文章没有指出它是为批量查询而设计的，我还没有t检查了类代码以进行验证).

As far as I remember, $wbdb->query() accepts only one statement at a time (at least the codex article doesn't point out it is designed for bulk queries, I haven't checked the class code to verify it).

尝试将这些语句放在两个不同的变量中，然后依次运行它们，如下所示:

Try putting those statements in two different variables, then run them one after the other, like this:

$SQL1 = "CREATE TEMPORARY [...] LIMIT 0, 500";
$SQL2 = "SELECT *, ROUND[...] LIMIT 0, 200";

$wpdb->query( $SQL1 );   
$wpdb->query( $SQL2 );

这篇关于使用多个查询的复杂Wordpress查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！