Asked  7 Months ago    Answers:  5   Viewed   59 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

19

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));
Wednesday, March 31, 2021
 
max_
answered 7 Months ago
85

However, thanks to this guys. I found out that you need to pass the value by reference with a & before like that :

foreach($Fields as $Name => &$Value){
    $Query->bindParam(':'.$Name, $Value, PDO::PARAM_STR);
}

This was driving me nuts.

Actual quote from PHP.net :

Vili 28-May-2010 12:01

This works ($val by reference):

<?php
foreach ($params as $key => &$val){
    $sth->bindParam($key, $val);
}
?>

This will fail ($val by value, because bindParam needs &$variable):

<?php
foreach ($params as $key => $val) {
    $sth->bindParam($key, $val);
}
?>
Wednesday, March 31, 2021
 
Hugo
answered 7 Months ago
79

Just create your query text wtih ? placeholders as:

INSERT INTO table (firstName, lastName) VALUES (?, ?),(?, ?),(?, ?)

And execute it. Sample code can be:

$data = ['Joe', 'Smith','Fred','Sampson','Lisa','Pearce'];
$placeholders = ['(?, ?)', '(?, ?)', '(?, ?)'];  // but you should define this data according to your data
$query = 'INSERT INTO table (firstName, lastName) VALUES ' . implode(',', $placeholders);
$stmt = $dbh->prepare($query);
$stmt->execute($data);
Saturday, May 29, 2021
 
zhartaunik
answered 5 Months ago
74

In case of a multi-value return function you can't refer to fields or methods of a specific value of the result when calling the function.

And if one of them is an error, it's there for a reason (which is the function might fail) and you should not bypass it because if you do, your subsequent code might also fail miserably (e.g. resulting in runtime panic).

However there might be situations where you know the code will not fail in any circumstances. In these cases you can provide a helper function (or method) which will discard the error (or raise a runtime panic if it still occurs).
This can be the case if you provide the input values for a function from code, and you know they work.
Great examples of this are the template and regexp packages: if you provide a valid template or regexp at compile time, you can be sure they can always be parsed without errors at runtime. For this reason the template package provides the Must(t *Template, err error) *Template function and the regexp package provides the MustCompile(str string) *Regexp function: they don't return errors because their intended use is where the input is guaranteed to be valid.

Examples:

// "text" is a valid template, parsing it will not fail
var t = template.Must(template.New("name").Parse("text"))

// `^[a-z]+[[0-9]+]$` is a valid regexp, always compiles
var validID = regexp.MustCompile(`^[a-z]+[[0-9]+]$`)

Back to your case

IF you can be certain Get() will not produce error for certain input values, you can create a helper Must() function which would not return the error but raise a runtime panic if it still occurs:

func Must(i Item, err error) Item {
    if err != nil {
        panic(err)
    }
    return i
}

But you should not use this in all cases, just when you're sure it succeeds. Usage:

val := Must(Get(1)).Value

Alternative / Simplification

You can even simplify it further if you incorporate the Get() call into your helper function, let's call it MustGet:

func MustGet(value int) Item {
    i, err := Get(value)
    if err != nil {
        panic(err)
    }
    return i
}

Usage:

val := MustGet(1).Value

See some interesting / related questions:

how to parse multiple returns in golang

Return map like 'ok' in Golang on normal functions

Tuesday, June 1, 2021
 
Hugo
answered 5 Months ago
100

In other DB abstraction frameworks in other languages it can be used for things like making sure you're doing the proper escaping for in-lining values (for drivers that don't support proper bound parameters) and improving network efficiency by making sure numbers are binary packed appropriately (given protocol support). It looks like in PDO, it doesn't do much.

   if (PDO_PARAM_TYPE(param->param_type) == PDO_PARAM_STR && param->max_value_len <= 0 && ! ZVAL_IS_NULL(param->parameter)) {
                if (Z_TYPE_P(param->parameter) == IS_DOUBLE) {
                        char *p;
                        int len = spprintf(&p, 0, "%F", Z_DVAL_P(param->parameter));
                        ZVAL_STRINGL(param->parameter, p, len, 0);
                } else {
                        convert_to_string(param->parameter);
                }
        } else if (PDO_PARAM_TYPE(param->param_type) == PDO_PARAM_INT && Z_TYPE_P(param->parameter) == IS_BOOL) {
                convert_to_long(param->parameter);
        } else if (PDO_PARAM_TYPE(param->param_type) == PDO_PARAM_BOOL && Z_TYPE_P(param->parameter) == IS_LONG) {
                convert_to_boolean(param->parameter);
        }

So, if you say it is a STR (or if you say nothing at all as that is the default) and your data's internal type is a double then it will turn it into a string using one method, if it's not a double then it will convert it to a string using a different method.

If you say it's an int but it is really a bool then it will convert it to a long.

If you say it's a bool but it's really a number then it will convert it to a true boolean.

This is really all I saw (quickly) looking at the stmt source, I imagine once you pass the parameters into the driver they can do additional magic. So, I'd guess that all you get is a little bit of do the right and a whole lot of behavior ambiguity and variance between drivers.

Thursday, June 10, 2021
 
Easen
answered 5 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :