{"id":134,"date":"2021-03-08T00:27:44","date_gmt":"2021-03-08T00:27:44","guid":{"rendered":"https:\/\/phptutorial.net\/?page_id=134"},"modified":"2021-07-08T04:05:11","modified_gmt":"2021-07-08T04:05:11","slug":"pdo-connecting-to-mysql","status":"publish","type":"page","link":"https:\/\/www.phptutorial.net\/php-pdo\/pdo-connecting-to-mysql\/","title":{"rendered":"Connecting to MySQL"},"content":{"rendered":"\n<p><strong>Summary<\/strong>: in this tutorial, you&#8217;ll learn step by step how to connect to a MySQL database from PHP using PDO.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='prerequisites'>Prerequisites <a href=\"#prerequisites\" class=\"anchor\" id=\"prerequisites\" title=\"Anchor for Prerequisites\">#<\/a><\/h2>\n\n\n\n<p>Before connecting to a MySQL database server, you need to have:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>A MySQL database server, a database, and an account that has access to the database.<\/li><li>PDO MySQL driver enabled in the php.ini file<\/li><\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id='1-setting-mysql-database-parameters'>1) Setting MySQL database parameters <a href=\"#1-setting-mysql-database-parameters\" class=\"anchor\" id=\"1-setting-mysql-database-parameters\" title=\"Anchor for 1) Setting MySQL database parameters\">#<\/a><\/h3>\n\n\n\n<p>Suppose you have a local MySQL database server that has the following information: <\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>The host is <code>localhost<\/code>.<\/li><li>The <code>bookdb<\/code> database on the local database server.<\/li><li>The account with the user <code>root<\/code> and password <code>'S@cr@t1!'<\/code> that can access the <code>bookdb<\/code> database.<\/li><\/ul>\n\n\n\n<p>In PHP, you can create a <code>config.php<\/code> file and place the database parameters:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"HTML, XML\" data-shcb-language-slug=\"xml\"><span><code class=\"hljs language-xml\"><span class=\"php\"><span class=\"hljs-meta\">&lt;?php<\/span>\n\n$host = <span class=\"hljs-string\">'localhost'<\/span>;\n$db = <span class=\"hljs-string\">'bookdb'<\/span>;\n$user = <span class=\"hljs-string\">'root'<\/span>;\n$password = <span class=\"hljs-string\">'S@cr@t1!'<\/span>;<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">HTML, XML<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">xml<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>To use the database parameters, you can include the <code>config.php<\/code> file using the <code><a href=\"https:\/\/phptutorial.net\/php-tutorial\/php-require\/\">require<\/a><\/code> construct:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"HTML, XML\" data-shcb-language-slug=\"xml\"><span><code class=\"hljs language-xml\"><span class=\"php\"><span class=\"hljs-meta\">&lt;?php<\/span>\n\n<span class=\"hljs-keyword\">require<\/span> <span class=\"hljs-string\">'config.php'<\/span>;<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">HTML, XML<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">xml<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\" id='2-enable-pdo_mysql-driver'>2) Enable PDO_MySQL Driver <a href=\"#2-enable-pdo_mysql-driver\" class=\"anchor\" id=\"2-enable-pdo_mysql-driver\" title=\"Anchor for 2) Enable PDO_MySQL Driver\">#<\/a><\/h3>\n\n\n\n<p>PDO_MYSQL is a driver that implements the PDO interface. PDO uses the PDO_MYSQL driver to connect to a MySQL database.<\/p>\n\n\n\n<p>To check if the PDO_MYSQL driver is enabled, you open the <code>php.ini<\/code> file. The <code>php.ini<\/code> file is often located under the <code>php<\/code> directory. For example, you can find the <code>php.ini<\/code> file under the <code>C:\\xampp\\php<\/code> directory if you use <code>XAMPP<\/code> on Windows.<\/p>\n\n\n\n<p>The following shows the extension line in the&nbsp;<code>php.ini<\/code>&nbsp;file:<\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">;extension=php_pdo_mysql.dll<\/code><\/span><\/pre>\n\n\n<p>To enable the extension, you need to uncomment it by removing the semicolon (<code>;<\/code>) from the beginning of the line like this:<\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">extension=php_pdo_mysql.dll<\/code><\/span><\/pre>\n\n\n<p>After that, you need to restart the web server for the change to take effect.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='mysql-data-source-name'>MySQL data source name <a href=\"#mysql-data-source-name\" class=\"anchor\" id=\"mysql-data-source-name\" title=\"Anchor for MySQL data source name\">#<\/a><\/h2>\n\n\n\n<p>PDO uses a data source name (DSN) that contains the following information:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>The database server host<\/li><li>The database name<\/li><li>The user<\/li><li>The password<\/li><li>and other parameters such as character sets, etc.<\/li><\/ul>\n\n\n\n<p>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:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"JSON \/ JSON with Comments\" data-shcb-language-slug=\"json\"><span><code class=\"hljs language-json\"><span class=\"hljs-string\">\"mysql:host=host_name;dbname=db_name;charset=UTF8\"<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JSON \/ JSON with Comments<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">json<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>For example:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">$dsn = <span class=\"hljs-string\">\"mysql:host=localhost;dbname=bookdb;charset=UTF8\"<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-4\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p class=\"note\">Note that the charset UTF-8 sets the character set of the database connection to UTF-8.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='connecting-to-mysql'>Connecting to MySQL <a href=\"#connecting-to-mysql\" class=\"anchor\" id=\"connecting-to-mysql\" title=\"Anchor for Connecting to MySQL\">#<\/a><\/h2>\n\n\n\n<p>The following <code>index.php<\/code>&nbsp;script illustrates how to connect to the <code>bookdb<\/code> database on the MySQL database server with the <code>root<\/code>&nbsp;account:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-5\" data-shcb-language-name=\"HTML, XML\" data-shcb-language-slug=\"xml\"><span><code class=\"hljs language-xml\"><span class=\"php\"><span class=\"hljs-meta\">&lt;?php<\/span>\n\n<span class=\"hljs-keyword\">require<\/span> <span class=\"hljs-string\">'config.php'<\/span>;\n\n$dsn = <span class=\"hljs-string\">\"mysql:host=$host;dbname=$db;charset=UTF8\"<\/span>;\n\n<span class=\"hljs-keyword\">try<\/span> {\n\t$pdo = <span class=\"hljs-keyword\">new<\/span> PDO($dsn, $user, $password);\n\n\t<span class=\"hljs-keyword\">if<\/span> ($pdo) {\n\t\t<span class=\"hljs-keyword\">echo<\/span> <span class=\"hljs-string\">\"Connected to the $db database successfully!\"<\/span>;\n\t}\n} <span class=\"hljs-keyword\">catch<\/span> (PDOException $e) {\n\t<span class=\"hljs-keyword\">echo<\/span> $e-&gt;getMessage();\n}<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-5\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">HTML, XML<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">xml<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>How it works.<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>First, create a new PDO object with the data source name, user, and password. The PDO object is an instance of the <code>PDO<\/code> class.<\/li><li>Second, show the success message if the connection is established successfully or an error message if an error occurs.<\/li><\/ul>\n\n\n\n<p>If you have everything set up correctly, you will see the following message:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-6\" data-shcb-language-name=\"plaintext\" data-shcb-language-slug=\"plaintext\"><span><code class=\"hljs language-plaintext\">Connected to the bookdb database successfully!<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-6\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">plaintext<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">plaintext<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\" id='error-handling-strategies'>Error handling strategies <a href=\"#error-handling-strategies\" class=\"anchor\" id=\"error-handling-strategies\" title=\"Anchor for Error handling strategies\">#<\/a><\/h2>\n\n\n\n<p>PDO supports three different error handling strategies:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><code>PDO::ERROR_SILENT<\/code> &#8211; PDO sets an error code for inspecting using the <code>PDO::errorCode()<\/code> and <code>PDO::errorInfo()<\/code> methods. The <code>PDO::ERROR_SILENT<\/code> is the default mode.<\/li><li><code>PDO::ERRMODE_WARNING<\/code> &#8211; Besides setting the error code, PDO will issue an <code>E_WARNING<\/code> message.<\/li><li><code>PDO::ERRMODE_EXCEPTION<\/code> &#8211; Besides setting the error code, PDO will raise a <code>PDOException<\/code>. <\/li><\/ul>\n\n\n\n<p>To set the error handling strategy, you can pass an associative array to the PDO constructor like this:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-7\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">$pdo = <span class=\"hljs-keyword\">new<\/span> PDO($dsn, $user, $password, &#91;PDO::ATTR_ERRMODE =&gt; PDO::ERRMODE_EXCEPTION]);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-7\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Or you can use the <code>setAttribute()<\/code> method of the PDO instance:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-8\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">$pdo-&gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-8\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\" id='troubleshooting'>Troubleshooting <a href=\"#troubleshooting\" class=\"anchor\" id=\"troubleshooting\" title=\"Anchor for Troubleshooting\">#<\/a><\/h2>\n\n\n\n<p>There are some common issues when you connect to a MySQL database:<\/p>\n\n\n\n<p>If the MySQL driver is not enabled in the <code>php.ini<\/code>&nbsp;file, you will get the error message:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-9\" data-shcb-language-name=\"plaintext\" data-shcb-language-slug=\"plaintext\"><span><code class=\"hljs language-plaintext\">could not find driver<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-9\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">plaintext<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">plaintext<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>If you provide an incorrect password, you get the following error message:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-10\" data-shcb-language-name=\"plaintext\" data-shcb-language-slug=\"plaintext\"><span><code class=\"hljs language-plaintext\">SQLSTATE&#91;HY000] &#91;1045] Access denied for user 'root'@'localhost' (using password: YES)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-10\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">plaintext<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">plaintext<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>If you provide an invalid database name or the database does not exist, you get the following error message:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-11\" data-shcb-language-name=\"plaintext\" data-shcb-language-slug=\"plaintext\"><span><code class=\"hljs language-plaintext\">SQLSTATE&#91;HY000] &#91;1049] Unknown database 'bookdb'<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-11\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">plaintext<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">plaintext<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>If you provide an invalid database hostname, the following error message will display:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-12\" data-shcb-language-name=\"plaintext\" data-shcb-language-slug=\"plaintext\"><span><code class=\"hljs language-plaintext\">SQLSTATE&#91;HY000] &#91;2002] php_network_getaddresses: getaddrinfo failed: No such host is known.<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-12\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">plaintext<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">plaintext<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\" id='summary'>Summary <a href=\"#summary\" class=\"anchor\" id=\"summary\" title=\"Anchor for Summary\">#<\/a><\/h2>\n\n\n\n<ul class=\"wp-block-list\"><li>Enable the <code>PDO_MYSQL<\/code> driver in the <code>php.ini<\/code> file for connecting to a MySQL database from PHP PDO.<\/li><li>Create an instance of the PDO class to make a connection to a  MySQL database.<\/li><li>Use the PDO constructor or the <code>setAttribute()<\/code> method to set an error handling strategy.<\/li><\/ul>\n<div class=\"helpful-block-content\" data-title=\"\">\n\t<header>\n\t\t<div class=\"wth-question\">Did you find this tutorial useful?<\/div>\n\t\t<div class=\"wth-thumbs\">\n\t\t\t<button\n\t\t\t\tdata-post=\"134\"\n\t\t\t\tdata-post-url=\"https:\/\/www.phptutorial.net\/php-pdo\/pdo-connecting-to-mysql\/\"\n\t\t\t\tdata-post-title=\"Connecting to MySQL\"\n\t\t\t\tdata-response=\"1\"\n\t\t\t\tclass=\"wth-btn-rounded wth-yes-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t\tclass=\"feather feather-thumbs-up block w-full h-full\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M14 9V5a3 3 0 0 0-3-3l-4 9v11h11.28a2 2 0 0 0 2-1.7l1.38-9a2 2 0 0 0-2-2.3zM7 22H4a2 2 0 0 1-2-2v-7a2 2 0 0 1 2-2h3\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t<button\n\t\t\t\tdata-response=\"0\"\n\t\t\t\tdata-post=\"134\"\n\t\t\t\tdata-post-url=\"https:\/\/www.phptutorial.net\/php-pdo\/pdo-connecting-to-mysql\/\"\n\t\t\t\tdata-post-title=\"Connecting to MySQL\"\n\t\t\t\tclass=\"wth-btn-rounded wth-no-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M10 15v4a3 3 0 0 0 3 3l4-9V2H5.72a2 2 0 0 0-2 1.7l-1.38 9a2 2 0 0 0 2 2.3zm7-13h2.67A2.31 2.31 0 0 1 22 4v7a2.31 2.31 0 0 1-2.33 2H17\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t<div class=\"wth-form hidden\">\n\t\t<div class=\"wth-form-wrapper\">\n\t\t\t<div class=\"wth-title\"><\/div>\n\t\t\t\n\t\t\t<textarea class=\"wth-message\"><\/textarea>\n\n\t\t\t<button class=\"btn btn-primary wth-btn-submit\">Send<\/button>\n\t\t\t<button class=\"btn wth-btn-cancel\">Cancel<\/button>\n\t\t\n\t\t<\/div>\n\t<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>In this tutorial, you&#8217;ll learn step by step how to connect to a MySQL database from PHP using PDO.<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":21,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-134","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/www.phptutorial.net\/wp-json\/wp\/v2\/pages\/134","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.phptutorial.net\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.phptutorial.net\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.phptutorial.net\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.phptutorial.net\/wp-json\/wp\/v2\/comments?post=134"}],"version-history":[{"count":5,"href":"https:\/\/www.phptutorial.net\/wp-json\/wp\/v2\/pages\/134\/revisions"}],"predecessor-version":[{"id":2161,"href":"https:\/\/www.phptutorial.net\/wp-json\/wp\/v2\/pages\/134\/revisions\/2161"}],"up":[{"embeddable":true,"href":"https:\/\/www.phptutorial.net\/wp-json\/wp\/v2\/pages\/21"}],"wp:attachment":[{"href":"https:\/\/www.phptutorial.net\/wp-json\/wp\/v2\/media?parent=134"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}