Updated book PHP Tutorials: Programming with PHP and MySQL

PHP Tutorials Cover ImageThe eBook and Physical Book PHP Tutorials: Programming with PHP and MySQL has been recently updated to include new examples, expanded code explanations and updated material.   In particular:

* The removal of code and descriptions only relevant to PHP versions earlier than
PHP 7.
* Expansion of code examples.
* More explanation of code as used for connecting to databases using PDO and mysqli.
* Further details in data validation.
* Best coding principles and practices.
* Plus many other changes.

US Web site

or

UK Web Site

 

 

Bootstrap 4 NavBar make Rollover for DropDown Navigation and Clickable Top Link

This explains how to change the default Bootstrap 4 NavBar so that any drop down is activated by a mouse rollover rather than an on-click.  It also shows how to change the top level navigation link to be clickable.

The default behaviour of the Bootstrap 4 navigation is any drop down has to be clicked for the drop down to work.  The Bootstrap version that I am currently using is version 4.3 which you can see at:

https://getbootstrap.com/docs/4.3/components/navbar/#supported-content

If you look at the given example, part of the navbar is the drop down code:

 <li class="nav-item dropdown">
        <a class="nav-link 
                  dropdown-toggle" 
                  href="#" 
                  id="navbarDropdown" 
                  role="button" 
                  data-toggle="dropdown" 
                  aria-haspopup="true" 
                  aria-expanded="false">
          Dropdown
        </a>
        <div class="dropdown-menu" aria-labelledby="navbarDropdown">
          <a class="dropdown-item" href="#">Action</a>
          <a class="dropdown-item" href="#">Another action</a>
          <div class="dropdown-divider"></div>
          <a class="dropdown-item" href="#">Something else here</a>
        </div>
      </li>

To allow mouse rollover, add in the following styles as follows:

<style>
.dropdown:hover .dropdown-menu {
display: block;
}
</style>

Now remove the line data-toggle=”dropdown”  in the dropdown code which will make the top link clickable.

So that should be that.  Now a mouse rollover will cause the drop down to appear, and the top link is clickable.

Sell digital downloads – pros and cons of different methods

Sell Digital DownloadsThis article is about some of the decisions that you have to make about selling digital downloads on line. Digital content can be a great way to expand your web site presence, add to your existing physical shop or sell download content that you have created. There are many solutions to choose from and this article will explain issues that you will need to consider.

What is the Basic process of selling a digital product?

Digital products can be mp3 and mp4 files, templates for applications like WordPress, software applications, graphics, icons, and so on. Each of these will have particular requirements for you but the basic process of selling a digital product is essentially the same:

  1. Upload the files that you are going to sell to a web site.
  2. Create a buy now button or add to cart button and place on your web site page.
  3. When the customer clicks on the purchase button, it takes them to the payment processor such as PayPal.
  4. The customer makes the purchase and you receive confirmation of the purchase by email.
  5. You send the product to the customer either automatically using a mechanism associated with the payment processor or you may have some other method like sending the product manually by email at some later time.

What are the ways to sell digital products?

Essentially, there are two ways to sell your products:

  1. From your own web site using some sort of ecommerce application on your site.
  2. Form a third party web site that provides all the functionality for you.


Let’s look at the advantages and disadvantages of these.

Selling from your own web site:

First, you need to think about a shopping application that you can use to sell your products. This could be a product like PHP-eSeller, which is a PHP / MySQL application, or you could go for something like a WordPress installation and install an eCommerce plugin.

Selling from your own web site means that you will have complete control over the look and feel of the site. It also means you have control over the security of your digital products, that is you are not uploading them to someone else’s web site.

You will need a web site with a domain name, which involves understanding web site development and there will be some cost for the hosting plan of course.

If you are technically able, you can create your own web site but you could hire someone to do that. You could also consider a pre-built system like installing WordPress, which could make it easier for you to design your site.

If you are going to build your own site, try to ensure it is fully responsive, so that it works on different devices, desktop computers, tablets and phones.

You will have to set up the shopping application yourself unless you hire someone else to do it. Setting it up yourself may not be a significant issue, as modern applications should install easily.

Selling from a third party site

You will have to find a suitable company that provides a service to sell your digital products. There are many of these but they will charge a subscription so there will be an extra cost and this could be a problem if you only sell a small number a month. Subscriptions vary but usually they are on a per month basis and sometimes depend on the number of digital products or the file size.

You will be sheltered from many technicalities and it should be possible to set up and begin selling quite quickly.
You could have a wide range of payment processors to choose from which may be an advantage.

If you have problems with the system, there will be some kind of help desk available.

Security of your digital content

The security of your digital files is very important, so there should be a way of making sure that any digital files you upload cannot be download unless they were purchased.

If you are selling from your own web site, there are a number of ways of securing your files, which will be described with the shopping cart application that you are using. This often means the use of an htaccess file.

If you are using a third party web site, they should have a built in system that provides the required security. Make sure that you check with the company to ensure that your files are safe.

The type of processing that you need should include:

  1. The download link is unique to each customer.
  2. Downloading via the supplied link is limited in number of attempts and time.
  3. The files that are being sold can only be accessed with the download link – that is, it is not possible to download using a normal URL
  4. The processing with the payment system (PayPal for example) must make the download link available only after the customer has paid.

Music Download sites

Audio Files to sell Digital ContentsYou may be selling digital files that are specifically for music, in which case you may want to look for a system that allows you to upload audio preview files as well as the audio files for sale.
PHP-eSeller has a number of shopping displays intended specifically for music files.

Buy Now or Cart Functions

Buy now buttons are where the customer clicks the button and is immediately taken to the payment page. There is no opportunity to continue shopping or add more products to the shopping basket.

This may be good enough if you only have a few items to sell. If you are selling many items then a shopping cart system is needed, as it will allow the customer to add many products into their basket.

Shopping Cart systems use add to cart and view cart buttons.

Do you need a full-blown system

Sometimes having a super eCommerce web site is just not needed and you may be happy with a system where someone makes a purchase using PayPal for instance, and then you send the product on by email. In that case, you could create your own web site and add a few PayPal buttons.

If you want something more integrated where the product is delivered as soon as the money is in your account, then you need something more sophisticated.

For this you need a system like PayPal IPN where a special code is sent from PayPal as soon as a purchase is made. It is this code that triggers a process to send the purchased item to the customer.

Consider the Checkout process

What payment processor should you use? There are many payment processors around now, but some of them are quite obscure and new. It might be good to go for a lesser-known payment processor that may have lower fees, but if you were a purchaser, would you want to trust your payment through a company you have never heard of?
Digital products allow you to sell all over the world, as there is no shipping of goods, so you need to ensure that customers from different countries can pay you. So make sure you choose a payment processor that is known and available in different countries.

When you look at a shopping application, you will need to decide what kind of processing you want. Some shopping systems ask the customer to sign up before they make a payment that can slow down the purchasing process. Other systems aim for a simpler one-step process where the customer is not asked unnecessary questions. This can be particularly important where the customer is buying only one item.

Does the payment processor have a secure system for communicating with the application? When a customer makes a purchase, it normally sends a set of data to the shopping system. It is important to make sure that this data uses a secure method.

What sort of additional facilities do you need?

Other features that you need to check on are:

  1. Customise templates. Are you able to change the layouts of pages, or buttons?
  2. Customise emails. Can you easily modify the email text and layout to suit your requirements?
  3. Different payment providers. Does the system allow the use of different payment systems?

Do you need someone to do your marketing for you?

At some point, you will need to consider marketing your digital goods. If you are developing your own web site then that is going to be entirely your responsibility. You can of course hire someone to help.

Do I need a system to sell physical goods at the same time as digital goods?

One of the reasons to sell digital goods is there they have no shipping costs. You can sell the same product repeatedly and there is no stock to maintain. However, you may want to sell physical items as well. If you do then you need a shopping system that is able to handle international shipping costs. As you can imagine, this can be a very complicated issue and needs quite a lot of setting up – that is why many people only sell digital items.

Systems that are able to handle international shipping usually have a way to set shipping costs per destination. So you may have a shipping cost for your own country, then a cost for Europe, and so on. The application PHP-eSeller has this feature built in.

And finally …

I have tried to explain some of the points that you should consider for a digital download system. Look around the internet and investigate applications that you install onto your web site as well as those organisations that provide a complete service.

Go to PHP-eSeller

Use PHP-eSeller to sell your downloads online and sell physical items as well.

A Shopping Cart using PHP Sessions

This posts illustrates a relatively simple shopping cart in PHP using sessions to store cart details, quantities and total cart amount.  The cart has an add to cart button , remove from cart button and displays the total value of the cart.

This post is taken from part of the book PHP Tutorials: Programming with PHP and MySQL which is available as a paper back printed version or as a downloadable Kindle version. Click here for paper back version.

The following listing is the complete shopping cart in PHP:

<?php session_start();
#cart.php - A simple shopping cart with add to cart, and remove links
 //---------------------------
 //initialize sessions

//Define the products and cost
$products = array("product A", "product B", "product C");
$amounts = array("19.99", "10.99", "2.99");

//Load up session
 if ( !isset($_SESSION["total"]) ) {
   $_SESSION["total"] = 0;
   for ($i=0; $i< count($products); $i++) {
    $_SESSION["qty"][$i] = 0;
   $_SESSION["amounts"][$i] = 0;
  }
 }

 //---------------------------
 //Reset
 if ( isset($_GET['reset']) )
 {
 if ($_GET["reset"] == 'true')
   {
   unset($_SESSION["qty"]); //The quantity for each product
   unset($_SESSION["amounts"]); //The amount from each product
   unset($_SESSION["total"]); //The total cost
   unset($_SESSION["cart"]); //Which item has been chosen
   }
 }

 //---------------------------
 //Add
 if ( isset($_GET["add"]) )
   {
   $i = $_GET["add"];
   $qty = $_SESSION["qty"][$i] + 1;
   $_SESSION["amounts"][$i] = $amounts[$i] * $qty;
   $_SESSION["cart"][$i] = $i;
   $_SESSION["qty"][$i] = $qty;
 }

  //---------------------------
  //Delete
  if ( isset($_GET["delete"]) )
   {
   $i = $_GET["delete"];
   $qty = $_SESSION["qty"][$i];
   $qty--;
   $_SESSION["qty"][$i] = $qty;
   //remove item if quantity is zero
   if ($qty == 0) {
    $_SESSION["amounts"][$i] = 0;
    unset($_SESSION["cart"][$i]);
  }
 else
  {
   $_SESSION["amounts"][$i] = $amounts[$i] * $qty;
  }
 }
 ?>
 <h2>List of All Products</h2>
 <table>
   <tr>
   <th>Product</th>
   <th width="10px">&nbsp;</th>
   <th>Amount</th>
   <th width="10px">&nbsp;</th>
   <th>Action</th>
   </tr>
 <?php
 for ($i=0; $i< count($products); $i++) {
   ?>
   <tr>
   <td><?php echo($products[$i]); ?></td>
   <td width="10px">&nbsp;</td>
   <td><?php echo($amounts[$i]); ?></td>
   <td width="10px">&nbsp;</td>
   <td><a href="?add=<?php echo($i); ?>">Add to cart</a></td>
   </tr>
   <?php
 }
 ?>
 <tr>
 <td colspan="5"></td>
 </tr>
 <tr>
 <td colspan="5"><a href="?reset=true">Reset Cart</a></td>
 </tr>
 </table>
 <?php
 if ( isset($_SESSION["cart"]) ) {
 ?>
 <br/><br/><br/>
 <h2>Cart</h2>
 <table>
 <tr>
 <th>Product</th>
 <th width="10px">&nbsp;</th>
 <th>Qty</th>
 <th width="10px">&nbsp;</th>
 <th>Amount</th>
 <th width="10px">&nbsp;</th>
 <th>Action</th>
 </tr>
 <?php
 $total = 0;
 foreach ( $_SESSION["cart"] as $i ) {
 ?>
 <tr>
 <td><?php echo( $products[$_SESSION["cart"][$i]] ); ?></td>
 <td width="10px">&nbsp;</td>
 <td><?php echo( $_SESSION["qty"][$i] ); ?></td>
 <td width="10px">&nbsp;</td>
 <td><?php echo( $_SESSION["amounts"][$i] ); ?></td>
 <td width="10px">&nbsp;</td>
 <td><a href="?delete=<?php echo($i); ?>">Delete from cart</a></td>
 </tr>
 <?php
 $total = $total + $_SESSION["amounts"][$i];
 }
 $_SESSION["total"] = $total;
 ?>
 <tr>
 <td colspan="7">Total : <?php echo($total); ?></td>
 </tr>
 </table>
 <?php
 }
 ?>

The cart example uses the following sessions to maintain the state of the cart:

$_SESSION[“qty”][i] Stores the quantity for each product
$_SESSION[“amounts”][i] Stores the price from each product
$_SESSION[“cart”][i] Identifies which items have been added to the cart
$_SESSION[“total”] Stores the total cost

The sessions are actually arrays so in the case of:

$_SESSION[“qty”][i]

is the quantity for the element with number i.

Description of the PHP shopping cart code

We start by defining PHP to use sessions by:

session_start();

This has to be at the very top of the PHP page.

Next we set up our products and populate our sessions. In this example we are using a fixed array of product descriptions and amounts. You may want to do this in your application or you could read in the data into the $product and $amounts array from a database.

//---------------------------
 //initialise sessions

 //Define the products and cost
 $products = array("product A", "product B", "product C");
 $amounts = array("19.99", "10.99", "2.99");

 if ( !isset($_SESSION["total"]) ) {

  $_SESSION["total"] = 0;

  for ($i=0; $i< count($products); $i++) {
   $_SESSION["qty"][$i] = 0;
   $_SESSION["amounts"][$i] = 0;
 }
}

