Php Database Connection

Php Database Connection

1. Mysql

2. MSQLI

3.PDO

Mysql

 

harshit.info

 

 

When you install PHP, you can select from a number of extensions. To install support for MySQL, click the Extension node in the installer to open that node and select the MySQL node to install that extension. Your PHP installation may have already been installed with MySQL support – most installation on web server are.

database

 

The MySQL support in PHP consist of the number of function of function you can call to interact with MySQL, and here they are :

Accessing a Database using Following pree Define Function in PHP

  • mysql_affected_rows Get the number of rows affected by previous MySQL operation.
  • mysql_change_user Change the logged-in user.
  • mysql_clint_encoding Return the name of the current character set.
  • mysql_close Close a MySQL connection.
  • mysql_connect Connection to a MySQL database.
  • mysql_create_db Create a MySQL database.
  • mysql_data_seek Seek data in database.
  • mysql_db_name Get the name of the database.
  • mysql_db_query Send the MySQL query.
  • mysql_droup_db Droup ( that is, delete) a MySQL database.
  • mysql_error Returen the text of the error message from the previous MySQL operation.
  • mysql_fetch_array its fetch a result
  • mysql_fetch_assoc Fetch a result row as an associative array.
  • mysql_fetch_row Get a result as a enumerated array.
  • mysql_field_len Return the length of a given field.
  • mysql_field_name Get the name of the given field in a result.
  • mysql_field_seek Seek to a given field offset.
  • mysql_field_table Get the name of the table the given field is in.
  • mysql_field_type Get the type of the given field ina result.
  • mysql_get_server_info Get the MySQL server information.
  • mysql_info Get the information about the most recent query
  • mysql_select_dbs List database ablavle on MySQL server.
  • mysql_list_fields List MySQL table field
  • mysql_list_tables List the tables in a MySQL database.
  • mysql_list_num_fields Get the number of field in result.
  • mysql_num_row Get the number of rows in result.
  • mysql_pconnect Open a persist connection to MySQL server.
  • mysql_query Send a MySQL query.
  • mysql_result Get result data.
  • mysql_select_db Select MySQL database.
  • mysql_tablename Get the name of field.

We are going to put these function to work in this Technorishi Tutorial. For example, you might put together an example, phpdatatable.php, which read in and display the fruit table from the produce database.

 

phpmysql-ch4-fig1

 

How to Connect Database In PHP

Line by line explanation

PHP connection to database using connection objects. To create a connection objects for MySQL, use mysql_connect:

mysql_connect ( [ server [, username [, password [, new_link , clint_flags ]]]])

Here,server is mysql server, which can be URLS, port numbers, and so on. The username and password arguments are the mysql username or password.

The new_link argument, if set to TURE, force PHP to establish a new link to the database, even if it already has such a link. Otherwise, if you try open a second link to the database by called mysql_connect a second time, PHP may use it already-established link instead.

 

The clint_flag parameter can be a combination ( create by origin values together with the OR operator, | ) of the following constant :

MYSQL_CLEINT_SSL, MYSQL_CLIENT_COMPRESS, MYSQL_CLIENT_INORE_SPACE, or MYSQL_CLIENT_INERACTIVE.

The mysql_connect function return a connection object if successful, FALSE otherwise. Okay, lets connect to MySQL from PHP suing mysql_connect in this case, MySQL and PHP are on the same machine, so the server is just “localhost“.

Here’s how to create the connection object (fill in your own username and password of course);

<?php

$connection = mysql_connect ( “localhost”, “ root ”, “ ”)

 

Let’s argument this a little, making is display a message if there was an error, and quit, using the die function;

<?php

$connection = mysql_connect ( “ localhost ”, “ root ”, “ ”)

Or die (“ couldn’t connect database “);



?>

 

Connecting to the database

Okay, we are connected to mysql; the next step is to select the database you want to use you select the database with the mysql_select_db, which works like this

mysql_select_db ( database_name [, link_indetifer ] )

Here databse_name is the name of database which is “produce “ here, and link_indentifier is the connection object. Here how to put mysql_select_db to work in phpdatabasetable.php:

<?php

$connection = mysql_connect ( “ localhost “, “ root “ , “ “ )

$db = mysql_select_db (“produce”, connection)

Or die (“couldn’t select database “);

.
?>

 

Great you are connected to the database server and you select the database you want to work with. Selecting the database is crucial – if you don’t select database before proceeding, you will get error.

<?php

$a = mysql_connect('localhost','root','');

if(!$a)
{
    mysql_error();
}
mysql_select_db("database_name");

?>

2. PDO Connection

