PDO efficient SELECT and INSERT

Question

PDO efficient SELECT and INSERT

I am new to PHP

and PDO

.

Below is the code, but it is very slow. Is there a way to make it more efficient?

while()

not as efficient but does the job. I tried other solutions but nothing really works.

Any suggestions?

    public function register_new_member(){

        global $bcrypt; 
        global $populera;

        $query = $this->db->prepare("SELECT id, lid, firstname, surname FROM `members`");

        try{
            $query->execute();      
            while ($data = $query->fetch()){

            $id = $data['id'];
            $lid = $data['lid'];

            $username = $populera->create_username($data['firstname'],$data['surname']);    

            //Show data
            echo "<br>Username: ".$username;
            echo "<br>ID: ".$id;
            echo "<br>LID: ".$lid;      

            //Static password - change after login      
            $password = "password";

            //Bcrypt    
            $pass = $bcrypt->generateHash($password);

            //Show data
            echo "<br>Password: ".$pass;


            $query2  = $this->db->prepare("INSERT INTO `login` (`id`, `lid`, `user`, `password`) VALUES (?, ?, ?, ?) ");

            $query2->bindValue(1, $id);
            $query2->bindValue(2, $lid);
            $query2->bindValue(3, $username);
            $query2->bindValue(4, $pass);

            $query2->execute();
            }
            //Success? 
            echo "<br>Saved!";

            }catch(PDOException $e){
                die($e->getMessage());
            }
}

+3

php mysql pdo

Per76 Dec 21 14 at 20:05

source to share

4 answers

I think the efficiency issue here is that you run $query2->execute()

for every row as a result of the first query. You can make multiple tabs to make them more efficient.

How to insert multiple lines into one query

+1

benjosantony Dec 21 14 at 20:20

source to share

You don't know what you mean by very very slow

.

You can try this query to fill the table with login

bulk.

INSERT INTO login (id, lid, user)
SELECT id,
       lid,
       CONCAT(firstname, ' ', surname) AS user
  FROM members

This should update all the required lines.

Finally, you will need to do this from php in order to set passwords. This will set any blank or blank passwords to your starting point password.

        //Bcrypt    
        $pass = $bcrypt->generateHash($password);
        $query2  = $this->db->prepare("UPDATE login SET password  ? WHERE password IS NULL OR LENGTH(password) = 0");
        $query2->bindValue(1, $pass);
        $query2->execute();

+1

O. Jones Dec 21 14 at 20:29

source to share

I would consider optimizing a pair for this:

Prepare your insert outside of the while loop (no point in reworking it and it's expensive)
All inserts in a transaction, if there is a huge amount of data, just give them smaller chunks or use mysql savepoints.

+1

crusa Dec 22. 14 at 15:40

source to share

mike__t · Accepted Answer · 2014-12-21T20:50:15+0000

The hash function can be slow - since you set the password the same for all users, you can move this calculation out of the loop. (This should be slow because the hash is usually repeated for a number of "rounds" to force the rough hitting hard.)

I cannot tell from your code if you are using a random salt for each hash. If you are (and you should be), then this suggestion will leave you with a lot of passwords hashed with the same salt. I'm not sure how risky it is given that you set them to the same value anyway.

Personally, I would try to find a solution where I did not set passwords - eg. generating a random token that I email each user so they can create a new password for themselves. (Something you might need to allow users to reset forgotten passwords.)

PDO efficient SELECT and INSERT

More articles: