PHP SQL query returning with random characters

Question

In my SQL database, the row "actor_id" has the value:

00f98c228af5491b83430c97621fe108

However, this returns (when var_dumped):

array(8) {
  ["id"]=>
  string(3) "275"
  ["player_id"]=>
  string(16) "Fp�+5Bԥ��s0��"
  ["reason"]=>
  string(16) "distrurbing chat"
  ["expired"]=>
  string(10) "1425891575"
  ["actor_id"]=>
  string(16) "��"��I�C�b�"
  ["pastActor_id"]=>
  string(16) "�qGݯ�E��O8�'d�"
  ["pastCreated"]=>
  string(10) "1425890675"
  ["created"]=>
  string(10) "1425891613"
}

Notice how the columns have question marks, random characters? How can I get it to return the proper value?

Here is my code:

                $conn = $api->getSQL('minecraft');
                $sql = "SELECT * FROM bm_player_mute_records_prison ORDER BY pastCreated DESC LIMIT 1";
                $result = mysqli_query($conn, $sql);
                while($row = mysqli_fetch_assoc($result)) {

                    var_dump($row);
                }

Thanks.

You might need to change the character set, e.g. dev.mysql.com/doc/refman/5.0/en/charset-syntax.html — Dave
– Dave, Commented Mar 9, 2015 at 15:43
It looks like binary data, are you sure that the data in the database is what you think it is? Where are you pulling actor_id from to get 00f98c228af5491b83430c97621fe108? Is that base64 encoded, but perhaps when you're pulling it you're not base64 decoding it? — Halfstop
– Halfstop, Commented Mar 9, 2015 at 15:50
What's the column type? Is it a binary data type such as VARBINARY? — Álvaro González
– Álvaro González, Commented Mar 9, 2015 at 15:51
To get a better idea of what you're pulling out, you can try HEX(field) for any field to see the actual byte contents. — user2191247
– user2191247, Commented Mar 9, 2015 at 15:52

Álvaro González · Accepted Answer · 2015-03-09 16:06:05Z

1

Its "binary(16)"

Well, that's what binary implies: it isn't text so it won't look nice.

From the MySQL side, you have functions like HEX() to get a printable representation.
In PHP you have bin2hex().

edited Mar 9, 2015 at 16:06

answered Mar 9, 2015 at 15:54

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Toby Mellor Over a year ago

So you're saying I should change: $sql = "SELECT * FROM bm_player_mute_records_prison ORDER BY pastCreated DESC LIMIT 1"; to $sql = "SELECT * FROM bm_player_mute_records_prison ORDER BY HEX(pastCreated) DESC LIMIT 1"; ?

Álvaro González Over a year ago

I don't know what your exact needs are, but you could replace stuff like SELECT player_id with SELECT HEX(player_id) AS player_id (use an alias you don't get a funky column name). I'd probably format it from the PHP side. I'll edit my answer with that.

Naga Penmetsa · Accepted Answer · 2015-03-09 15:52:05Z

1

Try using decrypt functions for the rows

http://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html

answered Mar 9, 2015 at 15:52

Naga Penmetsa

3845 silver badges16 bronze badges

Comments

Rick James · Accepted Answer · 2015-03-10 06:03:14Z

0

I'll vote for it being MD5 encryption of something. An MD5 is 128 bits, the length of that hex. There no way to un-MD5 to find what it came from. (That is a feature of MD5.)

I'll vote that that it is not a UUID. SELECT UuidFromBin(unhex('00f98c228af5491b83430c97621fe108')); --> 8af5491b-8c22-00f9-8343-0c97621fe108, which is not a valid UUID, according to http://en.wikipedia.org/wiki/Universally_unique_identifier . I got the conversion function from my blog on UUIDs: http://mysql.rjweb.org/doc.php/uuid .

answered Mar 10, 2015 at 6:03

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

PHP SQL query returning with random characters

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related