Using PDO in MYSQL PHP project

PDO stands for PHP data Objects.PDO is a PHP extension to formalise PHP's database connections by creating a uniform interface. This allows developers to create code which is portable across many databases and platforms.
PDO supports many of the popular databases as seen on the list below.

• DBLIB: FreeTDS / Microsoft SQL Server / Sybase
• Firebird (http://firebird.sourceforge.net/): Firebird/Interbase 6
• IBM (IBM DB2)
• INFORMIX - IBM Informix Dynamic Server
• MYSQL (http://www.mysql.com/): MySQL 3.x/4.0
• OCI (http://www.oracle.com): Oracle Call Interface
• ODBC: ODBC v3 (IBM DB2 and unixODBC)
• PGSQL (http://www.postgresql.org/): PostgreSQL
• SQLITE (http://sqlite.org/): SQLite 3.x

In this Tutorial I will explain about PDO and its usage in MYSQL.

1. Connection with database

<?php
/*** mysql hostname ***/$hostname = 'localhost';

$dbname= 'mytable';/*** mysql username ***/$username = 'username';
/*** mysql password ***/$password = 'password';

try {
    $dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
    /*** echo a message saying we have connected ***/
    echo 'Connected to database';
    }
catch(PDOException $e)
    {
    echo $e->getMessage();
    }?>

2. For Closing database connection

Just set the connection variable to null

 $dbh = null;

3. For Executing Query statements like INSERT,UPDATE,DELETE

<?php  

//Insert query  

$query="INSERT INTO tablename(fieldname, fieldname) VALUES ('value1', 'value2')";

//Update query

$query="UPDATE tablename set fieldname='value' where condition";

//Delete query

$query="DELETE FROM table where condition;

 $dbh->exec($query);

    /*** echo the number of affected rows ***/
?>

4. Executing query statements like SELECT

/*** The SQL SELECT statement ***/
    $sql = "SELECT * FROM animals";
    foreach ($dbh->query($sql) as $row)
        {
        print $row['value1'] .' - '. $row['value2'] . '<br />';
        }


FETCH Modes

• FETCH ASSOC

To fetch an associative array from our results the constant PDO::FETCH_ASSOC is used and returns the column names as indexes or keys of the resulting array

$sql = "SELECT * FROM tablename";

    /*** fetch into an PDOStatement object ***/
    $stmt = $dbh->query($sql);

    /*** echo number of columns ***/
    $result = $stmt->fetch(PDO::FETCH_ASSOC);

    /*** loop over the object directly ***/
    foreach($result as $key=>$val)
    {
    echo $key.' - '.$val.'<br />';
    }

• FETCH NUM

Like PDO::FETCH_ASSOC, the PDO::FETCH_NUM produces a numerical index of the result set rather than the field names.


/*** The SQL SELECT statement ***/
    $sql = "SELECT * FROM tablename";

    /*** fetch into an PDOStatement object ***/
    $stmt = $dbh->query($sql);

    /*** echo number of columns ***/
    $result = $stmt->fetch(PDO::FETCH_NUM);

    /*** loop over the object directly ***/
    foreach($result as $key=>$val)
    {
    echo $key.' - '.$val.'<br />';
    }

• FETCH BOTH

There may be times you need to fetch both numerical and associative indexes. PDO::FETCH_BOTH produces a numerical and associative index of the result set so you can use either, or both.


/*** The SQL SELECT statement ***/
    $sql = "SELECT * FROM tablename";

    /*** fetch into an PDOStatement object ***/
    $stmt = $dbh->query($sql);

    /*** echo number of columns ***/
    $result = $stmt->fetch(PDO::FETCH_BOTH);

    /*** loop over the object directly ***/
    foreach($result as $key=>$val)
    {
    echo $key.' - '.$val.'<br />';
    }

5. For getting last insert Id


    $dbh->exec("INSERT INTO tablename(fieldname,fieldname) VALUES ('value1', 'value2')");

    /*** display the id of the last INSERT ***/
    echo $dbh->lastInsertId();


I have covered only basic things about PDO. Hope that this post is helpful to some people...