Complex Wordpress query using multiple queries

Question

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.

Answer 1

This seems to be a limitation of PHP itself, see this post on the Wordpress forums.

I was faced with the same problem and ended up creating a custom function that does the job, I paste it here in case it is useful for someone. The function is rather simple since it does a few assumptions, but is should be easy to modify it to tailor different needs. Specifically it assumes that:

• All statements are inserts and updates, there is no data to return.
• Statements are separated with an end of line sequence.
• Statements are enclosed within a transaction.

Here is the function:

function execute_multiline_sql($sql) {
    global $wpdb;
    $sqlParts = array_filter(explode("\r\n", $sql));
    foreach($sqlParts as $part) {
        $wpdb->query($part);
        if($wpdb->last_error != '') {
            $error = new WP_Error("dberror", __("Database query error"), $wpdb->last_error);
            $wpdb->query("rollback;");
            return $error;
        }
    }
    return true;
}

Usage example:

$sql = "start transaction;\r\n" .
       "insert into ...;\r\n" .
       "update ...;\r\n" .
       "commit;\r\n"
       ;

$result = execute_multiline_sql($sql);
if(is_wp_error($result)) {
    //Fail!
}

Answer 2

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

1. CREATE TEMPORARY [...] LIMIT 0, 500;
2. 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 );