The following code will reset and clear the sessions when the Reset Cart link is selected.

//---------------------------
  //Reset
  if ( isset($_GET['reset']) )
   {
    if ($_GET["reset"] == 'true')
    {
     unset($_SESSION["qty"]); //The quantity for each product
     unset($_SESSION["amounts"]); //The amount from each product
     unset($_SESSION["total"]); //The total cost
     unset($_SESSION["cart"]); //Which item has been chosen
   }
}

The following code adds an item to the sessions when the ‘Add to Cart’ link is clicked:

//---------------------------
//Add
if ( isset($_GET["add"]) )
{
$i = $_GET["add"];

$qty = $_SESSION["qty"][$i] + 1;

$_SESSION["amounts"][$i] = $amounts[$i] * $qty;
$_SESSION["cart"][$i] = $i;
$_SESSION["qty"][$i] = $qty;
}

and the following deletes an item from the cart when the ‘Delete from Cart’ link is clicked:

 //---------------------------
 //Delete
 if ( isset($_GET["delete"]) )
 {
   $i = $_GET["delete"];
   $qty = $_SESSION["qty"][$i];
   $qty--;
   $_SESSION["qty"][$i] = $qty;

 //remove item if quantity is zero
 if ($qty == 0) {
   $_SESSION["amounts"][$i] = 0;
   unset($_SESSION["cart"][$i]);
 }
 else
 {
   $_SESSION["amounts"][$i] = $amounts[$i] * $qty;
 }
 }

The rest of the code is the visual display using a table and various loops to show the product lists and the cart details together with the links.

This post is taken from part of the book PHP Tutorials: Programming with PHP and MySQL which is available as a paper back printed version or as a downloadable Kindle version. Click here for paper back version.

PHP-eSeller is a complete application that utilizes a shopping cart using PHP Sessions and is available from here:

Click here to go to PHP-eSeller web page

Analyzing Google Webmaster Statistics with Spreadsheets

The Google Tools

There are two Google tools that you can use to analyze the performance of your web site.  One is Google Analytics, the other is Google Webmasters.  There two systems give different views and statistics on your web site.

When you set up Google Webmasters you have to make sure that the URLs are set up properly and you need to set up conical reference.

Once Google is providing statistics into the webmaster tool, you can start analyzing the data.  However, you may have to wait for several months before you do a proper analyses and see trends.

Google Webmasters provide statistics on how your web site is performing with Google Search.  In particular, it includes information on the queries that the user entered into Google to arrive at a given page, click through rates and information on the search position.  All of these give you an understanding of search engine performance for you web site.

Downloading Your Data

The following assumes that you have already set up your web site and that data is available.

1) Log into Google Webmasters using your Google account.

The Search Console will be displayed.

2 ) Click on the Website that you want to analyze.

In the left hand navigation column, click on “Performance” and in the right hand side, scroll down to where you will see a list of “Queries” with the number of clicks and impressions.

3) Go to the “Download” icon and click on it.

4) Save the download as either a csv file or as a Google Sheets file.  I normally download as Google Sheets format and then open it up in an old version of Microsoft Excel to do the calculations.  However, you can just work with Google Sheets as it does the job just as well.

5) Sort the complete spreadsheet in order of “Impressions”, highest to lowest.

6) Add in a new column called %Impressions.

We want to calculate each value as a percentage of the total impressions.  So for each entry:
( Impression Cell Value )  / SUM( All Impression Values ) * 100

7) Add in a new column called %Cumulative

We want to add up all the cumulative values using the formulae =
%Impression + %Previous Cumulative Value

8) From the cumulative percentage we can look at the top 50% of queries which are the most important and do out analysis on that.

Doing The Analyses

We can now look at our listing and see which of the keywords in the top 50% have low CTR (Click Through Rate).  We can see that these keywords are giving high number of impressions on our site, but some are not returning good CTR.

We can look at these queries, and then try to work out how we can improve the click through rate.

The Other Downloads

From the same location we can download “Queries”, “Countries”, “Pages” and “Devices”.  From each one we can investigate different issues. 

The first thing to notice is that if you add up the impressions in each one, you will notice that they are different.  So what is going on here?

Well, Google has a page at:

https://support.google.com/webmasters/answer/7576553

There is a section called “Data Discrepancies” which gives some explanations for the differences.  Essentially, the issue with the queries download is that it is limited to 1000 rows, so there will be some loss of data although these will be from queries that are rarely made.  There is also loss of data for other reasons, such as excluding queries with “private” data, time lag differences in data processing, different processing techniques for different data sources and so on.

