Use CloudSQL With PHP Upload Script

Modify existing PHP Upload

Using CloudSQL instance from previous blog post let's modify existing Upload code using a code editor.

Previous App Engine upload script can be downloaded here

Downloading db_mysql.php from phpclasses.org

Community portals such as Phpclasses are a wonderful resource. Downloaded a easy to understand functions based PHP MySQL wrapper class. You will need to register with phpclasses.org before you can Download this db_mysql.php class

Modify app.yaml and php.ini

Replace your-project-id with project id you have on Google Cloud. Runtime is set for php 5.5. Here is app.yaml below:

<code>
application: your-project-id
version: alpha6
runtime: php55
api_version: 1
threadsafe: yes


handlers:
- url: /favicon\.ico
  static_files: favicon.ico
  upload: favicon\.ico
- url: /inc
  static_dir: inc
  
- url: /
  script: upload.php
</code>

Here is php.ini.

<code>
; This is a simple php.ini file on App Engine
; It enables output buffering for all requests by overriding the
; default setting of the PHP interpreter.
output_buffering = "On"
max_file_uploads = 0
upload_max_filesize = 100M
session.gc_maxlifetime = 10000
date.timezone = UTC
log_errors = 1
extension = "mongo.so"
</code>

During debugging added the last line "extension = "mongo.so"" to enable mongo extension. Due to this error:

This issue above turned out to be just a database host address issue.

Writing a config file and CloudSQL connection

<code>
//config
$storageBucketName = "";//enter your unique bucket-name. differen't from all the other bucket names on cloud storage

$dbHost = ":/cloudsql/project-id-name:sql-server-name"; //e.g. ":/cloudsql/project-id-name:sql-server-name"
$dbUser = "root";
$dbPass = ''; //can be left blank when connecting within AppEngine Project
$dbDatabaseName = "myfiles"; //database name
$dbTableName = "myfiles"; //table name

//"Start the bubble machine!"
DB::connect($dbHost, $dbUser, $dbPass, $dbDatabaseName);
</code>

Writing a functions.php file

This file has functions that allow application script to read and write to CloudSQL.

<code>

//PHP code

function writeFileToDatabase($filename,$dbTableName){
    
    $writeFilename = $filename;
  
    $getCurrentDate = new DateTime();
    $saveDateFormat = $getCurrentDate->format('Y-m-d H:i:s');
    
    DB::insert($dbTableName, array(
        "file_name"=> "$writeFilename",
        "insert_date"=> "$saveDateFormat",
        ));
      
}

function listFilesInDatabaseTable($dbTableName){
    
    $result = DB::select($dbTableName, array("file_id", "file_name", "insert_date") , " ORDER BY insert_date DESC");
//>

    <table style="margin-left:auto;margin-right:auto"  border="1">
        <tr>
            <td>Filename with location</td>
            <td>Timestamp</td>
            <td>Delete</td>
        </tr>
        
        //php
           while ($row = DB::fetchRow($result)){
        //>
          <tr>
            <td><?php echo $row->file_name;  ?> </td>
            <td><?php  echo $row->insert_date;   ?> </td>
              
                  <td>
                      <form class="SomeSpaceDude" name="delete-form" action="/"  method="post">
                          <input class="topcoat-button" type="hidden" value="<?php echo $row->file_id  ?>" name="delete" />
                          <input class="topcoat-button" type="submit" value="delete" /> 
                      </form>
                  </td>
        </tr>
        //php
               
           }//close while for table row list 
        
        DB::freeResult($result);
    
        //>
        <tr>
          <td colspan="3"></td>
        </tr>
    </table>
  
//php
}//close listFilesInDatabaseTable function
//>

//php

function deleteFromTable($removeThisRow,$dbTableName){
    
    $deleteThisRow = $removeThisRow;
    
    $result = DB::delete($dbTableName, "where file_id = $deleteThisRow");
    
}
//>
</code>

Adding requires to upload.php file for db_mysql.php, config.php , and functions.php

The required files are only needed one time when user visits application view. Here is top portion of upload.php:

<code>
//php

   require_once 'google/appengine/api/cloud_storage/CloudStorageTools.php';
   use google\appengine\api\cloud_storage\CloudStorageTools;
   require_once 'db_mysql.php';
   require_once 'config.php';
   require_once 'functions.php';
   $options = [ 'gs_bucket_name' => $storageBucketName ];
   $upload_url = CloudStorageTools::createUploadUrl('/', $options);

//>
</code>

Write functions for CloudSQL to List rows and write file locations

Here is writeFileToDatabase function used in upload.php from functions.php.

<code>

//php  
   writeFileToDatabase("gs://".$storageBucketName."/".$filename."",$dbTableName); 

//>

</code>

Here is deleteFromTable and listFilesInDatabaseTable function being used in upload.php from functions.php file.

<code>

 //php 

     if(isset($_POST['delete'])){
       
       deleteFromTable($_POST['delete'],$dbTableName);
    
      } 

    //list files 
       listFilesInDatabaseTable($dbTableName);
 //>

</code>

Here is complete upload.php:

<code>

//php

   require_once 'google/appengine/api/cloud_storage/CloudStorageTools.php';
   use google\appengine\api\cloud_storage\CloudStorageTools;
   require_once 'db_mysql.php';
   require_once 'config.php';
   require_once 'functions.php';
   $options = [ 'gs_bucket_name' => $storageBucketName ];
   $upload_url = CloudStorageTools::createUploadUrl('/', $options);

//>
<!DOCTYPE html>
<html>
<head>
<title></title>
<link rel="stylesheet" type="text/css" href="/inc/topcoat-0.8.0/css/topcoat-mobile-dark.css">
<link rel="stylesheet" type="text/css" href="/inc/css/main.css">
</head>
<body>
<div class="contentArea">
//php

if(isset($_POST['do-upload']) AND $_POST['do-upload'] === "yes"){

   $yesupload = $_POST['do-upload'];
   preg_match("/yes/", "".$yesupload."");

   $filename = $_FILES['testupload']['name'];
   
   $gs_name = $_FILES['testupload']['tmp_name'];
   move_uploaded_file($gs_name, "gs://".$storageBucketName."/".$filename."");

//>

   <p>Hey, file is uploaded</p>
   <p>Name of the file you uploaded: <?php echo $filename ?></p>
    
//php  
   writeFileToDatabase("gs://".$storageBucketName."/".$filename."",$dbTableName); 
//>
       
  <a href="/" target="_self" style="margin-top:30px" class="topcoat-button" rel="noopener">Go Back</a>  

//php
   }//close if do-upload set 
//>
<form class="SomeSpaceDude" name="upload-form" action="<?php echo $upload_url?>" enctype="multipart/form-data" method="post">
   <p>Files to upload: </p> <br>
   <input type="hidden" name="do-upload" value="yes">
   <input class="topcoat-button" type="file" name="testupload" >
   <input class="topcoat-button" type="submit" value="Upload">
</form>
</div>
    
<div>
 //php 

     if(isset($_POST['delete'])){
       
       deleteFromTable($_POST['delete'],$dbTableName);
    
      } 

    //list files 
       listFilesInDatabaseTable($dbTableName);
  //> 
</div>
</body>
</html>

</code>