Complex query failing with $wpdb->query but work directly in phpMyAdmin

  • Resolved thorakmedichi

    (@thorakmedichi)


    10 years, 8 months ago

    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

Viewing 6 replies - 1 through 6 (of 6 total)
  • Mike

    (@mike_vl)

    10 years, 8 months ago

    I stumbled upon this topic where there were also multiple queries being executed. (it doesn’t offer a solution but maybe helps explain why you’re running into problems)

    Link to query fails when multiple UPDATE statements are used

    Mike

    (@mike_vl)

    10 years, 8 months ago

    I don’t know if this wil help you along but I found a post use the union operator to combine the result sets from multiple queries into a single result set. that might point you in the right direction.

    Thread Starter thorakmedichi

    (@thorakmedichi)

    10 years, 8 months ago

    Hey Mike thanks for the replies. The first link just says that its an issue with PHP and leads me nowhere per se – but it does mention I just may not be able to do this at all.

    The second link using a UNION was a great lead – but doesn’t work in my case because it is not multiple SELECT statements. I am trying to create a temp table and then select more advanced information from it.

    It looks like I need to use something like the UNION to rebuild this query but not sure what. Any SQL masters out there??

    None the less both great ideas. Cheers

    Mike

    (@mike_vl)

    10 years, 8 months ago

    last (desperate) attempt 😉

    Would a database view offer a way forward?

    Thread Starter thorakmedichi

    (@thorakmedichi)

    10 years, 8 months ago

    So it turns out that even though I am creating a TEMPORARY table wordpress will store those results for a period of time. Therefore doing something as simple as this:

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

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

    Will work.

    Mike

    (@mike_vl)

    10 years, 8 months ago

    Good to hear! Will you set the topic to resolved?

Viewing 6 replies - 1 through 6 (of 6 total)
  • The topic ‘Complex query failing with $wpdb->query but work directly in phpMyAdmin’ is closed to new replies.

Tags