PHP Read AND Write TO MySQL ON Google Cloud From App Engine

by
PHP is no longer just a preview for AppEngine since June of 2015. It's amazing how easy it is to configure MySQL cloud instance on Google cloud console (console.developers.google.com). One can even write a SQL query in a simple text file “myquery.sql”, drag and drop text file to cloud storage bucket from most web browser, then simply point and click to import/run query on CloudSQL database. Let’s do a minor modification to web upload script and save uploaded file locations using the script on CloudSQL database.
  • view_list Create a CloudSQL instance

    Once you have your project created and selected on console.developers.google.com

    Click-New-instance 1-cloudsql-found-under-storage Type instance name after colon. let's name this "myfiles" and enter this later in config.php file of our AppEngine script. 2-create-CloudSQL-instance click "Show advanced options" and set this instance to be always on and Asynchronous under "Show advanced options" section. Set-CloudSQL-always-on-and-Asynchronous
  • view_list Create database name myfiles
    Click instance id and let's create a database click-CloudSQL-instance-name Now let's click "Databases" and "New Database" button create-a-database let's name this myfiles and default collation should be fine. If there is a need MySQL allows Database admin to configure Collation for even a single column of a table. default-collation-should-be-fine

    Create a table named myfiles

    To run a query on this new database to create a table. Create a text file name myfiles-table with .sql extension (myfiles-table.sql) using any plain text editor or your code editor (eclipse, Notepad++, TextMate, wrangler, brackets.io). Edit the text file with this SQL Query. Drop table line is there if you plan to test and deploy multiple times.
    
    
    
    DROP TABLE IF EXISTS myfiles;

    CREATE TABLE myfiles (
    file_id BIGINT UNSIGNED NULL AUTO_INCREMENT,
    file_name VARCHAR(255) NOT NULL,
    insert_date DATETIME NOT NULL,
    PRIMARY KEY (file_id)
    );
  • view_list Set permissions to Cloud Storage Bucket

    First need to set permissions for Storage Bucket before importing or running myfiles-table.sql query.

    Click Cloud Storage Browser click-Cloud-Storage-Browser Click the three dotted ellipse button and edit bucket permissions. Edit-bucket-permissions Check if project owners and editors have ownership permission. cloud-bucket-project-owner-and-editor-permission If you don't have a bucket please Create a Storage Bucket by clicking "Create Bucket" button. Create-Bucket
  • view_list Import/run query from Cloud Storage Bucket

    Browse to Storage bucket then Drag and drop myfiles-table.sql into your storage bucket. You can also click Upload files button.

    click-upload-files

    Browse to CloudSQL section of Google cloud dashboard and click on instance id link.

    click-CloudSQL-instance-name

    Click Import button.

    click-import-to-run-sql-query

    Type in bucket name and name of the text file created earlier. bucket-name/myfiles-table.sql

    Also click "Show advanced options" to enter database name "myfiles" and click OK

    run-sql-querry-on-new-database
  • view_list Modify existing PHP Upload

    Now there is a storage bucket and CloudSQL database setup. Now we can modify existing Upload code using a code editor.

    brackets-editor
  • view_list 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
  • view_list 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:
    
    
    
    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

    Here is php.ini.

    
    
    
    ; 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"

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

    unable-to-find-socket-transport-unix

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

  • view_list Writing a config file and CloudSQL connection
    
    
    
    <?php

    //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);

    ?>
  • view_list Writing a functions.php file
    This file has functions that allow application script to read and write to CloudSQL.
    
    
    
    <?php

    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",
            ));
          
    }
    ?>

    <?php
    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");
        
    }
    ?>
  • view_list 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:
    
    
    
    <?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);

    ?>
  • view_list Write functions for CloudSQL to List rows and write file locations
    Here is writeFileToDatabase function used in upload.php from functions.php.
    
    
    
    <?php  
       writeFileToDatabase
    ("gs://".$storageBucketName."/".$filename."",$dbTableName); 
    ?>
    Here is deleteFromTable and listFilesInDatabaseTable function being used in upload.php from functions.php file.
    
    
    
     <?php 

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

        
    //list files 
           
    listFilesInDatabaseTable($dbTableName);
      
    ?>
  • view_list Here is complete upload.php:
    
    
    
    <?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">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>

Reference:

http://googlecloudplatform.blogspot.com/2015/06/App-Engine-for-PHP-is-generally-available-Build-scalable-apps-faster.html PHP MySQL Wrapper class http://www.phpclasses.org/package/5205-PHP-MySQL-access-wrapper-based-on-static-functions.html#download Screenshots of Google Cloud Dashboard to help illustrate http://www.google.com/permissions/trademark/our-trademarks.html Brackets editor - Code Editor Screenshot used in this post brackets.io editor PHP and MySQL PHP and MySQL Web Development Fourth Edition Learning Paths
Z Data Tech https://www.zdatatech.com/logo/zdatatech-logo.png Last modified: October 29, 2018
Advertisement