jsimonweb/README.md

## README.md

      
    Raw
  

              README.md
            
          
    This is a collection of scripts used to demonstrate how to get started with Google Cloud SQL.
Follow the step by step procedures in this video: Getting Started with Google Cloud SQL
Prerequisites:


Sign up for Google Cloud SQL, if you haven't already.


Download and install MySQL Community Server which includes the MySQL Command-line client tool.

MySQL Command-line client settings:

host: IP address (shown in video)
port: 3306 (normal)
configuration: nothing special other than MySQL Command-line client installed


Download and Install the gcutil command line tool. This will be used for interacting with a Google Compute Engine VM instance in order to connect it to Cloud SQL.


Inventory of scripts:
SQL script to create a MySQL database and populate it with sample data:

schema_and_data.sql : This will be run from the MySQL command line client tool.

Setup scripts for installing PHP and phpMyAdmin on a Compute Engine VM:

setup-php.sh : Install the software necessary to serve PHP pages from a Compute Engine VM.
setup-phpmyadmin.sh : Install the software necessary to run phpMyAdmin on a Compute Engine VM.

Example PHP Pages:

index-gce.php : Example PHP page running on Compute Engine that connects to a Cloud SQL instance.
index-appengine.php : Example PHP page running on App Engine that connects to a Cloud SQL instance.


## index-appengine.php
<?php
//This is an example PHP page that connects to Google Cloud SQL databases from an App Engine application

/*
Connect to Cloud SQL
  Usage: Update the connection string with your connection information
    -Replace "project_id" with the Project ID of your Cloud SQL Project
    -Replace "demo-db" with the name of your instance in Cloud SQL
    -Replace "root" with your user name
*/
$conn = mysql_connect(":/cloudsql/project_id:demo-db", "root", "");
if (!$conn) {
    die('Connect Error (' . mysql__error());
}


//Select Database
$db_selected = mysql_select_db('bigstars', $conn);
if (!$db_selected) {
    die ('Can\'t use db : ' . mysql_error());
}

//Perform Query
$result = mysql_query("SELECT name, size FROM stars;");

//Show Results
echo "<!doctype html>";
echo "<body>";
echo "<h3>Results from Google Cloud SQL</h3>";
echo "<table class=simpletable border=1>";
echo "<tr><th align=left>Star Name</th><th>(x) times larger than the Sun</th></tr>";
while ($row = mysql_fetch_assoc($result)) {
    echo "<tr><td align=left>" . $row['name'] . "</td>";
    echo "<td align=center class=addCommas>" . $row['size'] . "</td></tr>";
}
echo "</table>";
?>


