SQLite3Stmt::bindValue

(PHP 5 >= 5.3.0, PHP 7)

SQLite3Stmt::bindValueBinds the value of a parameter to a statement variable

Description

public SQLite3Stmt::bindValue ( string|int $param , mixed $value , int $type = SQLITE3_TEXT ) : bool

Binds the value of a parameter to a statement variable.

Caution

Before PHP 7.2.14 and 7.3.0, respectively, once the statement has been executed, SQLite3Stmt::reset() needs to be called to be able to change the value of bound parameters.

Parameters

param

Either a string (for named parameters) or an int (for positional parameters) identifying the statement variable to which the value should be bound. If a named parameter does not start with a colon (:) or an at sign (@), a colon (:) is automatically preprended. Positional parameters start with 1.

value

The value to bind to a statement variable.

type

The data type of the value to bind.

  • SQLITE3_INTEGER: The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

  • SQLITE3_FLOAT: The value is a floating point value, stored as an 8-byte IEEE floating point number.

  • SQLITE3_TEXT: The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16-LE).

  • SQLITE3_BLOB: The value is a blob of data, stored exactly as it was input.

  • SQLITE3_NULL: The value is a NULL value.

As of PHP 7.0.7, if type is omitted, it is automatically detected from the type of the value: bool and int are treated as SQLITE3_INTEGER, float as SQLITE3_FLOAT, null as SQLITE3_NULL and all others as SQLITE3_TEXT. Formerly, if type has been omitted, it has defaulted to SQLITE3_TEXT.

Note:

If value is null, it is always treated as SQLITE3_NULL, regardless of the given type.

Return Values

Returns true if the value is bound to the statement variable, or false on failure.

Changelog

Version Description
7.4.0 param now also supports the @param notation.

Examples

Example #1 SQLite3Stmt::bindValue() example

<?php
$db 
= new SQLite3(':memory:');

$db->exec('CREATE TABLE foo (id INTEGER, bar STRING)');
$db->exec("INSERT INTO foo (id, bar) VALUES (1, 'This is a test')");

$stmt $db->prepare('SELECT bar FROM foo WHERE id=:id');
$stmt->bindValue(':id'1SQLITE3_INTEGER);

$result $stmt->execute();
var_dump($result->fetchArray(SQLITE3_ASSOC));
?>

The above example will output:

array(1) {
  ["bar"]=>
  string(14) "This is a test"
}

See Also

add a note add a note

User Contributed Notes 4 notes

up
30
zeebinz at gmail dot com
10 years ago
Note that this also works with positional placeholders using the '?' token:

<?php

$stmt
= $db->prepare('SELECT * FROM mytable WHERE foo = ? AND bar = ?');
$stmt->bindValue(1, 'somestring', SQLITE3_TEXT);
$stmt->bindValue(2, 42, SQLITE3_INTEGER);

?>

Positional numbering starts at 1.
up
10
andrevanzuydam at gmail dot com
5 years ago
I just want to say again,

Numbering for parameters starts at ONE!

This has caught me out quite a few times!
up
3
bohwaz
5 years ago
It might be a good idea to feed bindValue the type of the variable manually, or you might encounter weird stuff as the passed value is often treated as SQLITE3_TEXT and results in buggy queries.

For example:
<?php
$st
= $db->prepare('SELECT * FROM test WHERE (a+1) = ?');
$st->bindValue(1, 2);
?>

Will never return any result as it is treated by SQLite as if the query was 'SELECT * FROM test WHERE (a+1) = "2"'. Instead you have to set the type manually:

<?php
$st
= $db->prepare('SELECT * FROM test WHERE (a+1) = ?');
$st->bindValue(1, 2, \SQLITE3_INTEGER);
?>

And it will work. This bug is reported in https://bugs.php.net/bug.php?id=68849

Here is a simple function to help you make bindValue work correctly:

<?php
function getArgType($arg)
{
    switch (
gettype($arg))
    {
        case
'double': return SQLITE3_FLOAT;
        case
'integer': return SQLITE3_INTEGER;
        case
'boolean': return SQLITE3_INTEGER;
        case
'NULL': return SQLITE3_NULL;
        case
'string': return SQLITE3_TEXT;
        default:
            throw new \
InvalidArgumentException('Argument is of invalid type '.gettype($arg));
    }
}
?>
up
1
vaibhavatul47 at gmail dot com
4 years ago
I used following logic to prepare statements, It handles both Values and Arrays ( taking help from bohwaz note) :

<?php
   
function getArgType($arg) {
        switch (
gettype($arg)) {
            case
'double':  return SQLITE3_FLOAT;
            case
'integer': return SQLITE3_INTEGER;
            case
'boolean': return SQLITE3_INTEGER;
            case
'NULL':    return SQLITE3_NULL;
            case
'string':  return SQLITE3_TEXT;
            default:
                throw new \
InvalidArgumentException('Argument is of invalid type '.gettype($arg));
        }
    }

foreach (
$params as $index => $val) {
               
// indexing start from 1 in Sqlite statement
               
if (is_array($val)) {
                   
$ok = $stmt->bindParam($index + 1, $val);
                } else {
                   
$ok = $stmt->bindValue($index + 1, $val, getArgType($val));
                }
               
                if (!
$ok) {
                    throw new
Exception("Unable to bind param: $val");
                }
            }
?>
To Top