How to change an update query from mysql to pdo?

Question

I am using the bellow query to update but now I tried to change it to pdo and it failed to work Please any help will be appreciated

 function updateonlinesession(){            
    if(isset($_SESSION['username']['id'])){
    $uid = $_SESSION['username']['id'];
    $page = $_SERVER['REQUEST_URI'];
    $ip = $_SERVER['REMOTE_ADDR'];
    $username = $_SESSION['logged'];

    mysql_query("UPDATE site_user SET dateupdated  = now(),ip = '$ip' WHERE 
    username = '".mysql_real_escape_string($_SESSION['username'])."'"); 
    }

    }

Here is what I tried with pdo

function updateonlinesession(){         
if(isset($_SESSION['username']['id'])){
$uid = $_SESSION['username']['id'];
$page = $_SERVER['REQUEST_URI'];
$ip = $_SERVER['REMOTE_ADDR'];
$username = $_SESSION['logged'];

$update = ("UPDATE site_user SET dateupdated  = now(),ip = '$ip' WHERE 
username = '".($_SESSION['username'])."'");
$sth_update= $con->prepare($update);
$sth_update->execute();
}

}

?>

have a gander: http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers — Nick, Feb 26 '13 at 15:43
this thread talks about `sql injection` but it shows the ways to use prepared statements and one of these is PDO, [How to prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php) — John Woo, Feb 26 '13 at 15:43
Why is this upvoted while the OP hasn't provided any prove of effort? Besides there are tons of articles/answers that covers this topic. It seems to me like `give me the code` — HamZa, Feb 26 '13 at 15:45
@HamZa DzCyberDeV I put it chaeck now I edited after Mr Chris and Eggplant requested — Humphrey, Feb 26 '13 at 15:47
@HamZa DzCyberDeV I suspect the upvotes are being done in the hope that the question gains some visibility and serves as an example for novice PHP developers. Anything to stop them using the mysql extension! — Ken Keenan, Feb 26 '13 at 15:51

score 5 · Answer 1 · answered Feb 26 '13 at 15:45

// connetion
$conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);

// query
$sql = "UPDATE site_user SET dateupdated = NOW(), ip = ?
        WHERE username = ?";
$q = $conn->prepare($sql);
$q->execute(array($ip, $_SESSION['username']);

score 5 · Answer 2 · answered Feb 26 '13 at 15:46

First, connect somewhere in a bootstrap/config file:

$dsn = 'mysql:dbname=test;host=localhost;charset=utf8';
$opt = array(
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
);
$pdo = new PDO($dsn, $user, $pass, $opt);

then, run your query

function updateonlinesession(){
    global $pdo;
    if(isset($_SESSION['username']['id'])){
        $sql = "UPDATE site_user SET dateupdated=now(),ip=? WHERE username=?"; 
        $pdo->prepare($sql);
        $pdo->execute(array($_SERVER['REMOTE_ADDR'], $_SESSION['logged']));
    }
}

Make sure that all variables you're using have proper values (var_dump($_SESSION) is enough)

you really a star u always help me Great Man – Humphrey Feb 26 '13 at 15:53 — Humphrey, Feb 26 '13 at 15:53

score 3 · Accepted Answer · answered Feb 26 '13 at 15:47

3

$stmt = $db->prepare("UPDATE site_user SET dateupdated=now(), ip=? WHERE username=?");
$stmt->execute(array($ip, $_SESSION['username']));
$affected_rows = $stmt->rowCount();

where $db is your connection

again: A great tutorial

answered Feb 26 '13 at 15:47

Nick

171
10

No problem, good luck on making the switch, believe me its worth the effort in learning PDO. and that tutorial helps a lot. – Nick Feb 26 '13 at 15:52

score 2 · Answer 4 · answered Feb 26 '13 at 15:46

function updateonlinesession(){
    if(isset($_SESSION['username']['id'])){
        $uid = $_SESSION['username']['id'];
        $page = $_SERVER['REQUEST_URI'];
        $ip = $_SERVER['REMOTE_ADDR'];
        $username = $_SESSION['logged'];

        //Establish your PDO object.
        $pdo = new PDO("mysql:host=localhost;dbname=mydb", "username", "password");

        //Prepare your statement.
        $stmt = $pdo->prepare("UPDATE site_user SET dateupdate = now(), ip = ? WHERE username = ?");
        $stmt->execute(array(
            $ip,
            $_SESSION['username']
        ));
        $stmt->closeCursor();
    }
}

How to change an update query from mysql to pdo?

4 Answers4

Linked