I can't seem to query on a field of type "numeric".

From hook_schema:

'id' => array(
  'description' => "ID",
  'type' => 'numeric',
  'unsigned' => TRUE,
  'precision' => 20,
  'scale' => 0,
  'not null' => TRUE,
  'default' => 0,

if I do something simple like the following code, I don't get anything.

$query = db_select('tablename', 't')
  ->fields('t', array('nid'))
  ->condition('id', $id, '=');

However, if I execute the following query on PHPMyAdmin, I get a row.

SELECT * FROM `tablename` WHERE id = 195755377227337728;

I did try to type cast the id with (int) $id, but that didn't work either.

Do you have any thought?

  • The size of your id is mind-boggling. Do you really have nearly 2 hundred quadrillion records? – Clive Apr 27 '12 at 16:58

Two things that might be causing the problem:

  1. You need to execute the query: $result = $query->execute();
  2. The id you gave is HUGE, and I don't know if it would ever work. Drupal comes with the nid column set to a length of 10. Even if you've accounted for that, the max INT column size in MySQL is 4294967295 and your ID is 195755377227337728. Theoretically you could use BIG INT instead of INT. See: MySQL column type sizes

Also, try this Static Query instead of db_select():

$result = db_query("SELECT nid FROM {table} WHERE nid = :nid", array(':nid' => $nid,));
| improve this answer | |
  • Ignore my answer (I've deleted it), my maths was off :) That ID is perfectly acceptable for PHP and MySQL as an integer type (assuming a 64bit system). Thanks @kiamlaluno for the sanity check. My money's on the problem being that the query hasn't been executed. – Clive Apr 27 '12 at 17:24
  • Well, I ended up having to do this (didn't try the Static Query option). $rec = db_query("SELECT nid FROM {table} WHERE id = $id")->fetchAssoc(); – user6614 May 12 '12 at 5:03

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.