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 let me know if you find any errors or omissions in the document —Upsorn Praphamontripong, 30-September-2023)

Basic info when using PDO

  1. To use PDO to connect to a database, you need the following information:
    • Host name or IP address (and port number if a default port is not used)
    • Database name
    • Your username
    • Your password
  2. 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.

  3. Create an instance of PDO which connects to a database

         Syntax:   new PDO(dsn, username, password);

Example code: connect-db.php (text version)

DB (GCP)

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

  1. 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.

  2. To use PDO to connect to a database, you need the following information:
    • Host name — your-project-ID: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.

    php connecting to sql instance

  3. Set the DSN with the information above using the format

         driver:unix_socket=/cloudsql/your-instance-connection-name;dbname=your-database-name.
    where your instance connection name is your-project-name:us-east4:your-sql-instance-ID.

    For example, if your project name is cs4750, your SQL instance ID is db-demo, and the database name is guestbook,

         host would be cs4750:us-east4:db-demo
         DSN would be mysql:unix_socket=/cloudsql/cs4750:us-east4:db-demo;dbname=guestbook"
    (where PDO driver is mysql).


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

  1. 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.

  2. 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 cloud 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

      php connecting to sql instance

      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.

      php connecting to sql instance

    • On the overview page of your cloud instance, copy a Public IP address.

      php connecting to sql instance

    • 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)

  1. 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, PHP deployment on CS server, 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.

  2. 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 (XAMPP)
    • 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.
  3. 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).


DB (CS)

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

  1. 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, PHP deployment on CS server, or PHP deployment using XAMPP.

    Also assume that you have already set up your MySQL database on the CS department server. If you have not set up your database, please refer to DB setup using CS server.

  2. To use PDO to connect to a database, you need the following information:
    • Host name — mysql01.cs.virginia.edu
    • Database name — your-computingID
    • Your username — your-computingID
    • Your password — your-CS-mysql-password
  3. Set the DSN with the information above using the format

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

    For example, the DSN would be
         mysql:host=mysql01.cs.virginia.edu;dbname=your-computingID
    (where PDO driver is mysql). Note: MySQL database on the CS server uses a default port. Thus, no port number is needed here.








Copyright © 2023 Upsorn Praphamontripong

Released under the Creative Commons License CC-BY-NC-SA 4.0 license.

Last updated 2023-09-30 10:54