CODEWITHSUNDEEP
phpmysqlgoogle-cloud-platform
Matthew
Matthew

Reputation: 49

Connecting to Cloud MySQL Instance

I am fairly new at web development; I am currently trying to create a website that allows users to Submit information and have it stored in a database. I have GoDaddy for my domain name and Hosting, and I use Google Cloud Platform for the cloud database storage.

I have successfully figured out how to store HTML form data into a local database (PhpMyAdmin); I have also successfully connected my Google Cloud instance with another local database using MySQL Workbench.

I cannot figure out how to get everything connected; do I need to connect my Google Cloud instance with GoDaddy? Should I just use GoDaddy's Database offering? I tried putting the IPv4 address of my instance as the 'hostname' in my PHP connection code, which I uploaded to GoDaddy, but it is not connecting and I don’t know why. I don't even know if this is what you are supposed to do?

I’ve read about having to allow permissions for particular IP addresses to store information, but that makes no sense to me as in the real world, I can simply go to a website, fill out a form, and my information is then sent to that company's’ remote database somewhere. That’s what I’m trying to accomplish for my website. Thank you.

PS: I'm using PHP as my server-side language.

Upvotes: 0

Views: 206

Answers (1)

Ben Shoval
Ben Shoval

Reputation: 1752

Here is a general function that you can use to connect to a Google Cloud SQL instance:

function connectSQL( $dbProject, $dbRegion, $dbInstance, $dbIP, $dbName, $dbUsername, $dbPassword, $ssl = TRUE, $persistent = FALSE, $appEngine = FALSE ) {
  // $dbProject is the project that owns the database, which may differ from the project using the database

  // Create a connection
  $db = NULL;
  if ( $appEngine ) {
    // Connect from App Engine
    if ( $persistent ) {
      try {
        $db = new pdo( "mysql:unix_socket=/cloudsql/$dbProject:$dbRegion:$dbInstance;dbname=$dbName", $dbUsername, $dbPassword, array( PDO::ATTR_PERSISTENT => TRUE ) );
      } catch(PDOException $ex) {
        echoArr( $ex );
        return FALSE;
      }
    } else {
      try {
        $db = new pdo( "mysql:unix_socket=/cloudsql/$dbProject:$dbRegion:$dbInstance;dbname=$dbName", $dbUsername, $dbPassword );
      } catch(PDOException $ex) {
        echoArr( $ex );
        return FALSE;
      }
    }
  } else {
    // Connect from a non-App Engine environment (localhost, other hosting, etc.)
    if ( $persistent ) {
      if ( !$ssl ) {
        try{
            $db = new pdo( 'mysql:host='.$dbIP.':3306;dbname='.$dbName, $dbUsername, $dbPassword, array( PDO::ATTR_PERSISTENT => TRUE ) );
          $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch(PDOException $ex) {
          echoArr( $ex );
          return FALSE;
        }
      } else {
        try{
            $db = new pdo( 'mysql:host='.$dbIP.':3306;dbname='.$dbName, $dbUsername, $dbPassword, array(
              PDO::MYSQL_ATTR_SSL_KEY=>'/path/to/client-key.pem',
              PDO::MYSQL_ATTR_SSL_CERT=>'/path/to/client-cert.pem',
              PDO::MYSQL_ATTR_SSL_CA=>'/path/to/server-ca.pem',
              PDO::ATTR_PERSISTENT => TRUE
              )
          );
          $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch(PDOException $ex) {
          echoArr( $ex );
          return FALSE;
        }
      }
    } else {
      if ( !$ssl ) {
        try{
          $db = new pdo( 'mysql:host='.$dbIP.':3306;dbname='.$dbName, $dbUsername, $dbPassword );
          $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch(PDOException $ex) {
          echoArr( $ex );
          return FALSE;
        }
      } else {
        try{
            $db = new pdo( 'mysql:host='.$dbIP.':3306;dbname='.$dbName, $dbUsername, $dbPassword, array(
              PDO::MYSQL_ATTR_SSL_KEY=>'/path/to/client-key.pem',
              PDO::MYSQL_ATTR_SSL_CERT=>'/path/to/client-cert.pem',
              PDO::MYSQL_ATTR_SSL_CA=>'/path/to/server-ca.pem'
              )
          );
          $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch(PDOException $ex) {
          echoArr( $ex );
          return FALSE;
        }
      }
    }
  }

  return $db;

}

You can use this regardless of where your script is hosted.

Upvotes: 1

Related Questions