Complex WordPress query using multiple queries

2 Views

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.

Read More

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.

Post Views: 2

Related posts

Leave a Reply

2 comments

  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("rn", $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;rn" .
       "insert into ...;rn" .
       "update ...;rn" .
       "commit;rn"
       ;

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

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