Insert a lot of record using single arguments and using bindParam

Question

I have some method to insert some data into a database like this:

public function register($username, $email, $hashedPassword, $activationCode)
    {
        try {
            $conn = Database::getConnection();
            // Connect and create the PDO object
            $conn->exec('SET CHARACTER SET utf8');      // Sets encoding UTF-8
            // Define and prepare an INSERT statement
            $sql = 'INSERT INTO users (username, email, pass, reset_token, dateAdded )
            VALUES (:username, :pass, :email, :token, now())';
            $sqlprep = $conn->prepare($sql);

            // Adds value with bindParam
            $sqlprep->bindParam(':username', $username, PDO::PARAM_STR);
            $sqlprep->bindParam(':email', $email, PDO::PARAM_STR);
            $sqlprep->bindParam(':pass', $hashedPassword);
            $sqlprep->bindParam(':token', $activationCode);

            // If the query is successfully executed, output the value of the last insert id
            if ($sqlprep->execute()) {
                //echo 'Succesfully added the row with id='. $conn->lastInsertId();
                $this->result = true;
            }
            $conn = null;        // Disconnect

        } catch (PDOException $e) {
            include('../views/error.php');
            include('../views/admin/includes/footer.php');
            exit();
        }
    }

The problem is I think it's not a good method if I have so many arguments for my function to enter into a database. So is it any good way I can enter a lot of fields just by using 1 parameter but still using bindParam? Since I see a lot of examples is only using prepare without bindParam. I think I can use an array, but I don't know the proper way to do it. So I need some help how I can do it.

Akshay Khetrapal · Answer 1 · 2015-03-19T09:37:29.163

0

You can insert the params as an array into $sqlprep->execute($param_array) Or, simply passing each param into an array inside execute, like this: $sqlprep->execute(array($param1, $param2))

Update:

Pass values into $input as an array:

$input = array('username' => $username, 'activationHash' => $activationHash); //and so on

Now on the model side, You can bind these values to params using foreach loop like this:

foreach ($values as $key => $value) {
        $sqlprep->bindParam(':' . $key, $value , PDO::PARAM_STR);
    }

edited Mar 19 '15 at 09:37

answered Mar 19 '15 at 09:18

Akshay Khetrapal

2,586
5
22
38

actually I want to keep my SQL using bindparam like this: $sqlprep->bindParam(':token', $activationCode); all I want is how do I put all the parameters like username, email, and other into a single parameter. so later I can extract it and put the parameter into bindparam . something like this maybe $input = (array(token => tokenValue, Email => Email value)) then later i can enter to my method $user->register($input). that's what i really want. – Ying Mar 19 '15 at 09:25
You have to declare all the parameters before binding. You can try this however, `$user->register($input)` where $input is an array And on the model side, `foreach ($values as $key => $value) { $sqlprep->bindParam(':' . $key, $value , PDO::PARAM_STR); }` – Akshay Khetrapal Mar 19 '15 at 09:30
The reason you cannot have a single parameter is because each parameter value is bind with the parameter at the database and if the number of parameters isn't equal to the number of values, you'll get an error each time. – Akshay Khetrapal Mar 19 '15 at 09:32
can you edit your answer and explain how to put all my parameters into array and how i can extract it using foreach please? i think this is the answer that i want. – Ying Mar 19 '15 at 09:34
for security reasing i want change :email or other automatically, i just want to enter value using single parameter. that all – Ying Mar 19 '15 at 09:37
Keeping your insert query as it is, you can use the updated foreach statement to bind params and it will work fine. – Akshay Khetrapal Mar 19 '15 at 09:38
with this code it's hard for me to change parambind setting like when i have to use PDO::PARAM_STR or PDO::PARAM_INT. – Ying Mar 19 '15 at 09:52
You can either get rid of it or set it to PDO::PARAM_STR. I'd suggest you get rid of it because if it is set to PDO::PARAM_STR or PDO::PARAM_INT, the driver ignores it – Akshay Khetrapal Mar 19 '15 at 10:37

score 0 · Answer 2 · edited May 23 '17 at 11:50

0

https://stackoverflow.com/a/10060755/1747411
Check second example, you have to repeat values with binds
e.g
VALUES (:username1, :pass1, :email1, :token1, now()), (:username2, :pass2, :email2, :token2, now())
and bindParam with loop

edited May 23 '17 at 11:50

Community

1
1

answered Mar 19 '15 at 09:21

Durim Jusaj

81
5

I'm afraid I don't want to use automatic value for my bindparam like using i++: $stmt -> bindParam($i++, $item['key1']); all I want is how to make all my argument become 1 argument only. that's all. – Ying Mar 19 '15 at 09:30

score 0 · Accepted Answer · answered Mar 20 '15 at 08:09

since you want keep your bindparam i suggest you use input like this:

$input = array('username' => $username, 'activationHash' => $activationHash);

and in your bindParam add a code like this:

public function register($input){
//code

$sqlprep->bindParam(':username', $input['username'], PDO::PARAM_STR);

//other
}

hope this will solve your problem

Insert a lot of record using single arguments and using bindParam

3 Answers3