Comparing Across Dates

Luckily for us, Google has provided us with features to compare one set of data with another over time.

If you click on the Date Compare button, you can select Filter or Compare. In the Compare list you can then choose the date range for comparison.

The resulting csv file provides with a wealth of information of changing data over time.

Example Spreadsheet File

If you click on the following link you can download and example xls spreadsheet which shows the spreadsheet formula.

Click here for example spreadsheet.

 

Posted in SOE

Updating SQLite database when updating a Xamarin Forms App for iOS and Andriod

Deploying a SQLite Database in a Xamarin Project

I use SQLite databases in some of my Xamarin projects and hence there is a requirement to make sure that anyone who has my application has the updated database when I do an update through Google Play or the Apple Store.

So in a situation where you have a pre-populated SQLite database, the first thing to do is to copy the database into the deployed location. This procedure is done when the application starts up by checking if the database already exists or not.

You add the SQLite database into the Assets folder for Android and into the Resources folder for iOS.

(Right click on the project Assets folder or Resources folder, select “add existing” and then browse to the SQLite file and select it).

In your Android project create a new class file FileAccessHelper.cs at the root of the Android project.

 

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;

using Android.App;
using Android.Content;
using Android.OS;
using Android.Runtime;
using Android.Views;
using Android.Widget;

namespace SQLiteDeploy.Droid
{
   public class FileAccessHelper
   {

    /// <summary>
    /// This copies the database over if it does not exist on the Andriod device
    /// </summary>
    /// <param name="dbPath">The path of the database on the Andriod device</param>
    /// <param name="SQLiteAsset">The database name in the assets folder</param>
    public static void CopyDatabaseIfNotExists(string dbPath, string SQLiteAsset)
       {
       if (!File.Exists(dbPath))
          {
             using (var br = new BinaryReader(Application.Context.Assets
                                                                    .Open(SQLiteAsset)))
             {
                using (var bw = new BinaryWriter(new FileStream(dbPath, 
                                                                     FileMode.Create)))
                {
                 byte[] buffer = new byte[2048];
                 int length = 0;
                 while ((length = br.Read(buffer, 0, buffer.Length)) > 0)
                    {
                       bw.Write(buffer, 0, length);
                    }
                 }
              }
           }

        }

     }
}

In the MainActivity.cs file:

 

//Copy over database
var SQLiteAsset = "SQLite.db3"; //database name
//get path of database
var documentsPath = System.Environment.GetFolderPath(System.Environment
                                   .SpecialFolder.MyDocuments);
var path = System.IO.Path.Combine(documentsPath, SQLiteAsset);
FileAccessHelper.CopyDatabaseIfNotExists(path, SQLiteAsset);

In your iOS project create a new class file FileAccessHelper.cs at the root of the iOS project.

 

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;

using Foundation;
using UIKit;

namespace SQLiteDeploy.iOS
{
     public class FileAccessHelper
     {

        /// <summary>
        /// This copies the database over if it does not exist on the iOS device
        /// If the file does not exist in dbPath, then copy it from SQLiteAsset location
        /// </summary>
        /// <param name="dbDestination">The path of the database on the iOS device</param>
        /// <param name="SQLiteAsset">The database name in the installation folder</param>
        public static void CopyDatabaseIfNotExists(string dbDestination, string SQLiteAsset)
        {

           //If file does not exist, then copy file to it from the resources folder
           //located at NSBundle.MainBundle.PathForResource
           if (!File.Exists(dbDestination))
           {
              var ext = Path.GetExtension(SQLiteAsset);
              var name = Path.GetFileNameWithoutExtension(SQLiteAsset);
              var dbSource = NSBundle.MainBundle.PathForResource(name, ext);

              //check if database exists at source first.
              if (File.Exists(dbSource))
              {
                  File.Copy(dbSource, dbDestination, true);
              }
            }
          }

     }
}

In the iOS Main.cs file:

 

     //Copy over database
     var SQLiteAsset = "SQLite.db3"; //database name

     var documentsPath = System.Environment.GetFolderPath(System.Environment
                                     .SpecialFolder.MyDocuments);
     var dbDestinationFolder = System.IO.Path.Combine(documentsPath, SQLiteAsset);

     //path is the full folder and file name, SQLLiteAssest is just the name of the file
     FileAccessHelper.CopyDatabaseIfNotExists(dbDestinationFolder, SQLiteAsset);

How do I test if it works?

Note that you can only test your application by completly deleting the application from the tablet or phone and then re-deploying it because the database may have already been copied over in a previous deployment.

 

Updated application but with new data in the database

