PDO prepared statement - what are colons in parameter names used for?

Question 1

I've seen many articles using colons (:) in front of named parameters when using PDO, and a couple that do not use the colon. I'd just as soon not use the colon, simply because it's one less keystroke and slightly easier to read.

It seems to be working fine for me, but I'm curious if there is something important that I'm missing when it comes to the use of colons?

For example, this works just fine:

function insertRecord ($conn, $column1, $comumn2) {
    try {
        $insertRecord = $conn->prepare('INSERT INTO Table1 (column1, column2)
        VALUES(:column1, :column2)');
        $insertRecord->execute(array(
                'column1' => $column1,
                'column2' => $column2
            ));
    }
    catch(PDOException $e) {
        echo $e->getMessage();
    }
}

As opposed to most developers using this, which also works:

function insertRecord ($conn, $column1, $comumn2) {
    try {
        $insertRecord = $conn->prepare('INSERT INTO Table1 (column1, column2)
        VALUES(:column1, :column2)');
        $insertRecord->execute(array(
                ':column1' => $column1,
                ':column2' => $column2
            ));
    }
    catch(PDOException $e) {
        echo $e->getMessage();
    }
}

Notice the colons in the execute statement parameters.

I'd like to understand what the colons are for.

Question 2

Colons are required in the SQL statement, to indicate which identifiers are placeholders.

Colons in the execute() or bindParam() calls are optional. The documentation specifies them, but the implementation is clever enough to figure out what you mean if you leave them out (what else could you mean?).

Question 3

Not near a terminal to check, but I believe you have to type bind it to INT and send it in as an INT, not as "b010101" (or whatever):

$sql='INSERT INTO test(id,data) VALUES(:id,:bit)';
$stmt=db::db()->prepare($sql);
$stmt->bindValue('id', null, PDO::PARAM_NULL);
$stmt->bindValue('bit', (int)$value, PDO::PARAM_INT);
$stmt->execute();

Quick check on Google brought up this similar previous answer.

Question 4

Yes, bindParam binds a parameter to a variable name (reference), not a value, as the manual says.

However, there's a simpler syntax for your situation. PDOStatement::execute can take an array of values.

public function insert($table, $cols, $values){

    $placeholder = array();
    for ($i = 0; i < count($values); $i++)
      $placeholder[] = '?';

    $sql = 'INSERT INTO '. $table . ' (`' . implode("`, `", $cols) . '`) ';
    $sql.= 'VALUES (' . implode(", ", $placeholder) . ')';

    $stmt = $this->dbh->prepare($sql);
    $stmt->execute($values);

}

Question 5

Yes, you can reuse the same prepared statement, but not how you have it in the question. What you are trying to do is essentially the same as doing this:

for ($i=0; $i<$some_number; $i++) {
    echo $i."n";
    for ($i=0; $i<$some_number; $i++) {
        // do something
    }
}

The the second for loop moves the same pointer as the original one, so therefore the output from the above would simply be "0" indicating that the original for loop only happened once.

So in order to get around this, you will need to store the results of the first execute into an array and then iterate over it. That way you won't have to worry about any pointers

$SQL = $dbh->prepare("SELECT * FROM user WHERE id=? AND users=?");
$SQL->execute(array($id,$userid));
$checks = $SQL->fetchAll();
foreach ($checks as $check) {
    $SQL->execute(array($id2,$userid2));
    while ($check2 = $SQL->fetchObject(){
        //while loops for second execution
    }
}

This way, you are using exactly the same prepared statement (which is good) and the original $check variable is available to be used in the while loop.

However, with all that said, I have a strong hunch that you can probably get everything into one single SQL query without the need for looping over it like this.

Question 6

I don't know much about PDO, but my feeling is there is something wrong with the way you bind the parameters. However, the easiest way to tell for sure is to see the actual query.

According to the docs, you should be able to see the generated query as it went to SQL in $stmt->queryString. It's not possible to see right now because you are binding the parameters to the statement after you are outputting $stmt.

Do a print_r() after you bind the parameters (or maybe even after execution of the query, I don't know). You should get the real query string, and get to the bottom of the problem.

PDO prepared statement - what are colons in parameter names used for?

Answers

php,pdo,prepared-statement