How to Store and Retrieve Clean Phone Numbers in Laravel Eloquent

When working with phone numbers in a Laravel application, it's essential to ensure they are stored in a clean and consistent format in the database. However, when retrieving the data, we often need to format it correctly for display. In this article, we'll explore how to clean up phone numbers in MySQL and then use an Eloquent accessor to modify the returned data dynamically.

Step 1: Cleaning Phone Number Data in MySQL

Imagine you have a user_details table with the following columns:

• phone_country_code (e.g., +44 for the UK, +1 for the USA)
• phone_number (e.g., 555-123-4567)

Sometimes, users enter phone numbers with spaces, dashes, or special characters. To clean up this data, we can run the following SQL query:

UPDATE user_details
SET
    phone_country_code = REGEXP_REPLACE(phone_country_code, '[^0-9]', ''),
    phone_number = REGEXP_REPLACE(phone_number, '[^0-9]', '');

Why do this?

• Ensures only numeric values are stored.
• Prevents formatting inconsistencies.
• Makes phone numbers easier to process and search in the database.

After running this query, phone numbers previously stored as:

+44 123-456-7890

Would now be stored as:

44 1234567890

Step 2: Automatically Formatting Country Codes in Eloquent

Now that our database stores only numbers, we need a way to add back the + sign when retrieving the phone_country_code. Instead of modifying the data directly in the database, we can use an Eloquent accessor.

Modify UserDetail.php Model

use Illuminate\Database\Eloquent\Model;

class UserDetail extends Model
{
    protected $table = 'user_details';
    protected $fillable = ['phone_country_code', 'phone_number'];

    // Accessor to add the + sign when retrieving phone_country_code
    public function getPhoneCountryCodeAttribute($value)
    {
        return $value ? '+' . $value : null;
    }
}

How It Works

• The getPhoneCountryCodeAttribute automatically appends + to the stored numeric country code.
• If the value is NULL, it remains NULL to avoid incorrect formatting.
• This ensures the stored data stays clean, but the retrieved data is properly formatted.

Now, when we retrieve a record:

$userDetail = UserDetail::find(1);
echo $userDetail->phone_country_code; // Output: "+44"

Other Considerations

1. Handling Empty Values Gracefully

If a user has no country code, the accessor should not append +. The current implementation handles this correctly with:

return $value ? '+' . $value : null;

However, if you want to default to a specific country code (e.g., +1 for the USA), you can modify it:

return $value ? '+' . $value : '+1';

2. Validating Phone Numbers Before Saving

To ensure clean input data, consider using Laravel's validation:

$request->validate([
    'phone_country_code' => 'required|numeric',
    'phone_number' => 'required|numeric',
]);

This prevents users from saving invalid characters into the database in the first place.

3. Formatting Phone Numbers on Output

Currently, we're only formatting phone_country_code. If you also want to format phone_number, consider using an accessor for that too:

public function getFormattedPhoneNumberAttribute()
{
    return preg_replace("/(\d{3})(\d{3})(\d{4})/", "$1-$2-$3", $this->phone_number);
}

This would return "123-456-7890" instead of "1234567890".

Finally

By storing clean, numeric-only phone numbers in the database while using Eloquent accessors to dynamically format data when needed, you get the best of both worlds:

• Efficient and standardized storage.
• User-friendly formatted output.
• Easier validation and processing.

Support Us