PDO Delete, How to set DatatTypes

Question

PDO Delete, How to set DatatTypes

Walter Pulido Jun '16

Created Jun '16	Last Reply Jun '16	Replies 1	Views 474	Votes 0

Walter Pulido 12.8k

Jun '16

Hi, I'm trying to delete a record from a table where the ID column is a string:

$this->_adapter->delete('my_table', "id = :token",
            array('token' => $tokenId),
            array('token' => Column::BIND_PARAM_STR)
        );

When I try to execute the query it is raising the following syntax error:

LINE 1: DELETE FROM "my_table" WHERE id=816c85cdb28556d0a

The error occurs because the query is sending the token as numeric instead of as a string, how can I properly set the data type of the token, so the token is added as a string to the SQL query?

Thanks in advance!

Jonathan Aaron Steel · Answer 1 · 2016-06-02T14:34:54-07:00

public boolean delete (string|array $table, [string $whereCondition], [array $placeholders], [array $dataTypes]) inherited from Phalcon\Db\Adapter

This method deletes data from a table using custom RBDM SQL syntax, so you can only supply string in a where condition.

Internally, this method uses PDO's execute with placeholders and dataTypes supplied, even though the condition itself is not treated but is taken as is (string).

Pay attention how this method works in Zephir:

        if !empty whereCondition {
            let sql = "DELETE FROM " . escapedTable . " WHERE " . whereCondition;
        } else {
            let sql = "DELETE FROM " . escapedTable;
        }

This is excerpt from my project:

        $whereCondition = "$primaryKey = $this->id";
                    //Warning! If $whereCondition is string it not escaped.
                    //But since we're fetching primary key dynamically from the table definition, i.e. not from an user input, this is a safe way to handle it
                    //Deletes data from a table using custom RBDM SQL syntax
                    $delete = static::$adapter->delete($table, $whereCondition);

            //Returns the number of affected rows by the lastest INSERT/UPDATE/DELETE executed in the database system
                    return $delete ? static::$adapter->affectedRows() : 0x00;