Update RecordThrough Google sheet to Mysql

 

// Replace the variables in this block with real values.

  var address = 'xxxxx';

  var user = 'xxxx';

  var userPwd = 'xxxx';

  var db = 'xxxxx';

 

 

  var dbUrl = 'jdbc:mysql://' + address + '/' + db;

 

  // C O N N E C T

function getDBConnection_(data)

{

 data = data || getMySqlConnectionData_();

 // getCloudSqlConnection

 var conn = Jdbc.getConnection('jdbc:mysql://' + data.address + ':3306/' + data.db + '?characterEncoding=UTF-8', data.user, data.userPwd);  

                            //  dbName?characterEncoding=UTF-8

                            //  jdbc:mysql://yoursqlserver.example.com:3306/database_name

 return conn;  

}

  // Read up to 1000 rows of data from the table and log them.

 function readFromTable() {

    var conn = Jdbc.getConnection(dbUrl, user, userPwd);

    var start = new Date();

    var stmt = conn.createStatement();

    var results = stmt.executeQuery('SELECT * FROM diaken_company');

    var sheet = SpreadsheetApp.getActiveSpreadsheet();

    var cell = sheet.getRange('A1');

    var numCols = results.getMetaData().getColumnCount();

    var row =0;

    while (results.next()) {

      var rowString = '';

      for (var col = 0; col < numCols; col++) {

        rowString += results.getString(col + 1) + '\t';

        cell.offset(row, col).setValue(results.getString(col +1 ));

      }

      row++

     Logger.log(rowString)

    }

    results.close();

    stmt.close();

    conn.close();

    var end = new Date();

    Logger.log('Time elapsed: %sms', end - start);

  }

// U P D A T E

function test_update()

{

 var conn = Jdbc.getConnection(dbUrl, user, userPwd);

 // samle update request

  var sheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1ru3Mwu6svgsr_euTP_XUq0AlU_GOAMMnFn58T4wTR8Y/edit#gid=0");

  var data = sheet.getDataRange().getValues();   

  

      var sql = "update diaken_company set outlet_04  = '"+ data[0][1]+"',outlet_05  = '"+ data[0][2]+"',outlet_06  = '"+ data[0][3]+"',outlet_07  = '"+ data[0][4]+"',outlet_08  = '"+ data[0][5]+"',outlet_09  = '"+ data[0][6]+"' where id = "+data[0][0];  

 runCustomUpdate_(sql);  

  

  

}

function runCustomUpdate_(sql)

{

 var conn = Jdbc.getConnection(dbUrl, user, userPwd);

 if (sql === '') { return -1; } // nothing to update  

 var stmt = conn.createStatement();

 Logger.log(sql)

 var results = stmt.executeUpdate(sql);

 conn.close();

 return results;

}

// custom menu function

function onOpen() {

  var ui = SpreadsheetApp.getUi();

  ui.createMenu('Record Update')

      .addItem('Save Data','saveData')

      .addToUi();

}

// function to save data

function saveData() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1ru3Mwu6svgsr_euTP_XUq0AlU_GOAMMnFn58T4wTR8Y/edit#gid=0");

  var data = sheet.getDataRange().getValues();   

  

      var sql = "update diaken_company set outlet_04  = '"+ data[0][1]+"',outlet_05  = '"+ data[0][2]+"',outlet_06  = '"+ data[0][3]+"',outlet_07  = '"+ data[0][4]+"',outlet_08  = '"+ data[0][5]+"',outlet_09  = '"+ data[0][6]+"' where id = "+data[0][0];  

 runCustomUpdate_(sql);  

  

}

Mysql data get and show in google sheet

Step 1: Create a spreadsheet within your Google Drive.

Step 2: Insert script in newly created spreadsheet.

Step 3.1: To insert the script in Google spreadsheet go to Tools > Script Editor.

Step 4.2: On opening script editor you will find a Google Apps Script pop-up Close it.

