[PDO]
pdo.dsn.mydb="mysql:dbname=testdb;host=localhost"
<?php
$dsn
=
'mydb'
;
$user
=
''
;
$password
=
''
;
$dbh
= new
PDO
(
$dsn
,
$user
,
$password
);
?>
theking2 at king dot ma
¶
1 year ago
A generic pattern to connect to a mariadb or mysql database using a settings file
<?php
$_SETTINGS
=
parse_ini_file
(
'./settings.ini'
,
true
);
$db
= new
\PDO
(
"mysql:hostname=
{
$_SETTINGS
[
'db'
][
'host'
]}
;dbname=
{
$_SETTINGS
[
'db'
][
'name'
]}
"
,
$_SETTINGS
[
'db'
][
'user'
],
$_SETTINGS
[
'db'
][
'pass'
],
[
\PDO
::
ATTR_DEFAULT_FETCH_MODE
=>
\PDO
::
FETCH_ASSOC
,
\PDO
::
MYSQL_ATTR_INIT_COMMAND
=>
"SET NAMES 'utf8mb4'"
]
);
?>
You might not need the options depicted here but I find them convenient.
Works with a settings.ini file containing for instance:
[db]
host = "localhost"
name = "dbname"
user = "dbuser"
pass = "dbpassword"
Francesco Montanari
¶
4 years ago
Most of the information in the comment here is outdated or wrong.
You do can use host=localhost to connect via socket, which is faster than TCP, so setting 127.0.0.1 is a performance loss.
To use proper utf you should use utf8mb4, for example:
$db = new PDO('mysql:host=' . DATABASE_HOST . ';dbname='. DATABASE_NAME .';charset=utf8mb4', DATABASE_USER, DATABASE_PASSWORD);
453034559 at qq dot com
¶
2 years ago
// set DEFAULT_FETCH_MODE
<?php
try {
$p
= new
PDO
(
"mysql:host=
$db_host
;port=
$db_port
;dbname=
$db_dbname
"
,
$db_user
,
$db_pass
,array(
PDO
::
ATTR_DEFAULT_FETCH_MODE
=>
PDO
::
FETCH_ASSOC
,
PDO
::
MYSQL_ATTR_INIT_COMMAND
=>
'SET NAMES utf8'
,
));
} catch (
PDOException $e
) {
print
"Error!: "
.
$e
->
getMessage
() .
"<br/>"
;
die();
}
?>
Michal Stefanak
¶
1 year ago
If you override PDO with own class and you want to implement alias from php.ini, you have to get it with `get_cfg_var` instead of `ini_get`.
<?php
class
PDO
extends
\PDO
{
public function
__construct
(
string $dsn
, ?
string $username
=
null
, ?
string $password
=
null
, ?array
$options
=
null
)
{
if (!
str_contains
(
$dsn
,
':'
)) {
$dsn
=
get_cfg_var
(
'pdo.dsn.'
.
$dsn
);
if (!
$dsn
) {
throw new
PDOException
(
'Argument #1 ($dsn) must be a valid data source name'
);
}
}
parent
::
__construct
(
$dsn
,
$username
,
$password
,
$options
);
}
}
?>
ohcc at 163 dot com
¶
8 years ago
You will get a fatal error if you don't catch the exception threw by PDO when it fails to connect to the database server like this.
Fatal error: in xxx.php on line xx
This error neither can be handled by error handlers nor can it be erased by the @ sign, which can make your script uncontrollable.
<?php
$db
= new
pdo
(
'mysql:host=127.0.0.1;port=3306;dbname=mysql;charset=utf8'
,
'user'
,
'password'
,array(
PDO
::
ATTR_ERRMODE
=>
PDO
::
ERRMODE_EXCEPTION
,
));
?>
You should always try ... catch ...
<?php
try{
$db
= new
pdo
(
'mysql:host=127.0.0.1;port=3306;dbname=mysql;charset=utf8'
,
'user'
,
'password'
,array(
PDO
::
ATTR_ERRMODE
=>
PDO
::
ERRMODE_EXCEPTION
,
));
}catch(
PDOException $pe
){
echo
$pe
->
getMessage
();
}
?>
berk0081 at umn dot edu
¶
9 years ago
Although not explicitly stated, parameters in the PDO $dsn string may be case-sensitive on some platforms or drivers.
<?php
$pdo
= new
PDO
(
"mysql:host=hostname;DBName=database"
,
"user"
,
"password"
);
$pdo
= new
PDO
(
"mysql:host=hostname;dbname=database"
,
"user"
,
"password"
);
?>
srycroft at excelsystems dot com
¶
7 years ago
For IBM i / iSeries / AS400 DB2 connections, you can specify system naming and library lists for php_pdo modules > 1.3.3-sg2
<?php
$pdo
= new
PDO
(
'ibm:'
.
$host
,
$user
,
$password
,
array(
PDO
::
I5_ATTR_DBC_SYS_NAMING
=>
true
,
PDO
::
I5_ATTR_DBC_LIBL
=>
"QGPL QSYS"
)
);
?>
See
http://yips.idevcloud.com/wiki/index.php/XMLSERVICE/PHPPDOChangeLog
max1josef
¶
11 months ago
SQLite3: by default, the database file is created on opening if it does not exist (PDO::SQLITE_OPEN_CREATE).
<?php
$dsn
=
'sqlite:D:\Databases\non_existing.db'
;
try {
$dbh
= new
PDO
(
$dsn
);
}
catch(
PDOException $e
) {
print
$e
->
getMessage
();
}
?>
No Exception is thrown, instead file "non_existing.db" will be created. To avoid this, use:
<?php
$dsn
=
'sqlite:D:\Databases\non_existing.db'
;
$options
= [
PDO
::
SQLITE_ATTR_OPEN_FLAGS
=>
PDO
::
SQLITE_OPEN_READWRITE
,
];
try {
$dbh
= new
PDO
(
'sqlite:D:\Databases\non_existing.db'
,
null
,
null
,
$options
);
}
catch(
PDOException $e
) {
print
$e
->
getMessage
();
}
?>
This will ouput:
SQLSTATE[HY000] [14] unable to open database file
If you want to open the database read-only, use PDO::SQLITE_OPEN_READONLY.
olivernybroe at gmail dot com
¶
8 years ago
The PDO connection is case-sensitive, this means that you cannot write
`$PDO = new PDO("MySQL:DBName=dbname;host=localhost");`
You would have to write it
`$PDO = new PDO("mysql:dbname=dbname;host=localhost");`
The difference here is that `mysql` and `dbname` is with all lower-case.
Some IDE's like PHPStorm will show a `TYPO ERROR`, at `dbname` if it's written with lower-case only, this is just to be ignored and have been reported to PHPStorm for them to fix. (Currrent version 10.0.2)
