Continuing from this topic where we explained most problems with PDO How to successfully rewrite old mysql-php code with deprecated mysql_* functions? now about understanding prepared statements... So in order to get remove mysql_* strings there are some examples so my question for all and other users may this find helpfull which solution is the best ... so example of old "made up* code:
in config.php:
$db = new dbConn('127.0.0.1', 'root', 'pass', 'people', 'login');
in login.php
$db->selectDb("login");
$query = mysql_query("SELECT * FROM account WHERE id='".$_session["id"]."' LIMIT 1");
$result = mysql_fetch_array($query);
$_session["id"] is defined when login actually, so now we have several options to do so:
In config.php:
$db_people = new PDO('mysql:host=127.0.0.1;dbname=people;charset=UTF-8', 'root', 'pass');
$db_login = new PDO('mysql:host=127.0.0.1;dbname=login;charset=UTF-8', 'root', 'pass');
And in login.php 1):
$stmt = $db_login->prepare("SELECT * FROM account WHERE id=? LIMIT 1");
$stmt->execute(array($_session["id"]));
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
Or this one is better when exclude query? Or the previous one is better?
And in login.php 2):
$query = "SELECT * FROM account WHERE id=? LIMIT 1";
$parameters = array($_session["id"]);
$statement = $db_login->prepare($query);
$statement->execute($parameters);
$results = $statement->fetch(PDO::FETCH_ASSOC);
And this login form:
public function login($user, $password)
{
global $web, $db;
if (!empty($user) && !empty($password))
{
$user = $web->esc($user);
$password = $web->doHash($user, $password);
$db->selectDb('login');
$qw = mysql_query("SELECT * FROM account WHERE username='".$user."' AND pass_hash='".$password."'");
if (mysql_num_rows($qw) > 0)
{
$result = mysql_fetch_array($qw);
$_session['name'] = $result['username'];
$_session['id'] = $result['id'];
return true;
}
else
return false;
}
else
return false;
}
Transfered into this form:
public function login($user, $password)
{
global $web, $db_login;
if (!empty($user) && !empty($password))
{
$user = $web->esc($user);
$password = $web->doHash($user, $password);
$stmt = $db_login->prepare("SELECT * FROM account WHERE username=? AND pass_hash=?");
$stmt->execute(array($user, $password));
$rows = $stmt->rowCount();
if ($rows > 0)
{
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
$_session['name'] = $result['username'];
$_session['id'] = $result['id'];
return true;
}
else
return false;
}
else
return false;
}
Is it ok or again do separate query or maybe do it in complete different way? Thank you all.
Also when there is multiple stmt should I use different name for it? For example I use stmt once and make a result1 after I do stmt second with result2 should I choose different name also for stmt variable or only result name is ok to be different?