Step 5: save file and run the script

 // Replace the variables in this block with real values.

    var address = '192.xxxxx';

    var user = 'xxxxx';

    var userPwd = 'xxxxx@@121';

    var db = 'xxxxx';

    var dbUrl = 'jdbc:mysql://' + address + '/' + db;

    // Read up to 1000 rows of data from the table and log them.

   function readFromTable() {

      var conn = Jdbc.getConnection(dbUrl, user, userPwd);

      var start = new Date();

      var stmt = conn.createStatement();

      // Read up to 1000 rows of data from the table and log them.

      // stmt.setMaxRows(1000);

      var results = stmt.executeQuery('SELECT * FROM hub_consulation_form');

      var sheet = SpreadsheetApp.getActiveSpreadsheet();

      var cell = sheet.getRange('A1');

      var numCols = results.getMetaData().getColumnCount();

      var row =0;

      while (results.next()) {

        var rowString = '';

        for (var col = 0; col < numCols; col++) {

          rowString += results.getString(col + 1) + '\t';

          cell.offset(row, col).setValue(results.getString(col +1 ));

        }

        row++

       Logger.log(rowString)

      }

      results.close();

      stmt.close();

      conn.close();

      var end = new Date();

      Logger.log('Time elapsed: %sms', end - start);

    }

    

Curl Notification send Using Php Firebase

 <?php

/* New Server Php */

$token = 'user tken';

  define( 'API_ACCESS_KEY', 'key' );

$registrationIds = array($token);

$msg = array

(

'messages' => 'Order Id',

'title'         => 'Got a new Order',

'subtitle' =>  'Order Id ',

'tickerText' => 4545,

'vibrate' => '2',

'sound' => 1,

'largeIcon' => 'large_icon',

'smallIcon' => 'small_icon'

);

$fields = array

(

'registration_ids' => $registrationIds,

'data' => $msg

);

$headers = array

(

'Authorization: key=' . API_ACCESS_KEY,

'Content-Type: application/json'

);

$ch = curl_init();

curl_setopt( $ch,CURLOPT_URL, 'https://fcm.googleapis.com/fcm/send' );

curl_setopt( $ch,CURLOPT_POST, true );

curl_setopt( $ch,CURLOPT_HTTPHEADER, $headers );

curl_setopt( $ch,CURLOPT_RETURNTRANSFER, true );

curl_setopt( $ch,CURLOPT_SSL_VERIFYPEER, false );

curl_setopt( $ch,CURLOPT_POSTFIELDS, json_encode( $fields ) );

$result = curl_exec($ch );

echo $result

curl_close( $ch );

?>

<?php

 /*Old Server php */

define( 'API_ACCESS_KEY', 'xxxxx' );

$singleID = 'user tken' ; 

$registrationIDs = array( 'user tken',     'user tken',     'user tken'

) ;

$fcmMsg = array(

'body' => 'here is a message. message',

'title' => 'This is title #1',

'sound' => "jingle_bell.mp3",

     'content_available' => true, 

     'priority' => 'high', 

);

$fcmFields = array(

'to' => $singleID,

    'sound' => 'jingle_bell.mp3',

    'priority' => 'high',

'notification' => $fcmMsg

);

$headers = array(

'Authorization: key=' . API_ACCESS_KEY,

'Content-Type: application/json'

);

 

$ch = curl_init();

curl_setopt( $ch,CURLOPT_URL, 'https://fcm.googleapis.com/fcm/send' );

curl_setopt( $ch,CURLOPT_POST, true );

curl_setopt( $ch,CURLOPT_HTTPHEADER, $headers );

curl_setopt( $ch,CURLOPT_RETURNTRANSFER, true );

curl_setopt( $ch,CURLOPT_SSL_VERIFYPEER, false );

curl_setopt( $ch,CURLOPT_POSTFIELDS, json_encode( $fcmFields ) );

$result = curl_exec($ch );

curl_close( $ch );

echo $result . "\n\n";

?>

Sunrise and Sunset time using PHP

 <?php

echo date("D M d Y"); 

echo("\nSunset time: "); 

echo(date_sunset(time(), SUNFUNCS_RET_STRING, 

                 30.7046, 76.7179, 266, 5.30)); 

                 

                 echo "<br>";

echo date("D M d Y"); 

echo("\nsunrise time: "); 

echo(date_sunrise(time(), SUNFUNCS_RET_STRING, 

              30.7046, 76.7179, 88, 5.30)); 

?>

Bootstrap table data sticky

 .tableFixHead { overflow-y: auto; max-height: 400px; }

/* Just common table stuff. */

table  { border-collapse: collapse; width: 100%; }

th, td { padding: 8px 16px; }

th     { background:#eee; }

var $th = $('.tableFixHead').find('thead th')

$('.tableFixHead').on('scroll', function() {

  $th.css('transform', 'translateY('+ this.scrollTop +'px)');

});

 <thead class="thead-dark">

 

 <div class="tableFixHead">