Each RDBMS needs a separate extension. Each extension has a different interface ext/dbxis an inefficient abstraction Multiple PEAR solutions,  Abstraction layers, Query builders, Data Access Objects, Nested Set support, But there is ‘no’OO in PHP 4. PHP can connect to all important RDBMS PDO provides a unified efficient abstraction. PHP is ready for UML Specialized extensions allow detailed control Multiple PEAR solutions More sophisticated abstraction layers Query builders Data Access Objects . . . Nested Set support Multiple ways of using databases with PHP File based as ext/dbaor ext/sqliteor embedded MySQL Talking SQL with embedded RDBMS Talking SQL with external RDBMS Using ODBC.

PDO at a glance

Data access abstraction (API unification) Multiple database, plug-in extensions, Object oriented, Iterator support, Destructive read support, All written in a tiny c layer, Will be used us base layer of upcoming MDB2, Available through PECL, Buildablefor PHP 5.0
Built-in starting from 5.1, Windows DLLs available Already used in a few production servers, ATM still marked experimental, Marcus Börger Advanced Object Oriented Database access using PDO 12, PDO at a glance

Prepared statements (unified, name and index), SQL state error code, Portability attributes, Transaction supprt, Scrollable cursors,Uses normal PHP error facilities or Exceptions Plans:, LOB suppor,

Connecting to the database
PDO uses DSNsto connect
<handler-name> ':' <native-DSN> 
try {
$dbh= new PDO($dsn, $user, $password, $options);
//
// Use the database
//
// and close it
$dbh= NULL;
} catch (PDOException$e) {
echo "Failed to connect:". $e->getMessage();
}

PHP 4 and Databases

PHP can connect to all important RDBMS
Each RDBMS needs a separate extension
Each extension has a different interface
ext/dbxis an inefficient abstraction

Multiple PEAR solutions
Abstraction layers
Query builders
Data Access Objects . . . Nested Set support
But there is ‘no’OO in PHP 4

PHP 5 and Databases

PHP can connect to all important RDBMS
PDO provides a unified efficient abstraction
PHP is ready for UML
Specialized extensions allow detailed control
Multiple PEAR solutions
More sophisticated abstraction layers
Query builders
Data Access Objects . . . Nested Set support

Multiple ways of using databases with PHP
File based as ext/dbaor ext/sqliteor embedded MySQL
Talking SQL with embedded RDBMS
Talking SQL with external RDBMS
Using ODBC

PDO at a glance
Data access abstraction (API unification)
Multiple database plug-in extensions
Object oriented
Iterator support
Destructive read support
All written in a tiny c layer
Will be used us base layer of upcoming MDB2

Available through PECL
Buildablefor PHP 5.0
Built-in starting from 5.1
Windows DLLs available
Already used in a few production servers
ATM still marked experimental
Marcus Börger Advanced Object Oriented Database access using PDO 12
PDO at a glance
Prepared statements (unified, name and index)
SQL state error code
Portability attributes
Transaction supprt
Scrollable cursors
Uses normal PHP error facilities or Exceptions
Plans:
LOB support

Connecting to the database

PDO uses DSNsto connect
<handler-name> ':' <native-DSN>
try {
$dbh= new PDO($dsn, $user, $password, $options);
//
// Use the database
//
// and close it
$dbh= NULL;
} catch (PDOException$e) {
echo "Failed to connect:". $e->getMessage();
}

PDO DSN format

odbc:odbc_dsn
mysql:host=name;dbname=dbname
sqlite:/path/to/db/file
sqlite::memory:
sqlite2:/path/to/sqlite2/file
pgsql:host=localhostport=5432dbname=test
oci:dbname=dbname;charset=charset
firebird:dbname=db;charset=charset;role=role

Direct SQL execution

PDO::exec() allows to avoid PDOStatementobject
Most usefullfor DDL (i.e. CREATE) and INSETR, UPDATE

$dbh= new PDO($dsn);
$cnt= $dbh->exec($sql);
if ($cnt!== false) {
echo "Rows affected: ". $cnt;
echo"Last inserted id: ". $dbh->lastInsertId();
} else {
echo "Error";
}

Fetching data with prepare

The default fetch methodology is unbuffered
Uses methods prepare()andexecute()
Forward only
Row count unknown

$dbh= new PDO($dsn);
$stmt= $dbh->prepare("SELECT* FROM FOO");
$stmt->execute();
while ($row= $stmt->fetch()) {
// use data in $row
}
$stmt= null;

Fetching data w/o prepare

Uses method query()
Forward only
Row count unknown

$dbh= new PDO($dsn);
$stmt= $dbh->query("SELECT* FROM FOO");
$stmt->execute();
while ($row= $stmt->fetch()) {
// use data in $row
}
$stmt= null;

Fetching data from iterator

Faster data access
Works with and without preparation
Forward only
Row count not available