When you want to deploy a new version of a database where the application is already available on the Google Play store and Apple store, then you need to make sure that the original database gets overwritten with the new database on the customer phone or tablet if different data is needed. The only way I can see to do that at the moment is to read the file size and compare it with the deployed value. This can be done in the FileAccessHelper.cs file.

The following is a modified version of FileAccessHelper.cs

 

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;

using Android.App;
using Android.Content;
using Android.OS;
using Android.Runtime;
using Android.Views;
using Android.Widget;

namespace KitchenTimer.Droid
{
       public class FileAccessHelper
       {

         /// <summary>
         /// This copies the database over if it does not exist on the Andriod device
         /// </summary>
         /// <param name="dbPath">The path of the database on the Andriod device</param>
         /// <param name="SQLiteAsset">The database name in the assets folder</param>
         public static void CopyDatabaseIfNotExists(string dbPath, string SQLiteAsset)
         {
            if (!File.Exists(dbPath))
            {
               using (var br = new BinaryReader(Application.Context.Assets
                                                                    .Open(SQLiteAsset)))
               {
                  using (var bw = new BinaryWriter(new FileStream(dbPath, 
                                                                     FileMode.Create)))
                  {
                   byte[] buffer = new byte[2048];
                   int length = 0;
                   while ((length = br.Read(buffer, 0, buffer.Length)) > 0)
                     {
                        bw.Write(buffer, 0, length);
                     }
                }
            }
        }
         else
        {

           //if file does exist, compare the file sizes
           long fileDeploySize = 20480; //deployment size of file.

          try
          {

           FileInfo fSQLiteAsset2 = new FileInfo(dbPath);
           long s2 = fSQLiteAsset2.Length;

               if (s2 != fileDeploySize)
               {
                  using (var br = new BinaryReader(Application.Context.Assets
                                                                   .Open(SQLiteAsset)))
                  {
                     using (var bw = new BinaryWriter(new FileStream(dbPath,
                                                                    FileMode.Create)))
                     {
                        byte[] buffer = new byte[2048];
                        int length = 0;
                        while ((length = br.Read(buffer, 0, buffer.Length)) > 0)
                        {
                           bw.Write(buffer, 0, length);
                        }
                     }
                 }
              }

          }
          catch
          {

          }

       }

    }
  }
}

In the above code, if the database already exists, I check the size of the database file and if they are different I copy the file over to the deploy location, overwriting the existing database file. In the above example the new database file size is 20480 bytes. So you do have to enter this value manually which may not be the best method.

 

How do I create a SQLite database and add my tables and data

I use “DB Browser for SQLite” which you can download from http://sqlitebrowser.org/ for Windows and Mac computers.

 

Using SQLite in your Xamarin Projects

This briefly describes using SQLite in your Xamarin Project and provides a simple exampple of the code:

** Add sqlite-net-pcl to all projects. This is the Nuget package sqlite-net-pcl by Frank A. Krueger
** Declare interface in PCL. You need to set up a connection Interface in the PCL project
** Implement in each application project. You then need to implement the interface in each of the other projects.

So first in the PCL project, create a folder called Persistence. Then create a new class interface file called
ISQLiteDb.cs as follows:

 

using SQLite;
using System;
using System.Collections.Generic;
using System.Text;

namespace SQLiteDeploy.Persistence
{
    public interface ISQLiteDb
    { 
      SQLiteAsyncConnection GetConnection();
    }
}

Now in both the Android Project and iOS project create a folder called Persistence. Then create a class file called SQLiteDb.cs with the following code which is the same for the Android file and iOS file:

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Android.App;
using Android.Content;
using Android.OS;
using Android.Runtime;
using Android.Views;
using Android.Widget;
using Xamarin.Forms;
using SQLite;
using SQLiteDeploy.Persistence;
using SQLiteDeploy.Droid.Persistence;
using System.IO;

[assembly: Dependency(typeof(SQLiteDb))]
namespace KitchenTimer.Droid.Persistence
{
    public class SQLiteDb : ISQLiteDb
    {
       public SQLiteAsyncConnection GetConnection()
       {
        var documentsPath = System.Environment.GetFolderPath(System.Environment
                                 .SpecialFolder.MyDocuments);
        var path = System.IO.Path.Combine(documentsPath, "SQLite.db3");

        return new SQLiteAsyncConnection(path);
       }

     }
}

You can download my example of this from here:

https://github.com/paulv200/SQLiteDeploy.git

 

Do I need SSL on my Web Server for use with PayPal IPN?

PayPal upgrades:

1) Changes to PayPal security for HTTP/1.1 and TLS 1.2
2) IPN Verification Postback to HTTPS

PayPal say their target for these is June 2018

 

