2

Does Drupal 7's DB API support SQL's CAST function? I have a varchar with numbers in it that I'm trying to convert to integers for proper sorting. Here's my statement:

$images = db_query('SELECT filename, caption FROM {iha_captions}
WHERE uid = :uid ORDER BY CAST(weight AS INT) ASC', array(':uid' => $userid));

And here is the error I get:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INT) ASC' at line 1: SELECT filename, caption FROM {iha_captions} WHERE uid = :uid ORDER BY CAST(weight AS INT) ASC; Array ( [:uid] => 2814 ) in iha_search_profile()

It then causes a cascade of SQL failures in Drupal's session and cache queries. Is CAST supported, or am I doing it wrong?

2

It's not a Drupal error; it comes from the MySQL engine about CAST using:

The type can be one of the following values:
BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL[(M[,D])]
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]

You cannot use INT; it should be SIGNED, UNSIGNED or DECIMAL.

2

It's not a Drupal error it's a MySQL error. CAST() won't accept INT as a valid type but does give you a choice between SIGNED and UNSIGNED to cast to an integer type.

The following query runs fine for me in Drupal:

SELECT * FROM node ORDER BY CAST(nid AS UNSIGNED)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.