$dbh= new PDO($dsn);
$stmt= $dbh->prepare("SELECT* FROM FOO");
$stmt->execute();
foreach($stmtas $row) {
// use data in $row
}
$stmt= null;
foreach($dbh->query("SELECT* FROM bar") as $row) {
// use data in $row
}

Fetching data into array

Data is fully buffered
Works with and without preparation
Randamaccess
Row count available
Usefullif database doesn't support parallel queries

$dbh= new PDO($dsn);
$stmt= $dbh->prepare("SELECT* FROM FOO");
$stmt->execute();
$data= $stmt->fetchAll();
foreach($dataas $row) {
// use data in $row
}
$stmt= null;

How to retrieve data

Fetch single dataset in default way

mixed PDOStatement::fetch(
int$mode=PDO_FETCH_BOTH,
int$orientation=PDO_FETCH_ORI_NEXT,
int$offset=0)
also controlled by
void PDOStatement::setFetchMode(
int$mode,// PDO_FETCH_*
[mixed*$params])// mode specific params

Fetch single column value

mixed PDOStatement::fetchColumn(
int$column_number=0) // zero based index

Fetch all rows at once

array PDOStatement::fetchAll(
int$mode=PDO_FETCH_BOTH,
string$class_name=NULL,
array$ctor_args=NULL)

Fetch single row as object

mixed PDOStatement::fetchObject(
string$class_name=NULL,
array$ctor_args=NULL)

Fetch modes and flags

Modes

[su_table]

PDO_FETCH_ASSOC associative array
PDO_FETCH_NUM numeric array
PDO_FETCH_BOTH default(assoc/numeric)
PDO_FETCH_OBJ into stdClass object
PDO_FETCH_BOUND into bound variables
PDO_FETCH_COLUMN single column
PDO_FETCH_CLASS into new instance
PDO_FETCH_INTO into existing object
PDO_FETCH_FUNC through function call

[/su_table]

Flags

[su_table]

PDO_FETCH_GROUP group by first col
PDO_FETCH_UNIQUE group unique by first col
PDO_FETCH_CLASSTYPE use classname in row
PDO_FETCH_SERIALIZE use serialization
   

[/su_table]

PDO_FETCH_BOUND

Fetching returns true until there is no more data
Binding parameters by "?" in sql(1 based index)
Binding parameters by ":name" in sql
Binding columns by name and index

$dbh= new PDO($dsn);
$stmt= $dbh->prepare(
'SELECT urlFROM urlsWHERE key=:urlkey');
$stmt->bindParam(':urlkey', $urlkey);
$stmt->bindColumn('url', $href);
$urlkey= ...; // get urlkey to translate
$stmt->execute(); // execute the query
// fetch data
$stmt->fetch(PDO_FETCH_BOUND);
// use data
echo '<a href="'. $href. '">'. $urlkey. '</a>';

PDO_FETCH_BOUND

Fetching returns true until there is no more data
Binding parameters by "?" in sql1 based index
Binding parameters by ":name" in sql
Binding columns by name and index
Binding can be done on execute()

$dbh= new PDO($dsn);
$stmt= $dbh->prepare(
'SELECT urlFROM urlsWHERE key=:urlkey');
$urlkey= ...; // get urlkey to translate
$stmt->execute(array(':urlkey' => $urlkey),
array('url' => $href));
// fetch data
$stmt->fetch(PDO_FETCH_BOUND);
// use data
echo '<a href="'. $href. '">'. $urlkey. '</a>';

PDO_FETCH_CLASS

Lets you specify the class to instantiate
PDO_FETCH_OBJ always uses stdClass
Writes data before calling __construct
Can write private/protected members

Lets you call the constructor with parameters

class Person{
protected $dbh, $fname, $lname;
function __construct($dbh) {
$this->dbh= $dbh;
}
function __toString() {
return $this->fname. " ". $this->lname;
}
}
$stmt= $dbh->prepare('SELECTfname, lnameFROM persons');
$stmt->setFetchMode(PDO_FETCH_CLASS, 'Person', array($dbh));
$stmt->execute();
foreach($stmtas $person) {
echo $person;
}

PDO_FETCH_CLASSTYPE

Lets you fetch the class to instantiate from rows
Must be used with PDO_FETCH_CLASS
The class name specified in fetch mode is a fallback

class Person{ /* … */}
class Employeeextends Person{ /* … */}
class Managerextends Employee{ /* … */}

class Managerextends Employee{ /* ... */}
$stmt= $dbh->prepare(
'SELECT class, fname, lnameFROM persons LEFT JOIN classes ON persons.kind= classes.id');
$stmt->setFetchMode(PDO_FETCH_CLASS|PDO_FETCH_CLASSTYPE, 'Person', array($dbh));
$stmt->execute();
foreach($stmtas $person) {
echo $person;
}

