Php pdo mysql connection error

Connections and Connection management

Connections are established by creating instances of the PDO base class. It doesn’t matter which driver you want to use; you always use the PDO class name. The constructor accepts parameters for specifying the database source (known as the DSN) and optionally for the username and password (if any).

Example #1 Connecting to MySQL

If there are any connection errors, a PDOException object will be thrown. You may catch the exception if you want to handle the error condition, or you may opt to leave it for an application global exception handler that you set up via set_exception_handler() .

Example #2 Handling connection errors

try $dbh = new PDO ( ‘mysql:host=localhost;dbname=test’ , $user , $pass );
foreach( $dbh -> query ( ‘SELECT * from FOO’ ) as $row ) print_r ( $row );
>
$dbh = null ;
> catch ( PDOException $e ) print «Error!: » . $e -> getMessage () . «
» ;
die();
>
?>

If your application does not catch the exception thrown from the PDO constructor, the default action taken by the zend engine is to terminate the script and display a back trace. This back trace will likely reveal the full database connection details, including the username and password. It is your responsibility to catch this exception, either explicitly (via a catch statement) or implicitly via set_exception_handler() .

Upon successful connection to the database, an instance of the PDO class is returned to your script. The connection remains active for the lifetime of that PDO object. To close the connection, you need to destroy the object by ensuring that all remaining references to it are deleted—you do this by assigning null to the variable that holds the object. If you don’t do this explicitly, PHP will automatically close the connection when your script ends.

Note: If there are still other references to this PDO instance (such as from a PDOStatement instance, or from other variables referencing the same PDO instance), these have to be removed also (for instance, by assigning null to the variable that references the PDOStatement).

Example #3 Closing a connection

$dbh = new PDO ( ‘mysql:host=localhost;dbname=test’ , $user , $pass );
// use the connection here
$sth = $dbh -> query ( ‘SELECT * FROM foo’ );

// and now we’re done; close it
$sth = null ;
$dbh = null ;
?>

Many web applications will benefit from making persistent connections to database servers. Persistent connections are not closed at the end of the script, but are cached and re-used when another script requests a connection using the same credentials. The persistent connection cache allows you to avoid the overhead of establishing a new connection every time a script needs to talk to a database, resulting in a faster web application.

Example #4 Persistent connections

$dbh = new PDO ( ‘mysql:host=localhost;dbname=test’ , $user , $pass , array(
PDO :: ATTR_PERSISTENT => true
));
?>

The value of the PDO::ATTR_PERSISTENT option is converted to bool (enable/disable persistent connections), unless it is a non-numeric string , in which case it allows to use multiple persistent connection pools. This is useful if different connections use incompatible settings, for instance, different values of PDO::MYSQL_ATTR_USE_BUFFERED_QUERY .

Note:

If you wish to use persistent connections, you must set PDO::ATTR_PERSISTENT in the array of driver options passed to the PDO constructor. If setting this attribute with PDO::setAttribute() after instantiation of the object, the driver will not use persistent connections.

Note:

If you’re using the PDO ODBC driver and your ODBC libraries support ODBC Connection Pooling (unixODBC and Windows are two that do; there may be more), then it’s recommended that you don’t use persistent PDO connections, and instead leave the connection caching to the ODBC Connection Pooling layer. The ODBC Connection Pool is shared with other modules in the process; if PDO is told to cache the connection, then that connection would never be returned to the ODBC connection pool, resulting in additional connections being created to service those other modules.

User Contributed Notes

