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.
/*** 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
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 />';
}
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 />';
}
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...
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();
}?>
$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: