What are the differences between using md5() function in PHP and MySQL for password hashing and how can compatibility issues be addressed?

When using the md5() function in PHP and MySQL for password hashing, the main difference is that PHP's md5() function generates a 32-character hexadecimal number, while MySQL's md5() function generates a 16-character hexadecimal number. To address compatibility issues, you can either use PHP's md5() function for both hashing and verification, or truncate the PHP-generated hash to 16 characters before storing it in MySQL for verification.

// Hashing the password using PHP's md5() function
$password = 'password123';
$hashed_password = md5($password);

// Truncating the hash to 16 characters for MySQL compatibility
$truncated_hash = substr($hashed_password, 0, 16);

// Storing the truncated hash in MySQL
// Verify the password by comparing the truncated hash with the MySQL-generated md5 hash
$query = "SELECT * FROM users WHERE username = 'example' AND password = MD5('$truncated_hash')";