-1

I'm writing a WordPress plugin with a users listing. To build this users listing, I use the get_users() WordPress function which work perfectly. I built a form to select some options to find users from their age and from the coordinates of their cities. Clearly, I need to check ages of users, from their birthdates) and position of users (from latitude and longitude of the city where they live) to determinate if they match for the current request. So, I use meta_query to custom my get_users() function to match some parameters, but for the 2 mentioned parameters (age and distance), I want/need to use 2 SQL procedures :

  • SQL procedure to get age from birthdate:

    DELIMITER $$
    CREATE FUNCTION `F_AGE`(in_dob datetime) RETURNS int(11)
    BEGIN
    DECLARE l_age INT;
    IF DATE_FORMAT(NOW(  ),'00-%m-%d') >= DATE_FORMAT(in_dob,'00-%m-%d') THEN
    SET l_age=DATE_FORMAT(NOW(  ),'%Y')-DATE_FORMAT(in_dob,'%Y');
    ELSE
    SET l_age=DATE_FORMAT(NOW(  ),'%Y')-DATE_FORMAT(in_dob,'%Y')-1;
    END IF;
    RETURN(l_age);
    END$$
    DELIMITER ;
    
  • SQL procedure to get distance from 2 latitude/longitude pairs:

    DROP FUNCTION IF EXISTS get_distance_metres;
    DELIMITER |
    CREATE FUNCTION get_distance_metres (lat1 DOUBLE, lng1 DOUBLE, lat2 DOUBLE, lng2 DOUBLE) RETURNS DOUBLE
    BEGIN
    DECLARE rlo1 DOUBLE;
    DECLARE rla1 DOUBLE;
    DECLARE rlo2 DOUBLE;
    DECLARE rla2 DOUBLE;
    DECLARE dlo DOUBLE;
    DECLARE dla DOUBLE;
    DECLARE a DOUBLE;
    
    SET rlo1 = RADIANS(lng1);
    SET rla1 = RADIANS(lat1);
    SET rlo2 = RADIANS(lng2);
    SET rla2 = RADIANS(lat2);
    SET dlo = (rlo2 - rlo1) / 2;
    SET dla = (rla2 - rla1) / 2;
    SET a = SIN(dla) * SIN(dla) + COS(rla1) * COS(rla2) * SIN(dlo) * SIN(dlo);
    RETURN (6378137 * 2 * ATAN2(SQRT(a), SQRT(1 - a)));
    END|
    DELIMITER ;
    

The 2 SQL Procedures work fine (I already use it for older projects, but not in WordPress). But I don't know if I can (how I can) use it with meta_query... Is it a way to do this or must I write a pure SQL request without use get_users() function?

For the first check, thus the users age check, I can solve easily the problem by simply adding the age of users in an user_meta in addition of the birthdates, so I could only check if an age is higher or lower (or equal) to the entered search age. But I'd prefer to check the age with the birthdate (so with the SQL procedure) to avoid to add an "useless" user_meta, and mostly to avoid to have to update the ages of users every years.

For the second check, the distance calculation between 2 latitude/longitude pairs, it's a little bit more difficult: I store the location's user data in an user_meta as an array, like this (I only paste the part of data which is targeting my problem, I store others data in the same user_meta, always in arrays (arrays in a parent array)):

"zipcode_search";a:7:{s:10:"place name";s:7:"Lyon 01";s:9:"longitude";s:6:"4.8345";s:5:"state";s:12:"Rhône-Alpes";s:18:"state abbreviation";s:2:"B9";s:8:"latitude";s:7:"45.7676";s:7:"country";s:2:"FR";s:7:"zipcode";s:5:"69001"

I think I probably could extract latitude and longitude with a specific SQL request, or a REGEX, but I don't know how, and mostly I don't know how add a SQL procedure in meta_query array...

If someone has already had the same problem to solve, I'll be happy to have advices on how to do it ;).

Thanks in advance for your replies!

BBFUNK01

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.