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