You may have seen an article from PayPal which talks about HTTP and TLS. This is a server issue
which is the responsibility of your web host. Essentially there is an upgrade by PayPal to ensure that
all their servers meet the latest standards and hence your web server will also require to operate
in that way. You should contact your host about this if you are unsure, although most servers will already
meet these standards.

 

The second update is using HTTPS for IPN verification.

PayPal says:

“If you are using PayPal’s instant Payment Notification (IPN) service, you will need to ensure
that HTTPS is used when posting the message back to PayPal for verification. HTTP PostBacks
will no longer be supported.”

“Merchants and partners use Instant Payment Notification (IPN) to receive notifications of
events related to PayPal transactions. The IPN message service requires that you acknowledge
receipt of these messages and validate them. This process includes posting the messages back
to PayPal for verification. In the past, PayPal has allowed the use of HTTP for these
PostBacks . For increased security going forward, only HTTPS will be allowed for PostBacks to
PayPal. At this time, there is no requirement for HTTPS on the outbound IPN call from PayPal
to the merchant’s IPN listener.”

In practice this means that you need https on your web server to send https to PayPal and
then you need to change the set up of you PHP-KeyCodes, PHP-eSeller or PHP-SecureArea
application to identify the URL of the site as https rather than http.

Note: if a customer tried to make a purchase it would still succeed, but your store would
not be notified of that fact, and your records will not reflect the sale properly. Nor
will the PHP application automatically send the information to the customer by email.

Refer to PayPal documentation:

https://www.paypal.com/au/webapps/mpp/ipn-verification-https

So do I need SSL on my Web Server when using PayPal IPN? Yes, the PostBack from PayPal will not work
and SSL will give better security for your site.

WordPress Plugin for selling License Key Codes – PHP-KeyCodes

Hello and welcome fellow programmers.

A WordPress version of PHP-KeyCodes has just been released which has most of the functionality of the standalone PHP/MySQL application PHP-KeyCodes.

The plugin can be viewed and downloaded from the following link:

https://wordpress.org/plugins/withinweb-php-keycodes/

The plugin allows you to sell per-configured license codes, pin numbers, mobile phone numbers or any other similar codes from your WordPress installation using a PayPal account.

Using Subversion for WordPress Plugins

Subversion Control for WordPress

If you write a plugin for WordPress and want to make it available on WordPress.org, you have to use Subversion to handle revisions, versions and source control.

There are a number of programs that you can use and which can be installed on a PC or a MAC. Many do use the command line which can be difficult for those not familiar with the particular commands. However, there are a number of client applications that have very good user interfaces which does make the process much easier.

SilkSVN Windows Command Line Client. https://sliksvn.com/download/
TortoiseSVN for Windows which can be downloaded at https://tortoisesvn.net/downloads.html
Versions for the Mac which can be downloaded at http://versionsapp.com

When I first began to look at WordPress and SVN, I wanted to use a visual method of updating. I wanted to click a button and for it to automatically do everything without thinking. However, I found that using a visual interface did not show the point of SVN and when I started to use a text interface, I found I understood the process much better. Also there is a lot of documentation available for the command line interface which is helpful when things go wrong.

The following are some notes that I have made over the years about using the command line system with WordPress which may help if you are having issues. It is based on the WordPress.org documentation but I have added in some extra points.

 

Command Line SVN

As I use Windows I use SilkSVN.  Download and install SilkSVN and in the cmd terminal (go to Windows Start and enter cmd.exe), type “svn” followed by the required commands. “svn help” will show help text and will show that SilkSVN is working.

The documentation that WordPress has produced is located here:

https://developer.wordpress.org/plugins/wordpress-org/how-to-use-subversion/

The only issue I have found when using SilkSVN is that you should use double quotes wherever it uses single quotes as in the standard SVN documentation.

Also, always make sure you change directory to where your local repository is located.

 

Starting a new plugin

On your computer, first create a local directory which will be your copy of the SVN repository.

In this document I will use my-local-dir to refer to the local directory.

Next, check out the pre-built repository using the co command:

my-local-dir / svn co https://plugins.svn.wordpress.org/your-plugin-name my-local-dir
> A my-local-dir/trunk
> A my-local-dir/branches
> A my-local-dir/tags
> Checked out revision 11325.

 

You will probably be asked your username and password which will be the one you were given when you registered for WordPress.org.

The above command will create a set of folders on your local computer in your local directory called “trunk”, “branches” and “tags”.

The “tags” folder is for your releases and will consist of a number of sub folders that correspond to your version numbers.

The “trunk” folder is where your development version is located.

Copy your files into the “trunk” folder and then you have to let subversion know you want to add the files into the repository:

