0

I am trying to insert data which contains a POINT field. I am using the $wpdb->insert method but when generating the SQL it is wrapping the POINT in quotes.

global $wpdb;
$table = $wpdb->base_prefix . 'points';

$Data = array(
    'Title' => $this->Title,
    'gPoint' => sprintf("POINT(%s,%s)",$this->Lng, $this->Lat),
    'Lat' => $this->Lat,
    'Lng' => $this->Lng
);

if (!$wpdb->insert($table,$Data))
    throw new Exception($wpdb->last_error);

This is the SQL generated. If I take the quotes from around the POINT field, the query will run.

INSERT INTO `wp_points` (`Title`, `gPoint`, `Lat`, `Lng`) VALUES ('My Marker', 'POINT(0.2566251,51.0581515)', '51.0581515', '0.2566251')

Is there a way round this?

0

If you use the %s in your sprintf statement, $wpdb->insert() will automatically apply single-quotes.

You want to use %f for float, or %d for int/digit.

  • Thanks, but by the time it gets to the $wpdb->insert, the sprintf is gone and just left with this : POINT(0.2566251,51.0581515) . Then the insert wraps the whole lot in quotes which is the problem, as the database tries to insert a string instead of a POINT. – StripyTiger Aug 30 at 8:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.