<!-- Style the results table -->
<style type="text/css">
h3 {font-family:verdana;font-size:24px;color:#181C26;}
table.simpletable {font-family:verdana;font-size:15px;color:#40434A;border-width:1px;border-color:#778AB8;border-collapse:collapse;}
table.simpletable th {border-width: 1px;padding: 10px;border-style: solid;border-color:#778AB8;background-color:#dedede;}
table.simpletable td {border-width: 1px;padding: 10px;border-style: solid;border-color: #778AB8;background-color: #ffffff;}
</style>

<!-- Add commas to numbers appearing in the table cell with the attribute 'class=addCommas'-->
<script type="text/javascript">
function formatNumberWithCommas(x) {
    return x.toString().replace(/\B(?=(\d{3})+(?!\d))/g, ",");
}

var elements = document.querySelectorAll('td.addCommas');
var i;
for (i in elements) {
   if(elements[i].innerHTML != undefined) {
         elements[i].innerHTML = formatNumberWithCommas(elements[i].innerHTML);
   }
}
</script>

</body>
</html>

## index-gce.php
<?php
//This is an example PHP page that connects to Google Cloud SQL databases from Google Compute Engine VMs

/*
Connect to Cloud SQL
  Usage: Update the connection string with your connection information
    -Replace "ip_address" with the IP address of your Cloud SQL instance
    -Replace "root" with your user name
    -Replace "password" with your password
*/
$conn = mysql_connect("ip_address", "root", "password");
if (!$conn) {
    die('Connect Error (' . mysql__error());
}


//Select Database
$db_selected = mysql_select_db('bigstars', $conn);
if (!$db_selected) {
    die ('Can\'t use db : ' . mysql_error());
}

//Perform Query
$result = mysql_query("SELECT name, size FROM stars;");

//Show Results
echo "<!doctype html>";
echo "<body>";
echo "<h3>Results from Google Cloud SQL</h3>";
echo "<table class=simpletable border=1>";
echo "<tr><th align=left>Star Name</th><th>(x) times larger than the Sun</th></tr>";
while ($row = mysql_fetch_assoc($result)) {
    echo "<tr><td align=left>" . $row['name'] . "</td>";
    echo "<td align=center class=addCommas>" . $row['size'] . "</td></tr>";
}
echo "</table>";
?>


<!-- Style the results table -->
<style type="text/css">
h3 {font-family:verdana;font-size:24px;color:#181C26;}
table.simpletable {font-family:verdana;font-size:15px;color:#40434A;border-width:1px;border-color:#778AB8;border-collapse:collapse;}
table.simpletable th {border-width: 1px;padding: 10px;border-style: solid;border-color:#778AB8;background-color:#dedede;}
table.simpletable td {border-width: 1px;padding: 10px;border-style: solid;border-color: #778AB8;background-color: #ffffff;}
</style>

<!-- Add commas to numbers appearing in the table cell with the attribute 'class=addCommas'-->
<script type="text/javascript">
function formatNumberWithCommas(x) {
    return x.toString().replace(/\B(?=(\d{3})+(?!\d))/g, ",");
}

var elements = document.querySelectorAll('td.addCommas');
var i;
for (i in elements) {
   if(elements[i].innerHTML != undefined) {
         elements[i].innerHTML = formatNumberWithCommas(elements[i].innerHTML);
   }
}
</script>

</body>
</html>

## schema_and_data.sql
/* Create a sample database and populate it with sample data */

/* Create a sample database */
CREATE DATABASE bigstars;

/* Select the database for use */
USE bigstars;

/* Create a sample table within the new database containing two columns */
CREATE TABLE stars (name VARCHAR(50), size INT);

/* Insert Data into the sample table */
INSERT INTO stars VALUES ('UY Scuti', 1708);
INSERT INTO stars VALUES ('NML Cygni', 1650);
INSERT INTO stars VALUES ('WOH G64', 1540);
INSERT INTO stars VALUES ('Westerlund 1-26', 1530);
INSERT INTO stars VALUES ('VX Sagittarii', 1520);
INSERT INTO stars VALUES ('KY Cygni', 1420);
INSERT INTO stars VALUES ('VY Canis Majoris', 1420);
INSERT INTO stars VALUES ('AH Scorpii', 1287);
INSERT INTO stars VALUES ('RW Cephei', 1260);
INSERT INTO stars VALUES ('PZ Cassiopeiae', 1190);

## setup-php.sh
#!/bin/bash
#Install the software packages necessary to serve PHP pages that connect to Cloud SQL

#Usage:
#Copy and run this script on a Debian based Google Compute Engine VM
#1. SSH into a Google Compute Engine VM and create the script: $sudo nano setup-php.sh
#2. Copy the contents of this script into the newly created file and save it: ctrl-x, then "y", then enter
#3. Give the script permission to run: $sudo chmod 755 setup-php.sh
#4. Run the script: $sudo ./setup-php.sh

#install updates
sudo apt-get update -y

#install Apache
sudo apt-get install -y apache2

#install PHP5
sudo apt-get install -y php5

#install PHP5 dependencies
sudo apt-get install -y libapache2-mod-php5
sudo apt-get install -y php5-json
sudo apt-get install -y php5-mysql

#install the MySQL client (optional)
sudo apt-get install -y mysql-client

#restart Apache
sudo /etc/init.d/apache2 restart

## setup-phpmyadmin.sh
#!/bin/bash
#Install the software packages necessary to run phpMyAdmin and connect to Cloud SQL

#Note:
#This script should be run after setup-php.sh because this script assumes that has been done previously

#Usage:
#Copy and run this script on a Debian based Google Compute Engine VM
#1. SSH into a Google Compute Engine VM (after running setup-php.sh)
#   and create the script: $sudo nano setup-phpmyadmin.sh
#2. Copy the contents of this script into the newly created file and save it: ctrl-x, then "y", then enter
#3. Give the script permission to run: $sudo chmod 755 setup-phpmyadmin.sh
#4. Run the script: $sudo ./setup-phpmyadmin.sh

#install MySQL Server
sudo apt-get install -y mysql-server

#install phpMyAdmin
sudo apt-get install -y phpmyadmin

#update apache configuration file to include link to phpmyadmin configuration
sudo echo 'Include /etc/phpmyadmin/apache.conf' >> /etc/apache2/apache2.conf

#restart Apache
sudo /etc/init.d/apache2 restart
	<?php
	//This is an example PHP page that connects to Google Cloud SQL databases from an App Engine application

	/*
	Connect to Cloud SQL
	Usage: Update the connection string with your connection information
	-Replace "project_id" with the Project ID of your Cloud SQL Project
	-Replace "demo-db" with the name of your instance in Cloud SQL
	-Replace "root" with your user name
	*/
	$conn = mysql_connect(":/cloudsql/project_id:demo-db", "root", "");
	if (!$conn) {
	die('Connect Error (' . mysql__error());
	}


	//Select Database
	$db_selected = mysql_select_db('bigstars', $conn);
	if (!$db_selected) {
	die ('Can\'t use db : ' . mysql_error());
	}

	//Perform Query
	$result = mysql_query("SELECT name, size FROM stars;");

	//Show Results
	echo "<!doctype html>";
	echo "<body>";
	echo "<h3>Results from Google Cloud SQL</h3>";
	echo "<table class=simpletable border=1>";
	echo "<tr><th align=left>Star Name</th><th>(x) times larger than the Sun</th></tr>";
	while ($row = mysql_fetch_assoc($result)) {
	echo "<tr><td align=left>" . $row['name'] . "</td>";
	echo "<td align=center class=addCommas>" . $row['size'] . "</td></tr>";
	}
	echo "</table>";
	?>


	<!-- Style the results table -->
	<style type="text/css">
	h3 {font-family:verdana;font-size:24px;color:#181C26;}
	table.simpletable {font-family:verdana;font-size:15px;color:#40434A;border-width:1px;border-color:#778AB8;border-collapse:collapse;}
	table.simpletable th {border-width: 1px;padding: 10px;border-style: solid;border-color:#778AB8;background-color:#dedede;}
	table.simpletable td {border-width: 1px;padding: 10px;border-style: solid;border-color: #778AB8;background-color: #ffffff;}
	</style>

	<!-- Add commas to numbers appearing in the table cell with the attribute 'class=addCommas'-->
	<script type="text/javascript">
	function formatNumberWithCommas(x) {
	return x.toString().replace(/\B(?=(\d{3})+(?!\d))/g, ",");
	}

	var elements = document.querySelectorAll('td.addCommas');
	var i;
	for (i in elements) {
	if(elements[i].innerHTML != undefined) {
	elements[i].innerHTML = formatNumberWithCommas(elements[i].innerHTML);
	}
	}
	</script>

	</body>
	</html>
	/* Create a sample database and populate it with sample data */

	/* Create a sample database */
	CREATE DATABASE bigstars;

	/* Select the database for use */
	USE bigstars;

	/* Create a sample table within the new database containing two columns */
	CREATE TABLE stars (name VARCHAR(50), size INT);

	/* Insert Data into the sample table */
	INSERT INTO stars VALUES ('UY Scuti', 1708);
	INSERT INTO stars VALUES ('NML Cygni', 1650);
	INSERT INTO stars VALUES ('WOH G64', 1540);
	INSERT INTO stars VALUES ('Westerlund 1-26', 1530);
	INSERT INTO stars VALUES ('VX Sagittarii', 1520);
	INSERT INTO stars VALUES ('KY Cygni', 1420);
	INSERT INTO stars VALUES ('VY Canis Majoris', 1420);
	INSERT INTO stars VALUES ('AH Scorpii', 1287);
	INSERT INTO stars VALUES ('RW Cephei', 1260);
	INSERT INTO stars VALUES ('PZ Cassiopeiae', 1190);
	#!/bin/bash
	#Install the software packages necessary to serve PHP pages that connect to Cloud SQL

	#Usage:
	#Copy and run this script on a Debian based Google Compute Engine VM
	#1. SSH into a Google Compute Engine VM and create the script: $sudo nano setup-php.sh
	#2. Copy the contents of this script into the newly created file and save it: ctrl-x, then "y", then enter
	#3. Give the script permission to run: $sudo chmod 755 setup-php.sh
	#4. Run the script: $sudo ./setup-php.sh

	#install updates
	sudo apt-get update -y

	#install Apache
	sudo apt-get install -y apache2

	#install PHP5
	sudo apt-get install -y php5

	#install PHP5 dependencies
	sudo apt-get install -y libapache2-mod-php5
	sudo apt-get install -y php5-json
	sudo apt-get install -y php5-mysql

	#install the MySQL client (optional)
	sudo apt-get install -y mysql-client

	#restart Apache
	sudo /etc/init.d/apache2 restart
	#!/bin/bash
	#Install the software packages necessary to run phpMyAdmin and connect to Cloud SQL

	#Note:
	#This script should be run after setup-php.sh because this script assumes that has been done previously

	#Usage:
	#Copy and run this script on a Debian based Google Compute Engine VM
	#1. SSH into a Google Compute Engine VM (after running setup-php.sh)
	# and create the script: $sudo nano setup-phpmyadmin.sh
	#2. Copy the contents of this script into the newly created file and save it: ctrl-x, then "y", then enter
	#3. Give the script permission to run: $sudo chmod 755 setup-phpmyadmin.sh
	#4. Run the script: $sudo ./setup-phpmyadmin.sh

	#install MySQL Server
	sudo apt-get install -y mysql-server

	#install phpMyAdmin
	sudo apt-get install -y phpmyadmin

	#update apache configuration file to include link to phpmyadmin configuration
	sudo echo 'Include /etc/phpmyadmin/apache.conf' >> /etc/apache2/apache2.conf

	#restart Apache
	sudo /etc/init.d/apache2 restart