Complex Wordpress query using multiple queries

mysql, php, wordpress

Solution

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

- `CREATE TEMPORARY [...] LIMIT 0, 500;`

- `SELECT *, ROUND[...] LIMIT 0, 200;`

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 );

Problem

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(). 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. 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] The Query: ``` 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; ``` 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. Any help would be appreciated.

Original source