1

I'm doing an exercise where I have a table called visit_counter with fields uid (primary key that stores the user id) and user_visit_count (sum of user's page visits). The purpose of the table is to store the user id and to count the times the user visits the page that loads the controller.

My problem is that my if condition is not working well. I'm trying to insert the row with the uid (primary key, user id), however, I need to execute the insert only after I have validated that the uid doesn't exist.

The following graphic is what I expect. To be able to check the stored values in the visit_counter table (sorry if the image is too big, I don't know how to resize it). In this exercise I'm forced to use insert or update. PHP My Admin: table visit_counter

Mymodule.install

/**
 * Implements hook_schema().
 */
function mymodule_schema() {
  $schema['visit_counter'] = [
    'description' => 'Descripción counter',
    'fields' => [
      'uid' => [
        'type' => 'int',
        'not null' => TRUE,
        'default' => 0
      ],
      'user_visit_count' => [
        'type' => 'int',
        'not null' => TRUE,
        'default' => 0
      ]
    ],
    'primary key' => ['uid'],
  ];

  return $schema;
}

src/Controller/MyModuleController.php.

<?php

namespace Drupal\mymodule\Controller;
use Drupal\Core\Controller\ControllerBase;

use Symfony\Component\DependencyInjection\ContainerInterface;
use Drupal\Core\Session\AccountInterface;
use Drupal\Core\Database\Connection;

/**
 * Returns responses for MyModule Database routes.
 */
class MyModuleController extends ControllerBase {
  protected $database;
  protected $currentUser;

  public function __construct(Connection $database, AccountInterface $current_user) {
    $this->database = $database;
    $this->currentUser = $current_user;
  }
  
  public static function create(ContainerInterface $container) {
    return new static(
        $container->get('database'),
        $container->get('current_user'),
    );
  }

  /**
   * Builds the response.
   */
  public function build() {
    $connection = $this->database;
    $currentUser = $this->currentUser;
    $visit = 1;

    // Here I pretend to retrieve the row that contains the user uid 
    $query = $connection->select('visit_counter', 't1')
        ->fields('t1', ['uid'])
        ->condition('uid', $uid, '=');
    $result = $query->execute();

    // Try to check result
    dpm($result);

    // if $result is false, then execute insert. But the problem is that it never execute it.
    if (!$result) {
      $insert = $connection->insert($dbTable)
        ->fields(['uid', 'user_visit_count']);
    
      $insert->values([
        'uid' => $uid,
        'user_visit_count' => $visit
      ]);
    
      $insert->execute();
    }

    $update = $connection->update($dbTable)
      ->expression('user_count', 'user_count + :visit', [':visit' => $visit]);
    $update->execute();

    $build['content'] = [
      '#type' => 'item',
      '#markup' => $this->t('It works!'),
    ];

    return $build;
  }
0

1 Answer 1

1

The shown code creates a new row, if a row for that user ID does not exist, or updates the existing row. For that, there is the Merge class.

In the question case, the code to update the database row would be the following one.

$connection->merge('visit_counter')
  ->key('uid', $currentUser->id())
  ->fields(['user_visit_count' => $visit])
  ->expression('user_visit_count', 'user_visit_count + :inc', [':inc' => 1])
  ->execute();

fields() is used to insert a database row, if a row for that user ID does not exist; expression() is used to update the existing database row, if there is already a row for that user ID.

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.