zenDB : Disallowed standalone number in sql template

3 posts by 2 authors in: Forums > CMS Builder
Last Post: July 11 (RSS)

By Djulia - July 10 - edited: July 10

Hi,

I'm trying to use zenDB to update a record, but I can't find the correct code syntax.

The error I receive is this : Disallowed standalone number in sql template. Use whereArray or placeholders instead

$userNum = 4;
$category = "CTA";

$resultSet = DB::query("UPDATE :_members
SET category = CASE WHEN INSTR(category, ?) = 0
                      THEN CONCAT(category, ?)
                      ELSE category
                 END
WHERE num = ?", $category. '\t', '\t' . $category. '\t', $userNum);

Do you have any suggestions?

I also tried but without success :

$resultSet = DB::query("UPDATE :_members
SET category = CASE WHEN INSTR(category, ?) = 0
                      THEN CONCAT(category, ?)
                      ELSE category
                 END
WHERE num = ?", [$category. '\t', '\t' . $category. '\t', $userNum]);

Thanks,
Djulia

By Dave - July 11

Hi Djulia,

You can pass the number as a positional parameter like this:

$resultSet = DB::query("UPDATE :_members
SET category = CASE WHEN INSTR(category, ?) = ?
                      THEN CONCAT(category, ?)
                      ELSE category
                 END
WHERE num = ?", ["$category\t", 0, "\t$category\t", $userNum]);

Or use named parameters like this:

$params    = [
    ':TcategoryT' => "\t$category\t",
    ':categoryT'  => "$category\t",
    ':userNum'    => $userNum,
    ':n0'         => 0,
];
$resultSet = DB::query("UPDATE :_members
SET category = CASE WHEN INSTR(category, :categoryT) = :n0
                    THEN CONCAT(category, :TcategoryT)
                    ELSE category
                 END
WHERE num = :userNum", $params);

Let me know if that works for you. (Note, code is untested, be sure to test it).

Dave Edis - Senior Developer
interactivetools.com

By Djulia - July 11 - edited: July 12

Hi Dave,

Thank you, it's perfect!

I really like the named parameters approach :
https://github.com/interactivetools-com/ZenDB/?tab=readme-ov-file#named-and-positional-placeholders

I use this query to update a pillbox field (multi-value). Basically, this code adds a category to a user only if it isn't already there.

Djulia