CODEWITHSUNDEEP
phpmysqlgoogle-app-enginemysqligoogle-cloud-sql
Yitasha
Yitasha

Reputation: 177

Connecting to Google Cloud SQL in Google App Engine

Basically I am deploying an simple website that has register and login. I have deployed into AppEngine and my Cloud SQL is in the same AppEngine Project. Project: http://cc-lab4.appspot.com/register.php

I created database and table in Cloud SQL, I want my register.php to connect to the cloud sql database and perform insert query.

I really want to use mysqli_connect() since I am familiar with this, PDO connection is new to me.

I don't know the exact way of connecting yet, havent been successfull at all. Anyone have idea how to use mysqli_connect() to Cloud SQL would be great.

After Editing by using one of the answers, still didn't work: Cloud SQL Database Instance

 //Variables for Database connection
 $user = "root";
 $pw = "root";
 $socket = '/cloudsql/'. $ENV{"cc-lab4:australia-southeast1:my-sql-artworks"};
 $dbname = "artworks";

 //Registration values from <Form>
 $username = $_POST['username'];
 $password = $_POST['password'];

 //Database connection
 $db = mysqli_connect(NULL, $user, $pw, $dbname, NULL, $socket);

 if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
 }

 //Table Member (id, username,password,reg_date)
 $q = "insert into member values(null, '$username', SHA('$password'), now())";
 mysqli_query($db, $q);

Upvotes: 2

Views: 843

Answers (3)

Yitasha
Yitasha

Reputation: 177

In the end, I managed to connect to my Cloud SQL with PDO method.

$dsn = getenv('MYSQL_DSN');
$user = getenv('MYSQL_USER');
$pw = getenv('MYSQL_PASSWORD');

//Database connection
$db = new PDO($dsn, $user, $pw);

//register value from <form> inputs
$username = $_POST['username'];
$password = $_POST['password'];

//insertion success
$statement = $db->prepare("insert into member values(null, '$username', SHA('$password'), now())");
$statement->execute();

My .yaml file configuration enter image description here

Upvotes: 0

Chris32
Chris32

Reputation: 4961

To connect your App Engine to your Cloud SQL instance you just need to specify the User, Password and DNS of your Cloud SQL instance in your App.yaml file, here is an example:

# Use the PHP 7.3 runtime (BETA) by replacing "php72" below with "php73"
runtime: php72

env_variables:
  # Replace USER, PASSWORD, DATABASE, and CONNECTION_NAME with the
  # values obtained when configuring your Cloud SQL instance.
  CLOUDSQL_USER:
  CLOUDSQL_PASSWORD:
  CLOUDSQL_DSN: "mysql:dbname=DATABASE;unix_socket=/cloudsql/CONNECTION_NAME"

For more info you can see the quickstart guide of App Engine in php

Upvotes: 1

PaulProgrammer
PaulProgrammer

Reputation: 17620

There's no specific reason you can't use mysqli_connect.

I do see your database user variable is user and not $user which might be an issue.

Have a look at the cloud sql documentation here and you should be able to adapt to the PHP syntax.

The Node.js setup looks like this:

let pool;
const createPool = async () => {
  pool = await mysql.createPool({
    user: process.env.DB_USER, // e.g. 'my-db-user'
    password: process.env.DB_PASS, // e.g. 'my-db-password'
    database: process.env.DB_NAME, // e.g. 'my-database'
    // If connecting via unix domain socket, specify the path
    socketPath: `/cloudsql/${process.env.CLOUD_SQL_CONNECTION_NAME}`,
    // If connecting via TCP, enter the IP and port instead
    // host: 'localhost',
    // port: 3306,

    //...
  });
};
createPool();

So the key observation is that the connection name to a socket is stored in an environment variable.

Your PHP might look like this:

$user = "whatever_user";
$password = "whatever_password";
$dbname = "whatever_database";
$socket = '/cloudsql/'.$ENV{"CLOUD_SQL_CONNECTION_NAME"};
$database = mysqli_connect(NULL, $user, $password, $dbname, NULL, $socket);

Upvotes: 2

Related Questions