Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysql pdo transaction and session storage

I have trouble deciding what would be the optimal solution for my web app, which is accessing (mainly reading) many times the same user data on every session.

Should I retrieve all user data at once (about 40 fields) to $_SESSION when opening a new session or should I keep a persistent PDO (mysql) connection and query just the parameters I need from the database on every script execution instead?

ALSO:

Would there be a big difference in performance between reading/updating a lot of fields at once (whith a custom query) or one by one (with a custom combination of generic queries) in the same transaction? e.g.

$dbh = new PDO("mysql:host=localhost;dbname",$dbuser,$dbpass,array(PDO::ATTR_PERSISTENT => true));

$fieldlist='';
foreach ($fields as $i=>$field){
    $fieldlist.=$field['name'].':field'.$i.',';
}
rtrim($fieldlist,',');
$dbh->prepare("UPDATE user SET ".$fieldlist." WHERE name=:name");
foreach ($fields as $i=>$field){
    $stmt->bindValue(':field'.$i, $field['value'], PDO::PARAM_STR);
}
$stmt->bindValue(':name', $name, PDO::PARAM_STR);
$stmt->execute();

$dbh = null;

vs.

$dbh = new PDO("mysql:host=localhost;dbname",$dbuser,$dbpass,array(PDO::ATTR_PERSISTENT => true));


$dbh->beginTransaction();

foreach($fields as $field){
    $stmt=$dbh->prepare("UPDATE user SET ".$field['name']."=:field WHERE name=:name");
    $stmt->bindValue(':field', $field['value'], PDO::PARAM_STR);
    $stmt->bindValue(':name', $name, PDO::PARAM_STR);
    $stmt->execute();
}

$dbh->commit();

$dbh = null;
like image 740
NotGaeL Avatar asked Oct 21 '22 21:10

NotGaeL


1 Answers

Would there be a big difference in performance

I would say there is a difference in common sense.
Why to repeat one by one while you can do it at once? Is there any reason to write more code for the same task?
It seems you're looking for troubles not where they really are.

like image 186
Your Common Sense Avatar answered Nov 02 '22 22:11

Your Common Sense