27. 11. 2019

Convert MYSQL HEX to string with PHP

Hexadecimal literal values are written using X'string' or 0xstring notation, where string contains hexadecimal digits (0..9, A..F).

SELECT HEX('EXAMPLE'), X'4558414D504C45';
+----------------+-------------------+
| HEX('EXAMPLE') | X'4558414D504C45' |
+----------------+-------------------+
| 4558414D504C45 | EXAMPLE           |
+----------------+-------------------+

You can also transform this value HEX value also with PHP instead of SELECT *, HEX(column) as column_name ... SQL.

<?php
function hexToString($hex) {
    $string = '';
    for ($i = 0; $i < strlen($hex); $i++) {
        $string .= dechex(ord($hex[$i]));
    }
    return $string;
}

Inserting string to the table will be easy just "INSERT INTO table (column) VALUES HEX(?)" or with Hexadecimal Literal string starting with 0x or X'string'.

#PHP #mysql