Database SQL Error, Should Work

3 Views

I’m trying to get my posts to order by radius, and so far i’m struggling, I’ve been using the following tut by Steve MArks, http://biostall.com/performing-a-radial-search-with-wp_query-in-wordpress

However the following code chucks up 3 errors.

Read More
function location_posts_where( $where )  
{  
    // Specify the co-ordinates that will form  
    // the centre of our search  
    $lat = '50.12335';  
    $lng = '-1.344453';  

    $radius = 10; // (in miles)  

    // Append our radius calculation to the WHERE  
    $where .= " AND $wpdb->posts.ID IN (SELECT post_id FROM lat_lng_post WHERE 
         ( 3959 * acos( cos( radians(" . $lat . ") ) 
                        * cos( radians( lat ) ) 
                        * cos( radians( lng ) 
                        - radians(" . $lng . ") ) 
                        + sin( radians(" . $lat . ") ) 
                        * sin( radians( lat ) ) ) ) <= " . $radius . ")";  

    // Return the updated WHERE part of the query  
    return $where;  
}

The first two errors are these: 

Notice: Undefined variable: wpdb in /home/beafweb/public_html/wp-content/themes/BEAF/functions.php on line 106

Notice: Trying to get property of non-object in /home/beafweb/public_html/wp-content/themes/BEAF/functions.php on line 106

Which I assume are to do with this part of the string AND $wpdb->posts.ID as it’s on line 106.

The last error that is chucked up in the debug log is: 

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 'IN (SELECT `post_id` FROM `lat_lng_post` WHERE ( 3959 * acos( cos( rad' at line 1]
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'festival-event' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND .ID IN (SELECT `post_id` FROM `lat_lng_post` WHERE ( 3959 * acos( cos( radians(50.12335) ) * cos( radians( 'lat' ) ) * cos( radians( 'lng' ) - radians(-1.344453) ) + sin( radians(50.12335) ) * sin( radians( 'lat' ) ) ) ) <= 10) ORDER BY wp_posts.post_date DESC LIMIT 0, 10

Is this linked to the first errors? seems to me that it fails to connect to the database.

I’ve tried backticking db parts of the string like previous threads say to, but that doesn’t work either.

My knowledge of SQL isn’t high but it is on my to do list, any help would be really appreciated.

Post Views: 3

Related posts

2 comments

  1. It’s definitely connecting to the database, otherwise you wouldn’t be erroring like that.

    Your DB is trying to parse your sql statement, but your database doesn’t know what $wpdb->posts.id is since that’s a PHP variable and your DB only speaks SQL.

    Perhaps you should concatenate that thing into your sql statement like $where .= " AND {$wpdb->posts}.ID IN (SELECT ...

  2. You have two errors, what causes the third.

    In your functions.php 106, you want to use the $wpdb variable, what is not in the scope of function. In the begining of your function, you need to add global $wpdb;

    And move it outside from your string 

    $where .= " AND " . $wpdb->post->ID . " IN (SELECT post_id FROM lat_lng_post WHERE 
     ( 3959 * acos( cos( radians(" . $lat . ") ) 
                    * cos( radians( lat ) ) 
                    * cos( radians( lng ) 
                    - radians(" . $lng . ") ) 
                    + sin( radians(" . $lat . ") ) 
                    * sin( radians( lat ) ) ) ) <= " . $radius . ")";

    I am not sure, what’s in $wpdb try to dump it.

Comments are closed.