my-local-dir / svn add trunk/*

 

Now check in the changes:

my-local-dir / svn ci -m "Check in to trunk"

 

Now copy the trunk files to the required tag folder.  In this example I am going to create a folder 1.0.0

my-local-dir / svn cp trunk tags/1.0.0
> A tags/1.0.0

 

and commit the changes:

my-local-dir / svn ci -m "tagging version 1.0.0"

So now on the remote repository you should have your released files in the tag/1.0.0 and your current working development files will also be in the “trunk” folder.

 

Creating a new version

We want to create a new version of our plugin so we:
* Modify the readme.txt with the new version number.
* Modify the main php file with the new version number.
* Update the trunk folder so that it contains the latest files.
* Copy the trunk folder to a new tags folder using SVN.
* Check in the changes using SVN.

Essentially the method is the same as described above but with a few more checks.

On your local computer, make sure that your trunk files are up to date.

We can do this by using the SVN update command. (First, change your directory to where the SVN local directory is situated):

my-local-dir / svn update

 

Files in the remote repository are downloaded and update the local files.

Copy over the file or files to your local trunk folder and then let subversion know you want to add those files back:

my-local-dir / svn add trunk/*

 

Now commit the changes:

my-local-dir / svn ci –m “add new files to trunk”

 

Now do a status display:

my-local-dir / svn stat

You can see the meaning of the status information further at the end of this article.

 

At this point, you may have to delete files / folders by:

my-local-dir / svn delete 

In some cases, you may have to use –force to force the deletion.

 

When you do:

my-local-dir / svn stat

you should now see those files/folders marked for deletion.

 

Deleting only deletes it form the local copy and only schedules if for deletion from the repository. You have to then commit the changes by:

my-local-dir / svn ci -m "New files update"
> Sending trunk/my-plugin.php
> Transmitting file data .
> Committed revision 11327.

 

That updates the files in the trunk folder and deletes any marked files.

Now that you have the files checked in to the trunk folder, you can copy over the files to a tag folder as described above.

 

You do this by:

my-local-dir / svn cp trunk tags/2.0
> A tags/2.0

 

Now, as always, check in the changes.

my-local-dir / svn ci -m "tagging version 2.0"
> Adding tags/2.0
> Adding tags/2.0/my-plugin.php
> Adding tags/2.0/readme.txt
> Committed revision 11328.

 

The assets folder

Use the assets folder for files like screenshots, banners and icons for wordpress.org site. Note that the assets folder is a separate folder in the root of the folder structure and is not a subfolder of the trunk or tag folder.

Update as the assets folder follows.

 

Navigate to the local folder which is the root of the SVN, then enter:

my-local-dir / svn add assets/*
>  A assets/sceenshot-1.png
>  A assets/sceenshot-2.png

The “add assets/*” identifies all files in the assets folder.

 

After you add all your files, you need to check in the changes back to the central repository using ci:

my-local-dir / svn ci -m “Add assets”
> Adding assets/screenshot-1.png
> Adding assets/screenshot-2.png
> Transmitting file data
> Committed revision 1124545

 

Status Command

A useful command is “svn status” which gives information about the state of the repository.

 my-local-dir / svn stat

 

If you see ? in the list of files or folders, it means that they are not under svn control. In which case do “svn add
To use this command as with all the other commands, you need to change directory on your local computer to the root folder of the SVN.

U: Working file was updated
G: Changes on the repository were automatically merged into the working copy
M: Working copy is modified
C: This file conflicts with the version in the repository
?: This file is not under version control
!: This file is under version control but is missing or incomplete
A: This file will be added to version control (after commit)
A+: This file will be moved (after commit)
D: This file will be deleted (after commit)
S: This signifies that the file or directory has been switched from the path of the rest of the working copy (using svn switch) to a branch
I: Ignored
X: External definition
~: Type changed
R: Item has been replaced in your working copy. This means the file was scheduled for deletion, and then a new file with the same name was scheduled for addition in its place.
L : Item is locked
E: Item existed, as it would have been created, by an SVN update.

 

Problems, problems, problems

If you get a message ‘folder name’ is scheduled for addition, but is missing then look at the following document:

https://benohead.com/svn-file-scheduled-addition-missing/

Also look at:

http://svnbook.red-bean.com/en/1.6/svn.ref.svn.c.delete.html

The method that I have found to use is rm (remove), or you may use the delete command. So you do something like:

my-local-dir / svn rm –force c:\csv\withinweb_wp_keycodes\withinweb-php-keycodes\tags\2.0.0\views

Once you have removed all the problems you should be able to commit.

Note that you may have change the local directory to the file or folder that you are going to delete or you have to enter the full path name of the file or folder.

 

Checking differences

Checking files are at the correct version can be done by doing a diff.