• PDO
  • Introduction
  • Installing/Configuring
  • Predefined Constants
  • Connections and Connection management
  • Transactions and auto-​commit
  • Prepared statements and stored procedures
  • Errors and error handling
  • Large Objects (LOBs)
  • PDO
  • PDOStatement
  • PDOException
  • PDO Drivers

  Источник

  Connecting to MySQL

  Summary: in this tutorial, you’ll learn step by step how to connect to a MySQL database from PHP using PDO.

  Prerequisites

  Before connecting to a MySQL database server, you need to have:

  • A MySQL database server, a database, and an account that has access to the database.
  • PDO MySQL driver enabled in the php.ini file

  1) Setting MySQL database parameters

  Suppose you have a local MySQL database server that has the following information:

  • The host is localhost .
  • The bookdb database on the local database server.
  • The account with the user root and password ‘S@cr@t1!’ that can access the bookdb database.

  In PHP, you can create a config.php file and place the database parameters:

   $host = 'localhost'; $db = 'bookdb'; $user = 'root'; $password = 'S@cr@t1!';Code language: HTML, XML (xml)

  To use the database parameters, you can include the config.php file using the require construct:

   require 'config.php';Code language: HTML, XML (xml)

  2) Enable PDO_MySQL Driver

  PDO_MYSQL is a driver that implements the PDO interface. PDO uses the PDO_MYSQL driver to connect to a MySQL database.

  To check if the PDO_MYSQL driver is enabled, you open the php.ini file. The php.ini file is often located under the php directory. For example, you can find the php.ini file under the C:\xampp\php directory if you use XAMPP on Windows.

  The following shows the extension line in the php.ini file:

  ;extension=php_pdo_mysql.dll

  To enable the extension, you need to uncomment it by removing the semicolon ( ; ) from the beginning of the line like this:

  extension=php_pdo_mysql.dll

  After that, you need to restart the web server for the change to take effect.

  MySQL data source name

  PDO uses a data source name (DSN) that contains the following information:

  • The database server host
  • The database name
  • The user
  • The password
  • and other parameters such as character sets, etc.

  PDO uses this information to make a connection to the database server. To connect to the MySQL database server, you use the following data source name format:

  "mysql:host=host_name;dbname=db_name;charset=UTF8"Code language: JSON / JSON with Comments (json)

  $dsn = "mysql:host=localhost;dbname=bookdb;charset=UTF8";Code language: PHP (php)

  Note that the charset UTF-8 sets the character set of the database connection to UTF-8.

  Connecting to MySQL

  The following index.php script illustrates how to connect to the bookdb database on the MySQL database server with the root account:

   require 'config.php'; $dsn = "mysql:host=$host;dbname=$db;charset=UTF8"; try < $pdo = new PDO($dsn, $user, $password); if ($pdo) < echo "Connected to the $db database successfully!"; > > catch (PDOException $e) < echo $e->getMessage(); >Code language: HTML, XML (xml)

  • First, create a new PDO object with the data source name, user, and password. The PDO object is an instance of the PDO class.
  • Second, show the success message if the connection is established successfully or an error message if an error occurs.

  If you have everything set up correctly, you will see the following message:

  Connected to the bookdb database successfully!Code language: plaintext (plaintext)

  Error handling strategies

  PDO supports three different error handling strategies:

  • PDO::ERROR_SILENT – PDO sets an error code for inspecting using the PDO::errorCode() and PDO::errorInfo() methods. The PDO::ERROR_SILENT is the default mode.
  • PDO::ERRMODE_WARNING – Besides setting the error code, PDO will issue an E_WARNING message.
  • PDO::ERRMODE_EXCEPTION – Besides setting the error code, PDO will raise a PDOException .

  To set the error handling strategy, you can pass an associative array to the PDO constructor like this:

  $pdo = new PDO($dsn, $user, $password, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);Code language: PHP (php)

  Or you can use the setAttribute() method of the PDO instance:

  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);Code language: PHP (php)

  Troubleshooting

  There are some common issues when you connect to a MySQL database:

  If the MySQL driver is not enabled in the php.ini file, you will get the error message:

  could not find driverCode language: plaintext (plaintext)

  If you provide an incorrect password, you get the following error message:

  SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: YES)Code language: plaintext (plaintext)

  If you provide an invalid database name or the database does not exist, you get the following error message:

  SQLSTATE[HY000] [1049] Unknown database 'bookdb'Code language: plaintext (plaintext)

  If you provide an invalid database hostname, the following error message will display:

  SQLSTATE[HY000] [2002] php_network_getaddresses: getaddrinfo failed: No such host is known.Code language: plaintext (plaintext)

  Summary

  • Enable the PDO_MYSQL driver in the php.ini file for connecting to a MySQL database from PHP PDO.
  • Create an instance of the PDO class to make a connection to a MySQL database.
  • Use the PDO constructor or the setAttribute() method to set an error handling strategy.

  Источник

  Читайте также:  Python hide windows console