Queries: Db and DbQuery

The reviews table exists; now it has to be filled and read. Database queries are run by the Db class, and a select query is conveniently assembled with the DbQuery builder — it adds the table prefix itself and puts the query parts in place.

Inserting a row

Add a method that saves a review to the main class:

// modules/acmehello/acmehello.php
public function addReview(int $productId, string $author, int $rating, string $content): bool
{
    return Db::getInstance()->insert('acmehello_review', [
        'id_product' => $productId,
        'author' => pSQL($author),
        'rating' => $rating,
        'content' => pSQL($content),
        'date_add' => date('Y-m-d H:i:s'),
    ]);
}

The table name is written without the prefix — insert() adds it. The pSQL() function escapes a string so that a quote in the text does not break the query.

To have something to read, add a couple of rows straight to the database:

INSERT INTO ps_acmehello_review (id_product, author, rating, content, date_add)
VALUES (1, 'Anna', 5, 'Great product', NOW()), (1, 'Boris', 4, 'Good', NOW());

Reading

// modules/acmehello/acmehello.php
public function getReviews(int $productId): array
{
    $query = new DbQuery();
    $query->select('r.id_review, r.author, r.rating, r.content, r.date_add')
        ->from('acmehello_review', 'r')
        ->where('r.id_product = ' . $productId)
        ->orderBy('r.date_add DESC')
        ->limit((int) Configuration::get('ACMEHELLO_LIMIT'));
 
    return Db::getInstance()->executeS($query) ?: [];
}

from() takes the table name without the prefix and a short alias. executeS() returns an array of rows, each row being a column-to-value array.

Using it in the hook

In the hook handler replace the placeholder array with a method call:

// modules/acmehello/acmehello.php
public function hookDisplayFooterProduct(array $params): string
{
    $this->context->smarty->assign([
        'reviews' => $this->getReviews((int) Tools::getValue('id_product')),
    ]);
 
    return $this->fetch('module:acmehello/views/templates/hook/product-reviews.tpl');
}

Open product number 1 in the front office: the reviews block now shows rows from the database. Other products show "No reviews yet".

Reading methods

Method	What it returns
`executeS($query)`	All rows
`getRow($query)`	The first row
`getValue($query)`	The first value of the first row
`execute($sql)`	`true` or `false` — for queries without a result

The average rating of a product is a single value, so getValue() fits:

$query = new DbQuery();
$query->select('AVG(r.rating)')
    ->from('acmehello_review', 'r')
    ->where('r.id_product = ' . (int) $productId);
 
$average = round((float) Db::getInstance()->getValue($query), 1);

DbQuery methods

Method	Query part
`select('a.id, a.name')`	Columns
`from('table', 'a')`	Table and alias
`leftJoin('product', 'p', 'p.id_product = a.id_product')`	A join
`where('a.active = 1')`	A condition; several calls are joined with `AND`
`groupBy('a.id_product')`	Grouping
`orderBy('a.date_add DESC')`	Sorting
`limit(10, 20)`	Count and offset

Update and delete

$db = Db::getInstance();
 
$db->update('acmehello_review', ['rating' => 4], 'id_review = ' . (int) $reviewId);
$db->delete('acmehello_review', 'id_review = ' . (int) $reviewId);
 
$newId = (int) $db->Insert_ID();

The third argument of update() and the second argument of delete() are the WHERE condition. Insert_ID() returns the id of the last inserted row.

Common mistake

A request value put straight into a condition is an SQL injection: ->where('id_product = ' . Tools::getValue('id_product')). The Db class does not bind parameters for you. Cast every number with (int), pass every string through pSQL() and wrap it in quotes: "author = '" . pSQL($author) . "'".

Thanks for reading!