The xf_attachment table and content_type question

[AndyB]

Hello,

In my Insert Attachment All add-on located here:

https://xenforo.com/community/resources/insert-attachment-all.3119/

I have a query that is searching the xf_attachment.content_type field for 'post'. This has worked perfectly for many forums. But today I ran across a situation where it's not working. Normally I always saw 'post' in this field, example:



But on the forum I'm troubleshooting I see this:



Notice the content_type has a hex value.

Can someone explain why this is, the two values tranlated are:

post

and

xengallery_media

Has the XenForo Media Gallery changed the values to hex?

My main question is how do I set up a query to search for '706f7374' ?

This is my query:

        // get posts
        $posts = $db->fetchAll("
        SELECT DISTINCT(xf_post.post_id), xf_post.message
        FROM xf_post
        INNER JOIN xf_attachment ON xf_attachment.content_id = xf_post.post_id
        INNER JOIN xf_attachment_data ON xf_attachment_data.data_id = xf_attachment.data_id
        WHERE xf_attachment.content_type = ?
        AND xf_attachment_data.width > ?
        AND xf_post.message NOT LIKE CONCAT('%[ATTACH=full]', xf_attachment.attachment_id , '[/ATTACH]%')
        ORDER BY xf_post.post_id ASC
        LIMIT ?
        ", array('post', 0, $limit));

Thank you.

 

[Chris D]

Can someone explain why this is

Some PhpMyAdmin versions display the value of binary fields as hex, rather than the textual representation. I believe the brand new PhpMyAdmin builds have either fixed this, or at least provided an option to automatically display it in the correct format.

Has the XenForo Media Gallery changed the values to hex?

No... the field type is "varbinary" and pretty much always has been. You can read more about that, here: http://dev.mysql.com/doc/refman/5.7/en/binary-varbinary.html

My main question is how do i set up a query to search for 'post' or '706f7374' ?

You don't need to. MySQL automatically handles casting this appropriately. Directly running the query in PhpMyAdmin:

SELECT * FROM xf_attachment WHERE content_type = 'post'

Should yield the results you would expect it to.

 

[AndyB]

Thank you, Chris.