{"id":1280,"date":"2021-04-20T04:42:54","date_gmt":"2021-04-20T04:42:54","guid":{"rendered":"https:\/\/phptutorial.net\/?page_id=1280"},"modified":"2021-07-08T04:09:33","modified_gmt":"2021-07-08T04:09:33","slug":"php-pdo-mysql","status":"publish","type":"page","link":"https:\/\/www.phptutorial.net\/php-pdo\/php-pdo-mysql\/","title":{"rendered":"PHP PDO MySQL"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to create a new sample database in MySQL for practicing with PDO.<\/p>\n\n\n\n

Creating a database #<\/a><\/h2>\n\n\n\n

First, log in to the MySQL database using the root<\/code> user from the Command Prompt on Windows or Terminal on macOS and Linux:<\/p>\n\n\n

mysql -u root<\/code><\/span>Code language:<\/span> Shell Session<\/span> (<\/span>shell<\/span>)<\/span><\/small><\/pre>\n\n\n
And you’ll prompt for a password; enter the password for the root<\/code> user and you’re connected to the MySQL Server.<\/p>\n\n\n\n
CREATE<\/span> DATABASE<\/span> IF<\/span> NOT<\/span> EXISTS<\/span> bookdb;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
USE<\/span> bookdb<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
CREATE<\/span> TABLE<\/span> IF<\/span> NOT<\/span> EXISTS<\/span> publishers (\n    publisher_id INT<\/span> AUTO_INCREMENT,\n    name<\/span> VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    PRIMARY KEY<\/span> (publisher_id)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, execute the following CREATE TABLE<\/code> statement to create the books<\/code> table:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> IF<\/span> NOT<\/span> EXISTS<\/span> books (\n    book_id INT<\/span> AUTO_INCREMENT,\n    title VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    isbn VARCHAR<\/span>(13<\/span>) NULL<\/span>,\n    published_date DATE<\/span> NULL<\/span>,\n    publisher_id INT<\/span> NULL<\/span>,\n    PRIMARY KEY<\/span> (book_id),\n    CONSTRAINT<\/span> fk_publisher FOREIGN<\/span> KEY<\/span> (publisher_id)\n        REFERENCES<\/span> publishers (publisher_id)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Connecting to the bookdb database from PHP #<\/a><\/h2>\n\n\n\n
First, create a config.php<\/code> file to store the database parameters:<\/p>\n\n\n
<?php<\/span>\n\n$host     = 'localhost'<\/span>;\n$db       = 'bookdb'<\/span>;\n$user     = 'root'<\/span>;\n$password = ''<\/span>;<\/span><\/code><\/span>Code language:<\/span> HTML, XML<\/span> (<\/span>xml<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, create a new connect.php<\/code> file that connects to the bookdb<\/code> database:<\/p>\n\n\n
<?php<\/span>\n\nrequire<\/span> 'config.php'<\/span>;\n\n$dsn = \"mysql:host=$host;dbname=$db;charset=UTF8\"<\/span>;\n\ntry<\/span> {\n\t$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION];\n\n\t$conn = new<\/span> PDO($dsn, $user, $password, $options);\n\n\tif<\/span> ($conn) {\n\t\techo<\/span> \"Connected to the $db database successfully!\"<\/span>;\n\t}\n} catch<\/span> (PDOException $e) {\n\techo<\/span> $e->getMessage();\n}<\/span><\/code><\/span>Code language:<\/span> HTML, XML<\/span> (<\/span>xml<\/span>)<\/span><\/small><\/pre>\n\n\n
To make it more reusable, you can define a function called connect()<\/code> that returns a new database connection and return it from the connect.php<\/code> file:<\/p>\n\n\n
<?php<\/span>\n\nrequire_once<\/span> 'config.php'<\/span>;\n\nfunction<\/span> connect<\/span>($host, $db, $user, $password)<\/span>\n<\/span>{\n\t$dsn = \"mysql:host=$host;dbname=$db;charset=UTF8\"<\/span>;\n\n\ttry<\/span> {\n\t\t$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION];\n\n\t\treturn<\/span> new<\/span> PDO($dsn, $user, $password, $options);\n\t} catch<\/span> (PDOException $e) {\n\t\tdie<\/span>($e->getMessage());\n\t}\n}\n\nreturn<\/span> connect($host, $db, $user, $password);<\/span><\/code><\/span>Code language:<\/span> HTML, XML<\/span> (<\/span>xml<\/span>)<\/span><\/small><\/pre>\n\n\n
<?php<\/span>\n\n$pdo = require<\/span> 'connect.php'<\/span>;\n\nvar_dump($pdo);<\/span><\/code><\/span>Code language:<\/span> HTML, XML<\/span> (<\/span>xml<\/span>)<\/span><\/small><\/pre>\n\n\n
The require<\/code> construct loads the connect.php<\/code> file and returns the new instance of PDO returned by the connect()<\/code> function.<\/p>\n\n\n\n
Using the class-based approach #<\/a><\/h2>\n\n\n\n
To use a class<\/a> instead of a function<\/a> to create a new database connection, you can follow these steps:<\/p>\n\n\n\n
First, create a new file called Connection.php<\/code> and define the Connection<\/code> class:<\/p>\n\n\n
<?php<\/span>\n\nrequire_once<\/span> 'config.php'<\/span>;\n\nclass<\/span> Connection<\/span>\n<\/span>{\n\tpublic<\/span> static<\/span> function<\/span> make<\/span>($host, $db, $user, $password)<\/span>\n\t<\/span>{\n\t\t$dsn = \"mysql:host=$host;dbname=$db;charset=UTF8\"<\/span>;\n\n\t\ttry<\/span> {\n\t\t\t$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION];\n\n\t\t\treturn<\/span> new<\/span> PDO($dsn, $username, $password, $options);\n\t\t} catch<\/span> (PDOException $e) {\n\t\t\tdie<\/span>($e->getMessage());\n\t\t}\n\t}\n}\n\nreturn<\/span> Connection::make($host, $db, $user, $password);<\/span><\/code><\/span>Code language:<\/span> HTML, XML<\/span> (<\/span>xml<\/span>)<\/span><\/small><\/pre>\n\n\n
The Connection<\/code> class has the make()<\/code> method that returns a new instance of PDO.<\/p>\n\n\n\n
Second, use the Connection.php<\/code> file in other script files as follows:<\/p>\n\n\n
<?php<\/span>\n\n$pdo = require<\/span> 'Connection.php'<\/span>;\nvar_dump($pdo);<\/span><\/code><\/span>Code language:<\/span> HTML, XML<\/span> (<\/span>xml<\/span>)<\/span><\/small><\/pre>\n\n\n
In this tutorial, you have learned how to create the bookdb<\/code> database in the MySQL server and developed a reusable script for connecting the database.<\/p>\n
\n\t
\n\t\t
Did you find this tutorial useful?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
To get the database connection in other script files, you use the require<\/a><\/code> construct like this:<\/p>\n\n\n
After that, execute the following CREATE TABLE<\/a><\/code> statement to create the publishers<\/code> table:<\/p>\n\n\n
Then, select<\/a> to the bookdb<\/code> database:<\/p>\n\n\n
Next, create the bookdb<\/code> database by executing the following CREATE DATABASE<\/a><\/code> statement:<\/p>\n\n\n