PHP with SQL - run php function on a field in WHERE clause before it gets compared with LIKE

Question

I use Wordpress/Woocommerce with MySQL. I need to find all orders with the same billing phone as a given one. The basic SQL is below. The problem is that when the phone numbers were stored in the DB they were not canonized and so there are all possible forms like 0YZ-abcd, 0YZabcd,+KMYZabcs, etc I already have a PHP function to canonize any phone number. My question is how to apply that function to every row's _billing_phone column which is being considered in the WHERE clause.

The original SQL is

 $canonized_phone_to_compare_to = canonize_phone_number($original_phone_number);

 $results = $wpdb->get_col( "
    SELECT p.ID FROM {$wpdb->prefix}posts AS p
    INNER JOIN {$wpdb->prefix}postmeta AS pm ON p.ID = pm.post_id
    WHERE pm.meta_key = '_billing_phone' 
    AND pm.meta_value = '" . $canonized_phone_to_compare_to . "'      
" );

What I need is to find a way to apply canonize_phone_number() to pm.meta_value just before it gets compared. In other words, each time the SQL 'checks' if the pm.meta_value equals to $canonized_phone_to_compare_to I want the SQL to apply my function (or a stored procedure maybe?) to pm.meta_value and re-format that field

Or any other way to tackle this?

Answer 1

You can't run a PHP function in a running SQL-query. I think the best would be if you stored them in a universal format.

Depending on how you're data is stored you could use replace to get rid of white-spaces and minus sign. Then use right function to get the last part from your phone numbers. I don't recommend this because you won't be able to index and the query could become slow. (It's not the case for newer versions of MySQL.)

For example, in Sweden, all mobile numbers look like this 07z-xxx xx xx, 00467z-xxx xx xx, +467z-xxxxxxx, +46(0)7z-xx xx xxx. In this case, you could replace all white-spaces and minus sign with nothing, then match on the last 7 digits.

All countries have their numbering plan so you've to check the documentation for your data.

Answer 2

i'm not sure about SQL but you can always do it in PHP . so try getting the columns without the last part .

surely there is better way to do it but I think this will work .

$canonized_phone_to_compare_to = "0xxxxxxxxxx";
$results = $wpdb->get_col("
    SELECT p.ID FROM {$wpdb->prefix}posts AS p
    INNER JOIN {$wpdb->prefix}postmeta AS pm ON p.ID = pm.post_id
    WHERE pm.meta_key = '_billing_phone'   
 ");
foreach ($results as $i => $v) {
    $results[$i]['pm.meta_value'] = canonize_phone_number($results[$i]['pm.meta_value']);
    if ($canonized_phone_to_compare_to == $results[$i]['pm.meta_value']) {
        $newResults[] = $results[$i];
    }
}