db_result D6 query conversion to db_select D7

0
votes

I am trying to convert this D6 code with query to Drupal 7

   foreach ($order->products as $product) {
      if (db_result(db_query("SELECT nid FROM {the_table} WHERE fid = 'string' AND nid = %d", $product->nid))) {
       $nid[] = $product->nid;
       }
     }

I changed it to for the query:

    if (db_result(db_query('SELECT nid FROM {the_table} WHERE fid = 'string' AND nid = :nid', array(':nid' => $product->nid)))) {

AND then to as a Dynamic query it came out written as this I thought

   foreach ($order->products as $product) {
     $query = db_select('{the_table}', '');
     $query->fields('nid', array(''));
     $query->condition('fid', 'string');
     $query->condition('nid', ':nid');
     $query->execute();             
     $result = $query->fetchAssoc();
      foreach ($result as $record) {
      $product->nid;
      $nid [] = $product->nid;
        }
     }

I also tried a "->fetchfield();" statement in there instead - same error I also tried :string instead of 'string'

It throws this error

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 'AS FROM the_table the_table WHERE (fid = 'string') AND' at line 1: SELECT nid. AS FROM {the_table} the_table WHERE (fid = :db_condition_placeholder_0) AND (nid = :db_condition_placeholder_1) ; Array ( [:db_condition_placeholder_0] => string [:db_condition_placeholder_1] => :nid ) in process() (line 450 of /. . . the.module).

ALSO I do not know why the error shows two of "the_table" - one behind another - so is it possible the otrignal D6 query was bad ??

Does anyone know what I have done wrong ??

2
phpdrupaldrupal-7

2 Answers

0
votes

I did not pay close enough attention that the brackets are not proper syntax in the new format of query. It works - and i see the other places I went wrong after reading the dynamic query page again at https://www.drupal.org/dynamic-queries

 <?php
  $query = db_select('the_table', 'the_table');
  $query->fields('the_table', array('nid'));
  $query->condition('the_table.fid', 'string');
  $query->condition('the_table.nid', $product->nid);
  $query->execute();
  ?>
0
votes

You should fix the following errors:

// error
$query = db_select('{the_table}', '');
// fixed
$query = db_select('TABLE_NAME', 'TABLE_ALIAS'); // remove the curly braces. The second argument is for you to choose an alias for your table.

// error
$query->fields('nid', array(''));
// fixed
$query->fields('nid', array('id', 'name')); // Choose the fields you want to return from the query.

// error
$query->condition('nid', ':nid');
// fixed
$query->condition('nid', $someVariable, '='); // no need to use ':nid'