-2

I'm trying to query the database inside my module, but I get this error.

Drupal\Core\Database\DatabaseExceptionWrapper: 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 'FROM "commerce_product_variation" "cpv" WHERE ("variation_id" NOT IN (NULL)) AND' at line 1: SELECT FROM "commerce_product_variation" "cpv" WHERE ("variation_id" NOT IN (:db_condition_placeholder_0)) AND ("status" = :db_condition_placeholder_1); Array ( [:db_condition_placeholder_0] => [:db_condition_placeholder_1] => 1 ) in deactivateProductVariantsNotInList() (regel 816 van /data/sites/web/pcreathingbe/subsites/staging.protectasolutions.be/web/modules/custom/importproducts/importproducts.module).

The code I am using is the following.

function deactivateProductVariantsNotInList($variant_type) {
  global $milieu_data;

  if ($variant_type == 'milieutechniek') {
    $database = \Drupal::service('database');
    $query = $database->select('commerce_product_variation', 'cpv');
    $query->condition('variation_id', $milieu_data, 'NOT IN')
      ->condition('status', 1)
      ->execute();
  }
}
2

2 Answers 2

2

If you take a closer look at your error message, your query probably fails because your $milieu_data variable is null.

FROM "commerce_product_variation" "cpv" WHERE ("variation_id" NOT IN (NULL)) AND where (NULL) is your $milieu_data variable value.

In a NOT IN condition, it should be formatted like NOT IN (value1, value2, ...). If $milieu_data is allowed to be null, then you should add a condition to have your query condition modified to $query->isNotNull("variation_id");

1
  • Yes, this is what happened.
    – apaderno
    Nov 5, 2022 at 9:37
-2

I cannot speak to misterdidi's answer it may apply also . . . but

Your error message points specifically to a syntax error. As I said in the comments it appears to be a " ; " - the semicolon at

$query = $database->select('commerce_product_variation', 'cpv');

which should be

$query = $database->select('commerce_product_variation', 'cpv')

A PHP code checker will report no issue with either but Drupal is sensitive to it

For instance in Drupal 7 this code below

   $weight = db_select('system', 's')
     ->fields('s', array('weight'))
     ->condition('name', 'uc_quote', '=')
     ->execute()
     ->fetchField();
   db_update('system')
     ->fields(array('weight' => $weight + 1))
     ->condition('name', 'uc_addresses', '=')
     ->execute();

ONLY has the semicolon at the end or execute and in Drupal 8/9 Drupalmoduleupgrader changed it to

   $weight = \Drupal::database()->select('system', 's')
     ->fields('s', array('weight'))
     ->condition('name', 'uc_quote', '=')
     ->execute()
     ->fetchField();
   // TODO: Drupal Rector Notice: Please delete the following comment after you've made any necessary changes.
   // You will need to use 
  \Drupal\core\Database\Database::getConnection()` if you 
 do not yet have access to the container here.
   \Drupal::database()->update('system')
     ->fields(array('weight' => $weight + 1))
     ->condition('name', 'uc_addresses', '=')
     ->execute();

While there is no "system" table any longer in Drupal 8 / 9 the purpose of DrupalModuleUpgader and Drupal Rector is to correct syntax and apply coding standards

You will notice whether it is $weight or your $query variable there is no semicolon behind the statement.

The semicolon needs to be removed

9
  • No, $query = $database->select('commerce_product_variation', 'cpv'); is correct, given that the next lines starts with $query->condition('variation_id', $milieu_data, 'NOT IN').
    – apaderno
    Nov 5, 2022 at 8:45
  • @apaderno you are WRONG. See drupal.org/docs/8/api/database-api/dynamic-queries/… at "The dynamic query would use the following code." just above "#Executing the query" NEVER when there are array conditions below the the database select or other command is there a " ;" semicolon in the format the Original Poster has presented. The query does not "fail" like misterdidi says, the rror message says there is a syntax issue - and that semicolon like that in Drupal will throw that message Nov 6, 2022 at 10:39
  • This is plain PHP syntax: $query = $database->select('commerce_product_variation', 'cpv'); $query->condition('variation_id', $milieu_data, 'NOT IN'); is correct; $query = $database->select('commerce_product_variation', 'cpv') $query->condition('variation_id', $milieu_data, 'NOT IN'); throws a parsing/syntax error.
    – apaderno
    Nov 6, 2022 at 10:43
  • The question is edited, but the original had //'s and the error. The way it shows now is correct - and someone edited it to change it. It is NOT the same questiion I saw originally Nov 6, 2022 at 10:51
  • I just removed the commented lines, but those don't change the code, since PHP ignores the comments between code lines.
    – apaderno
    Nov 6, 2022 at 15:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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