Home Indexing array in php pdo insert while loop
Reply: 1

Indexing array in php pdo insert while loop

Brendan
1#
Brendan Published in 2017-12-07 19:21:45Z

I'm trying to insert a row into a table for each fetch from another table using a while loop. The code I currently have inserts the first users data into the database.

If the insert query is outside of the loop it will enter the last users data. It must be an issue iterating through the array. Is there a way I can index the array values for each pass of the loop?

$query = "SELECT * FROM users_table" ;
$statement = $db->prepare($query);
$rows = $statement->fetchAll(); 
$statement->execute();

while($rows = $statement->fetch()){

    $salted = $sso_key . $companyId;
    $hash = hash('sha1',$salted,true);
    $saltedHash = substr($hash,0,16);

    $iv = substr(md5(microtime()),rand(0,16),16); //Generate random 16 bit string


    $user_data = array( 
      "user_id" => $rows['id'],
      "first_name" => $rows['first_name'],
      "last_name" => $rows['last_name'],
      "email" => $rows['email'],
      "position" => $rows['type']);

    $data = json_encode($user_data);
    $data = $iv . $data;

    $pad = 16 - (strlen($data) % 16);
    $data = $data . str_repeat(chr($pad), $pad);

    $cipher = mcrypt_module_open(MCRYPT_RIJNDAEL_128,'','cbc','');
    mcrypt_generic_init($cipher, $saltedHash, $iv);
    $encryptedData = mcrypt_generic($cipher,$data);
    mcrypt_generic_deinit($cipher);

    $encryptedData = base64_encode($encryptedData);

    $token = array(
        "token" => $encryptedData
    );

    $token_data = json_encode($token);

    echo "
    <br>Here is the token for ".$user_data['first_name'].", ".$user_data['last_name']."
    ".$user_data['email'] ." : 
    " . $token['token'];



    $query = "INSERT INTO another_table 
            (token ,first_name,last_name,email,position) 
            VALUES (:token, :first_name,
            :last_name, :email, :position)"; 


    $statement = $db->prepare($query);
    $statement->bindValue(':token', $token['token']);
    $statement->bindValue(':first_name', $user_data['first_name']);
    $statement->bindValue(':last_name', $user_data['last_name']);
    $statement->bindValue(':email', $user_data['email']);
    $statement->bindValue(':position', $user_data['position']);


    $statement->execute();

}
?>
IncredibleHat
2#
IncredibleHat Reply to 2017-12-07 19:42:49Z

Reducing that code to the basics, you have this:

$query = "SELECT * FROM users_table" ;
$statement = $db->prepare($query);
$rows = $statement->fetchAll(); 
$statement->execute();
while($rows = $statement->fetch()){
    // .. stuff ..
    $query = "INSERT INTO another_table 
            (token ,first_name,last_name,email,position) 
            VALUES (:token, :first_name,
            :last_name, :email, :position)";
    $statement = $db->prepare($query);
    $statement->bindValue(':token', $token['token']);
    $statement->bindValue(':first_name', $user_data['first_name']);
    $statement->bindValue(':last_name', $user_data['last_name']);
    $statement->bindValue(':email', $user_data['email']);
    $statement->bindValue(':position', $user_data['position']);
    $statement->execute();
}

See the problem yet? Ok, here is a hint to fix it:

$query = "SELECT * FROM users_table" ;
$statement = $db->prepare($query);
$statement->execute();
while($row = $statement->fetch()){
    $query = ".. stuff ..";
    $OTHER_statement = $db->prepare($query);
    // .. stuff .. 
    $OTHER_statement->execute();
}

Inside your while loop, you were overwriting the variable $statement with the insert sql. This effectively ended your while loop prematurely. Can also cause some serious unwanted and unexpected issues if that second sql was another SELECT.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.252706 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO