PHP bound variables, Invalid parameter number error or PDO Update Statement

Question

I built a update statement in PHP using PDO, however I keep getting this error.

ERROR: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

I have counted and re-counted (25) and spent a while making sure my syntax is correct and I can't understand what I am doing wrong.

Here is the code:

$sql = "UPDATE :tablename
        SET entryName = :entryname,
        entryAdd1 = : add1,
        entryAdd2 = :add2,
        entryCity = :city,
        entryCounty = :county,
        entryCountry = :country,
        entryPC = :postcode,
        entryPhone = :phone,
        entryEmail = :email,
        entryURL = :entryurl,
        entryDesc = :entryDesc,
        entryImg = :entryimg,
        entryStar  = :star,
        entryCuststar = :custstar,
        chains = :chains,
        cafe = :cafe,
        fishandchips = :fishandchips,
        indian = :indian,
        itallian = :italian,
        pizzeria = :pizzeria,
        tapas = :tapas,
        tearooms = :tearooms
        WHERE :colname = :entryid";

        $statement = $conn->prepare($sql);

        $statement->bindParam(":tablename", $tablename, PDO::PARAM_STR);

        $statement->bindParam(":entryname", $entryname, PDO::PARAM_STR);
        $statement->bindParam(":add1", $add1, PDO::PARAM_STR);
        $statement->bindParam(":add2", $add2, PDO::PARAM_STR);
        $statement->bindParam(":city", $entrytown, PDO::PARAM_STR);
        $statement->bindParam(":county", $entrycounty, PDO::PARAM_STR);
        $statement->bindParam(":country", $entrycountry, PDO::PARAM_STR);
        $statement->bindParam(":postcode", $entrypostcode, PDO::PARAM_STR);
        $statement->bindParam(":phone", $entryphone, PDO::PARAM_STR);
        $statement->bindParam(":email", $entryemail, PDO::PARAM_STR);
        $statement->bindParam(":entryurl", $entryurl, PDO::PARAM_STR);
        $statement->bindParam(":entryDesc", $entrydesc, PDO::PARAM_STR);
        $statement->bindParam(":entryimg", $entryicon, PDO::PARAM_STR);
        $statement->bindParam(":star", $star, PDO::PARAM_STR);
        $statement->bindParam(":custstar", $custstar, PDO::PARAM_STR);
        $statement->bindParam(":chains", $chains, PDO::PARAM_INT);
        $statement->bindParam(":cafe", $cafe, PDO::PARAM_INT);
        $statement->bindParam(":fishandchips", $fishchips, PDO::PARAM_INT);
        $statement->bindParam(":indian", $indian, PDO::PARAM_INT);
        $statement->bindParam(":italian", $italian, PDO::PARAM_INT);
        $statement->bindParam(":pizzeria", $pizzeria, PDO::PARAM_INT);
        $statement->bindParam(":tapas", $tapas, PDO::PARAM_INT);
        $statement->bindParam(":tearooms", $tearooms, PDO::PARAM_INT);

        $statement->bindParam(":colname", $colname, PDO::PARAM_STR);
        $statement->bindParam(":entryid", $entryid, PDO::PARAM_INT);

        $count = $statement->execute();

I state the $conn variable further up, it's just a practice directory I was making. If anyone could point me in the right direction I would appreciate it.

Answer 1

You can't parameterize table/column names. Try this if you need them to be dynamic:

$sql = "UPDATE " . $tablename . "
    SET entryName   = :entryname,
    entryAdd1       = :add1,
    entryAdd2       = :add2,
    entryCity       = :city,
    entryCounty     = :county,
    entryCountry    = :country,
    entryPC         = :postcode,
    entryPhone      = :phone,
    entryEmail      = :email,
    entryURL        = :entryurl,
    entryDesc       = :entryDesc,
    entryImg        = :entryimg,
    entryStar       = :star,
    entryCuststar   = :custstar,
    chains          = :chains,
    cafe            = :cafe,
    fishandchips    = :fishandchips,
    indian          = :indian,
    itallian        = :italian,
    pizzeria        = :pizzeria,
    tapas           = :tapas,
    tearooms        = :tearooms
    WHERE " . $colname . " = :entryid";

    $statement = $conn->prepare($sql);

    $statement->bindParam(":entryname", $entryname, PDO::PARAM_STR);
    $statement->bindParam(":add1", $add1, PDO::PARAM_STR);
    $statement->bindParam(":add2", $add2, PDO::PARAM_STR);
    $statement->bindParam(":city", $entrytown, PDO::PARAM_STR);
    $statement->bindParam(":county", $entrycounty, PDO::PARAM_STR);
    $statement->bindParam(":country", $entrycountry, PDO::PARAM_STR);
    $statement->bindParam(":postcode", $entrypostcode, PDO::PARAM_STR);
    $statement->bindParam(":phone", $entryphone, PDO::PARAM_STR);
    $statement->bindParam(":email", $entryemail, PDO::PARAM_STR);
    $statement->bindParam(":entryurl", $entryurl, PDO::PARAM_STR);
    $statement->bindParam(":entryDesc", $entrydesc, PDO::PARAM_STR);
    $statement->bindParam(":entryimg", $entryicon, PDO::PARAM_STR);
    $statement->bindParam(":star", $star, PDO::PARAM_STR);
    $statement->bindParam(":custstar", $custstar, PDO::PARAM_STR);
    $statement->bindParam(":chains", $chains, PDO::PARAM_INT);
    $statement->bindParam(":cafe", $cafe, PDO::PARAM_INT);
    $statement->bindParam(":fishandchips", $fishchips, PDO::PARAM_INT);
    $statement->bindParam(":indian", $indian, PDO::PARAM_INT);
    $statement->bindParam(":italian", $italian, PDO::PARAM_INT);
    $statement->bindParam(":pizzeria", $pizzeria, PDO::PARAM_INT);
    $statement->bindParam(":tapas", $tapas, PDO::PARAM_INT);
    $statement->bindParam(":tearooms", $tearooms, PDO::PARAM_INT);

    $statement->bindParam(":entryid", $entryid, PDO::PARAM_INT);

    $count = $statement->execute();

Thanks,

Andrew

Answer 2

Table and Column names cannot be replaced by parameters in PDO. You'd have to replace these with the actual names, offcourse you can filter out unwanted data if you wanted to, but not by using the prepared statements.

Won't work:

$sth = $dbh->prepare('SELECT name, colour, calories FROM ?  WHERE calories < ?');

THIS WORKS!

$sth = $dbh->prepare('SELECT name, colour, calories FROM fruit WHERE calories < ?');