Binding multiple values in pdo

Viewed 316 times

Is there's an easy way of binding multiple values in PDO without repitition ? Take a look at the following code :

$result_set = $pdo->prepare("INSERT INTO `users` (`username`, `password`, `first_name`, `last_name`) VALUES (:username, :password, :first_name, :last_name)");

$result_set->bindValue(':username', '~user');
$result_set->bindValue(':password', '~pass');
$result_set->bindValue(':first_name', '~John');
$result_set->bindValue(':last_name', '~Doe');

$result_set->execute();

Here, I binded values in a repepeated way which is 4 times. So is there's an easy way of binding multiple values in PDO ?

Answers

You can always bind values within the arguments of execute() as long as you're fine with the values being treated as PDO::PARAM_STR (string).

$result_set = $pdo->prepare("INSERT INTO `users` (`username`, `password`, `first_name`, `last_name`) VALUES (:username, :password, :first_name, :last_name)");
$result_set->execute(array(
    ':username' => '~user',
    ':password' => '~pass',
    ':first_name' => '~John',
    ':last_name' => '~Doe'
));

You can use the array passed just like any array:

$user = "Nile";
$pdo->execute(array(":user" => $user));

Tuesday, November 15, 2022

qwertylpc-d555efe705be

You can build the "IN (...)" string dynamically:

$in_string = '(';
foreach ( $array_of_parameters as $parameter ) {
    $in_string .= ':' . chr($i + 97) . ','; // Get the ASCII character
}
$in_string = substr($in_string, 0, -1) . ')';

$statement = $db->prepare("SELECT blah FROM blah_table WHERE blahID IN ($in_string)");

Sunday, October 23, 2022

kentzo

Since you are not passing the value in the execute method, it will not be automatically escaped for you. The following would be escaped for you:

$sql = "INSERT INTO sessions (id, name) VALUES (1, ?)";
$query = $this->connection->prepare($sql);
$query->execute(array("O'brian"));

Monday, October 17, 2022

jesse_sherlock

Is it possible to bind a table name?

No.

You have to whitelist table names. I doubt you want to let a user to browse any table from your database.

And you have to format identifiers manually as well. There is a tag wiki with example. Why not read it first?

Update: As you can see, PDO turns out to be inconvenient for real life tasks. So, you have to have a more intelligent abstraction library to handle MySQL queries. Here is an example using the safeMysql class, which will make your code dramatically shorter:

class form{
    public function __construct($table){
        global $db;
        return $db->getAll("DESCRIBE ?n", $table);
    }
}

2 notes:

I've removed the second parameter as there is no code in your function that uses it.
NEVER connect in the class. Use an already opened connection instead. Or you will kill your MySQL server with so many connects.

Exclude implemented version

class form {
    public function __construct($table,$skip = array("id")){
        global $db;
        $data = array();
        $res = $db->query("DESCRIBE ?n", $table);
        while($row = $db->fetch($res)) {
            if (!in_array($row['Field'],$skip)) {
                $data[] = $row;
            }
        }
        return $data;
    }
}

However, such class seldom can be used as intended - there are always a lot of exceptions and manual formatting to make it usable.

Wednesday, September 28, 2022

andersonbd1

I don't think ctypes allows one to translate std::pair to a python tuple without much boilerplate code. Especially since std::pair is a feature of the c++11 standard, and ctypes only works with c-style functions [citation / verification needed].

I suggest would using output parameters, the c way to return multiple values. The idea is simple, the c-function returns it's values by pointer, example.c:

void divide_modulo(int a, int b, int *div, int *rest)
{
    *div  = a / b;
    *rest = a % b;
}

Then compile it to a shared library:

gcc -o libexample.so -shared example.c

The libexample.so now allows you to write to python integers via a pointer in c which are passes as parameters like this:

import ctypes
lib = ctypes.cdll.LoadLibrary('./libexample.so')

def divide_modulo(a, b):
  div = ctypes.c_int(0)
  rest = ctypes.c_int(0)
  lib.divide_modulo(a, b, ctypes.byref(div), ctypes.byref(rest))
  return (div.value, rest.value)

print(divide_modulo(11, 4))

The ctypes.byref wrapper when calling lib.divide_modulo with div and rest converts an int to a pointer to an int.

Sunday, October 30, 2022

florian_neumann

Only authorized users can answer the search term. Please sign in first, or register a free account.

Not the answer you're looking for? Browse other questions tagged :

php

pdo

bind

Binding multiple values in pdo

Answers

php,pdo,bind