Connecting PHP and DB

This is a short introduction to help you connect a PHP program and a MySQL database. In this introduction, the PHP Data Objects (PDO) interface is used. PDO is a database abstraction layer providing flexibility for many database engines. However, the same idea applies when drivers such as MySQL or MySQLi are used. The database connectivity concept can also apply to other languages such as Java using JDBC. For more information on PDO, please refer to PDO documentation.
(Please tell me if you find any errors or omissions in the document —Upsorn Praphamontripong, 30-May-2022)

Connect with PDO

To use PDO to connect to a database, you need the following information:
- Host name
- Database name
- Your username
- Your password
Set the Data Source Name (DSN)
Syntax: driver:host=the-host-name;dbname=your-database-name

Data Source Name (DSN) specifies the host computer for the MySQL database and the name of the database. If the MySQL database is running on the same server as PHP, use the localhost keyword to specify the host computer.
Create an instance of PDO which connects to a database
Syntax: new PDO(dsn, username, password);

Example code: connectdb.php (text version)

DB (GCP)

Connecting PHP (hosted on GCP) to DB (hosted on GCP)

Assuming that you have already set up your PHP environment on GCP App Engine (this introduction uses Standard App Engine). If you have not set up your PHP environment on GCP App Engine or do not have Cloud SDK available, please refer to PHP deployment on GCP.

Also assume that you have already set up your Cloud SQL. If you have not set up your Cloud SQL, please refer to DB setup on GCP.
To use PDO to connect to a database, you need the following information:
- Host name — your-project-name:us-east4:your-sql-instance-ID (which is the SQL instance connection name)
- Database name — your-database-name
- Your username — root (unless you created other users)
- Your password — password-you-set-for-the-root-user (unless you created other users)
To find the instance connection name, go to the Cloud SQL instances page. Go to the overview page of your SQL instance and locate the Instance connection name.
Set the DSN with the information above using the format

     driver:host=your-project-name:us-east4:your-sql-instance-ID;dbname=your-database-name.

For example, if your project name is cs4640, your SQL instance ID is db-demo, and the database name is guestbook,
     host would be cs4640:us-east4:db-demo
     DSN would be mysql:host=cs4640:us-east4:db-demo;dbname=guestbook
(where PDO driver is mysql).
Troubleshooting:
If the above setting results in "getaddrinfo failed: Name or service not known" message, please try the following DSN settting.

      mysql:unix_socket=/cloudsql/cs4640:us-east4:db-demo;dbname=guestbook

Connecting PHP (hosted on non-GCP) to DB (hosted on GCP)

Assuming that you have already set up your Cloud SQL. If you have not set up your Cloud SQL, please refer to DB setup on GCP.
To connect to a cloud SQL, you need to configure access to your cloud instance. For more information on configuration, please refer to configure-instance-mysql.
- Start your SQL instance.
- Find the IP address of your (or client where the PHP program is hosted) machine, using What's my IP.
- Copy that IP address, you will later give permission to this IP address to access your SQL instance.
- Go to the overview page of your cloud instance.
- Select the Connections tab. Under Authorized networks, click Add network
  
  Enter the IP address you got previously, (this will allow your IP address to make a connection to the SQL instance), click Done and then click Save.
- On the overview page of your cloud instance, copy a Public IP address.
- In your PHP program, specify the host name with the Public IP address
  host would be your-cloud-instance-public-IP-address
  DSN would be mysql:host=your-cloud-instance-public-IP-address;dbname=your-database-name

DB (XAMPP)

Connecting PHP (hosted on GCP or non-GCP) to DB (hosted on XAMPP)

Assuming that you have already set up your PHP environment. If you have not set up your PHP environment, please refer to PHP deployment on GCP or PHP deployment using XAMPP.

Also assume that you have already set up your MySQL database using XAMPP. If you have not set up your database, please refer to DB setup using XAMPP.
To use PDO to connect to a database, you need the following information:
- Host name — your-host-name:port-number
- Database name — your-database-name
- Your username — root (unless you create other users)
- Your password — password-you-set-for-the-root-user (unless you create other users)
To find a host name
- Start your database server
- Access phpMyAdmin
- Select the User accounts tab
- Locate the username you created, by default the host name is localhost
To find a port number
- Start your database server
- Access phpMyAdmin
- Use the Console (bottom of the screen), type SHOW VARIABLES WHERE Variable_name = 'port';
- Then execute the query (press Ctrl+Enter for Mac users; Command+Enter for Windows users). By default, MySQL database in XAMPP uses port 3306.
Set the DSN with the information above using the format

driver:host=your-host-name:port-number;dbname=your-database-name.

For example, assuming the database name is "guestbook", the DSN would be

mysql:host=localhost:3306;dbname=guestbook
(where PDO driver is mysql).