PDO_FETCH_INTO

Lets you reuse an already instantiated object

Does not allow to read into protected or private
Because the constructor was already executed

class Person{
public$dbh, $fname, $lname;
function __construct($dbh) {
$this->dbh= $dbh;
}
function __toString() {
return $this->fname. " ". $this->lname;
}
}
$stmt= $dbh->prepare('SELECTfname, lnameFROM persons');
$stmt->setFetchMode(PDO_FETCH_INTO, new Person($dbh));
$stmt->execute();
foreach($stmtas $person) {
echo $person;
}

PDO_FETCH_FUNC

Lets you specify a function to execute on each row

class Person{
protected $fname, $lname;
static function Factory($fname,$lname) {
$obj= new Person;
$obj->fname= $fname;
$obj->lname= $lname;
}
function __toString() {
return $this->fname. " ". $this->lname;
}
}
$stmt= $dbh->prepare('SELECTfname, lnameFROM persons');
$stmt->setFetchMode(PDO_FETCH_FUNC,
array('Person', 'Factory'));
$stmt->execute();
foreach($stmtas $person) {
echo $person;
}

PDOStatementas real iterator

PDOStatementonly implements Traversable
Wrapper IteratorIteratortakes a Traverable

$it= new IteratorIterator($stmt);

Now the fun begins
Just plug this into any other iterator
Recursion, SQL external unions, Filters, Limit, …

foreach(newLimitIterator($it, 10) as $data) {
var_dump($data);
}

Deriving PDOStatement
prepare() allows to specify fetch attributes

PDOStatementPDO::prepare(
string$sql,
array(PDO_ATTR_STATEMENT_CLASS=>
array(stringclassname,
array(mixed* ctor_args))));
classMyPDOStatementextends PDOStatement{
protected$dbh;
function__construct($dbh) {
$this->dbh= $dbh;
}
}
$dbh->prepare($sql,
array(PDO_ATTR_STATEMENT_CLASS=>
array('MyPDOStatement', array($dbh))));

prepare() allows to specify fetch attributes

PDOStatementPDO::prepare(
string$sql,
array(PDO_ATTR_STATEMENT_CLASS=>
array(stringclassname,
array(mixed* ctor_args))));
classMyPDOStatementextends PDOStatement{
protected$dbh;
function__construct($dbh) {
$this->dbh= $dbh;
}
}
$dbh->prepare($sql,
array(PDO_ATTR_STATEMENT_CLASS=>
array('MyPDOStatement', array($dbh))));

Deriving allows to convert to real iterator

class PDOStatementAggregateextends PDOStatementimplements IteratorAggregate
{
private function __construct($dbh, $classtype) {
$this->dbh= $dbh;
$this->setFetchMode(PDO_FETCH_CLASS,$classtype, array($this));
}
function getIterator(){
$this->execute();
return new IteratorIterator($this, 'PDOStatement'); /* Need to be base class */
}
}
$stmt= $dbh->prepare('SELECT* FROM Persons', array(PDO_ATTR_STATEMENT_CLASS=> array('PDOStatementAggregate',
array($dbh, 'Person'))));
foreach($stmtas $person){
echo $person;
}

PDO error modes

PDO offers 3 different error modes
$dbh->setAttribute(PDO_ATTR_ERRMODE, $mode);

PDO_ERRMODE_SILENT
Simply ignore any errors
PDO_ERRMODE_WARNING
Issue errors as standard phpwarnings
PDO_ERRMODE_EXCEPTION
Throw exception on errors
Map native codes to SQLSTATE standard codes
Aditionallyoffers native info

Performance

10 times Querying 10 rows

Iterators vs. Arrays
Implemented as engine feature: 56%
Building an Array is expensive

queryArrayvs. query and fetchArray: 89%
Function calls are expensive

Buffered vs. Unbuffered: up to 60%
Buffered queries need to build a hash table
Buffered queries must copy data
Unbufferedqueries can use destructivereads
Copying data is expensive

Comparing OO vs. Procedural code
PC is easy to program?
PC uses resources: O(n*log(n))
PC uses a single function table: 2000 … 4000
OO code is little bit more to learn
OO code is easy to maintain
OO code uses object storage: O(n+c)
OO uses small method tables: 10 … 100

2 thoughts on “Php Database Connection”

  1. Thanks for finally writing about >Php Database Connection 1.MYSQL- 2.MSQLI – 3.PDO | Harshit.info <Liked it!

  2. I am now not certain where you’re getting your info, however great topic.

    I needs to spend a while studying much more or figuring out more.

    Thank you for excellent information I was looking for this information for my mission.

Leave a Reply

Your email address will not be published. Required fields are marked *