Updating wppl_friends_locator table

[vcpros]

I’m not sure where to post this, but I am trying to update the lat & long values when a user changes their zipcode. I am using buddypress, and I have managed to update the zipcode in the wppl_friends_locator table to match the one in their profile.

The profile zipcode has a limited number of choices, so I am trying the set the lat/long to the zipcode coordinates (whatever google maps returns) using a switch statement.

//get zip code from xprofile $sql = "SELECT value FROM wp_bp_xprofile_data WHERE field_id='5' AND user_id='" . bp_displayed_user_id() . "';"; $r_query = mysqli_query($conn, $sql); $x_sql = mysqli_fetch_array($r_query); //update zip code on geo my wp table switch ( $x_sql[0] ) { case '10026': $update_sql = "UPDATE wppl_friends_locator SET zipcode='10026', lat=40.802381, long=-73.952681 WHERE member_id=" . bp_displayed_user_id() . ";"; break; case '10027': $update_sql = "UPDATE wppl_friends_locator SET zipcode='10027', lat=40.811407, long=-73.953060 WHERE member_id=" . bp_displayed_user_id() . ";"; break; default: $update_sql = "UPDATE wppl_friends_locator SET zipcode='10001' WHERE member_id=" . bp_displayed_user_id() . ";"; break; } $u_query = mysqli_query($conn, $update_sql); $conn->close();

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

//get zip code from xprofile $sql = "SELECT value FROM wp_bp_xprofile_data WHERE field_id='5' AND user_id='" . bp_displayed_user_id() . "';"; $r_query = mysqli_query($conn, $sql); $x_sql = mysqli_fetch_array($r_query);   //update zip code on geo my wp table switch ( $x_sql[0] ) { case '10026': $update_sql = "UPDATE wppl_friends_locator SET zipcode='10026', lat=40.802381, long=-73.952681 WHERE member_id=" . bp_displayed_user_id() . ";"; break;   case '10027': $update_sql = "UPDATE wppl_friends_locator SET zipcode='10027', lat=40.811407, long=-73.953060 WHERE member_id=" . bp_displayed_user_id() . ";"; break;   default: $update_sql = "UPDATE wppl_friends_locator SET zipcode='10001' WHERE member_id=" . bp_displayed_user_id() . ";"; break;   }   $u_query = mysqli_query($conn, $update_sql); $conn->close();

I’ve narrowed it down that the lat/long isn’t being correctly input. The lat/long columns are float(10,6), and I’m not sure how to change those values to something else.

Edit: added pastebin of code.

[vcpros]

Does lat/lang column have to be float(10,6)? Can I change it to something like decimal, double, or varchar without breaking the plugin? Meaning does any functionality depending on those columns being floats?

[Eyal Fitoussi]

Hello Vcpros,

Is there a specific reason why you are using mysqli_query instead of using the native $wpdb for the databased queries ( see codex ) ? Also, you can retrieve the xprofile field data using BuddyPress function instead of direct SQL query.

I believe that the script below should work for you. I didn’t test it but you might want to give it a try. Unless there is a reason that I don’t see why you’d use the direct MYSQL queries.

// get the user ID $user_id = bp_displayed_user_id(); // get the zipcode value $zipcode = xprofile_get_field_data( 5, $user_id ); global $wpdb; switch ( $zipcode ) { case '10026': $wpdb->update( 'wppl_friends_locator', array( 'zipcode' => '10026', 'lat' => '40.802381', 'long' => '-73.952681' ), array( 'member_id' => $user_id ), array( '%s', '%s', '%s' ), array( '%d' ) ); break; case '10027': $wpdb->update( 'wppl_friends_locator', array( 'zipcode' => '10027', 'lat' => '40.811407', 'long' => '-73.953060' ), array( 'member_id' => $user_id ), array( '%s', '%s', '%s' ), array( '%d' ) ); break; default: $wpdb->update( 'wppl_friends_locator', array( 'zipcode' => '10001' ), array( 'member_id' => $user_id ), array( '%s' ), array( '%d' ) ); break; }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57

// get the user ID $user_id = bp_displayed_user_id();   // get the zipcode value $zipcode = xprofile_get_field_data( 5, $user_id );   global $wpdb;   switch ( $zipcode ) {          case '10026':      $wpdb->update( 'wppl_friends_locator',     array(      'zipcode' => '10026',      'lat'   => '40.802381',      'long'   => '-73.952681'     ),     array( 'member_id' => $user_id ),     array(      '%s',      '%s',      '%s'     ), array( '%d' ) ); break;     case '10027':      $wpdb->update( 'wppl_friends_locator',     array(      'zipcode' => '10027',      'lat'   => '40.811407',      'long'   => '-73.953060'     ),     array( 'member_id' => $user_id ),     array(      '%s',      '%s',      '%s'     ), array( '%d' ) ); break;   default: $wpdb->update( 'wppl_friends_locator',     array(      'zipcode' => '10001'     ),     array( 'member_id' => $user_id ),     array(      '%s'     ), array( '%d' ) ); break; }

And you should not modify the database structure as it will most likely create issues with the plugin ( and float is Google’s recommended column type ) . The coordinates that you are using should be saved in database.

I hope that helps.

[vcpros]

Hi Eyal, that worked perfectly. As to why I wasn’t using it, I simply didn’t know about it; I am familiar with myslqi_query so that’s what I went with. Thank you so much for the help, $wpdb is something to study for the future.

[Author]

Posts