SImple Query Drupal 7

Question

I am trying to create a simple static query just to see if the data exsits but I keep getting an error:

$result = db_query('SELECT * FROM field_data_field_product_id WHERE field_product_id_value = "mnbvcxz"');

if ($result->rowCount() > 0) {
  Do something
}

I get this error:

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'mnbvcxz' in 'where clause': SELECT * FROM field_data_field_product_id WHERE field_product_id_value = "mnbvcxz"; Array ( ) in validate_product_id_form_alter() (line 9 of sites/all/modules/validate_product_id/validate_product_id.module).

Answer 1

I'm not sure exactly why but it's got something to do with the double quotes in the query. Replacing them with single quotes makes queries of that nature run just fine.

You should really be using parameters rather than putting values straight into the query string though. db_query() accepts an array as its second argument to perform parameter replacement, and doing things this way means you don't even need to worry about quotes; PDO will take care of that for you.

Plus if you extend your query to accept that argument from a variable rather than a static string, you don't need to worry about SQL injection (again PDO will take care of it).

Your query would look like this:

$args = array(':product_id' => 'mnbvcxz');
$result = db_query('SELECT * FROM field_data_field_product_id WHERE field_product_id_value = :product_id', $args);

To offer another alternative, the preferred way of querying by field data is to use the EntityFieldQuery class. There are several benefits, possibly the most important being that it's storage engine agnostic - if you decide to move your field data to (say) MongoDB, the query will still work.

e.g.

$query = new EntityFieldQuery();
$query->entityCondition('entity_type', 'node')
  ->fieldCondition('field_product_id', 'value', 'mnbvcxz');

$results = $query->execute();
if (!empty($results['node'])) {
  $matching_nodes = node_load_multiple(array_keys($results['node']));

  // Do something with those nodes
}