Thursday 16 February 2017

Use 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...

0 comments: