Complex query failing with $wpdb->query but work directly in phpMyAdmin
-
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;
I presume it has to do with the fact that I am actually trying to run two queries and it doesn’t like the ‘;’ separating the two. But if I remove it the WP query fails and if I try it in phpMyAdmin it says I have an sql error on that line…
Confused on what to do
- The topic ‘Complex query failing with $wpdb->query but work directly in phpMyAdmin’ is closed to new replies.