Category: PHP Updates

Last modified on November 27th, 2019 by Vincy.

Sales, revenue, ROI are the key metrics to measure a business growth. The payment gateway is a sensitive factor that affects these metrics directly. It is the first and foremost item on the checklist for any eCommerce business.

There are many Payment Gateway providers. PayPal being the popular provider and 2Checkout is catching out fast. Earlier I wrote about how to integrate PayPal checkout in ecommerce website. In this payment gateway integration series, now it is time to see about 2Checkout. 

2Checkout is one of the best payment gateway provider to support card payments. It works similar to Stripe payment gateway. Card payments has less friction and will enable to increase the conversion rate in your eCommerce business.

2Checkout is growing everyday in popularity. It is available in more than 180 countries and used by 17,000 firms.

To integrate 2Checkout to your ecommerce application there are few simple steps. I have described those steps in this article to make your job easier.

Also, we will see the card validator script, payment request-response handlers and more.

What is inside?

1. Existing payment gateway methods supporting card payment
2. Advantages of 2Checkout payment
3. File structure
4. Steps to integrate 2Checkout payment gateway using PHP
5. Database script
6. Go live
7. 2Checkout Payment gateway example output

Existing payment gateway methods supporting card payment

I have been writing about payment gateways for a while We have already seen many types of payment gateway integration using PHP. I already written tutorials for CCAvenue, PayPal, Stripe, SagePay and Authorize.Net. 

2Checkout, Stripe, CCAvenue are some of the examples for the methods supporting card-payments. There are many methods like InstaMojo, Cashfree, EBS and Paytm support card-payments.

Advantages of 2Checkout payment

• Easy to integrate using payment API libraries in PHP, PYTHON and more languages.
• It allows customers to opt for a variety of payment methods via PayPal account or debit/credit cards.
• 2Checkout is a secured fraud-proof for selling products or subscription plans online.
• It provides a localized buying experience for the customer.
• It supports many currencies that supports global payments.
• Integratable with the eCommerce tools like Shopify, Bigcommerce.

File structure

This image shows the file structure of the 2Checkout payment gateway PHP example. The root files index.php, payment.php, return.php are in a sequential flow during the payment.

While running this example, it lands with the index.php to show the payment form. Form targets payment.php that processes payment and redirects to the return page.

The redirect URL will have the payment response status parameter. It helps to create a response HTML on the return page. 

The assets folder has the CSS, JS created for this example. TwoCheckoutService.php contains the library includes to verify card details and process payment.

You can see all the third-party components in the vendor directory.

Steps to integrate 2Checkout payment gateway using PHP

1. Download the 2Checkout payment API library in PHP.
2. Create a 2Checkout account and generate API keys.
3. Create a payment form to collect the card details from the customer.
4. Generate payment API token and transfer the card details in a secure way.
5. Verify card details and process charges with 2Checkout Payment API.

Step 1: Download 2Checkout payment gateway

2Checkout payment API library has many features. It helps to manage purchase flow, orders, invoices, billing and more. It simplifies the integration process and reduces the developer’s effort.

Download the 2Checkout payment API library from GitHub. And then, put it into your application vendor directory.

While integrating Stripe Billing, we saw how to integrate third-party API for payment.

Include the TwoCheckout.php service before verifying card details and process payments. This file includes all other library dependencies. It handles card validation and charges on the server-side.  

In this example, the Checkout.php use this library file to perform the payment operations.

Step 2: Creating 2Checkout sandbox account and generate API keys

2Checkout provides a sandbox environment to check payment flow using the API functions. 

This is a common practice to go with a sandbox mode for testing purposes. Then we can go live after verifying the payment flow with this mode.

Let us create a 2Checkout sandbox account via the developer console. By logging into the console, we can get our API keys.

1. Register and log in to the 2Checkout sandbox account.
2. Select the API menu from the header and click the Start Integrating button.
3. Go to the Settings tab to get the API keys.
4. Copy the Publishable Key and Private Key in the key generation section of the page.

Click the Generate button if you are not able to see the API keys.

I have added the keys and seller account id in the application config name Config.php.

After configuring the API keys, use the test data to make the payment in the sandbox mode. You can get the test data from the API page as shown below.

Step 3: Create 2Checkout payment form in HTML

This form contains input fields to get the basic credit card details from the buyer. Those are the cardholder’s name and email, card number, CVC, card expiry tenure.

It will also ask the billing address from the buyer.

In this payment form, it has hidden fields for keeping the seller account id and product price, code.

<?php namespace Phppot; use Phppot\Config; require_once "Config.php"; $productCode = "WWPS235"; ?> <html> <head> <meta name="viewport" content="width=device-width, initial-scale=1"> <link href="./assets/css/style.css" rel="stylesheet" type="text/css"> <title>2checkout-payment-gateway-integration-using-PHP</title> </head> <body> <div id="formContainer"> <?php $productDetail = Config::productDetail(); $itemName = $productDetail[$productCode]["itemName"]; $itemPrice = $productDetail[$productCode]["itemPrice"]; ?> <div class="product-row"> <p class="product"><?php echo $itemName; ?></p> <div class="price float-right"><?php echo $itemPrice; ?> <?php echo Config::CURRENCY; ?> </div> </div> <form id="paymentForm" novalidate method="post" action="payment.php"> <input type="hidden" id="itemNumber" name="itemNumber" value="<?php echo $productCode; ?>" /> <input type="hidden" id="itemPrice" name="itemPrice" value="<?php echo $itemPrice; ?>" /> <input type="hidden" id="seller_id" value="<?php echo Config::SELLER_ID; ?>" /> <input type="hidden" id="publishable_key" value="<?php echo Config::PUBLISHABLE_KEY; ?>" /> <div class="field-row col2 float-left"> <label>Card Holder Name</label> <input type="text" class="demoInputBox required" name="cardHolderName" id="cardHolderName"> </div> <div class="field-row col2 float-right"> <label>Email</label> <input type="email" class="demoInputBox required" name="cardHolderEmail" id="cardHolderEmail"> </div> <div class="field-row"> <label>Card Number</label> <input type="text" class="demoInputBox required" name="cardNumber" id="cardNumber"> </div> <div class="field-row col2 float-left"> <label>Expiry Month / Year</label> <br /> <select name="expiryMonth" id="expiryMonth" class="demoSelectBox required"> <?php $months = Config::monthArray(); $count = count($months); for ($i = 0; $i < $count; $i ++) { $monthValue = $i + 1; if (strlen($i) < 2) { $monthValue = "0" . $monthValue; } ?> <option value="<?php echo $monthValue; ?>"><?php echo $months[$i]; ?></option> <?php } ?> </select> <select name="expiryYear" id="expiryYear" class="demoSelectBox required"> <?php for ($i = date("Y"); $i <= 2030; $i ++) { $yearValue = substr($i, 2); ?> <option value="<?php echo $yearValue; ?>"><?php echo $i; ?></option> <?php } ?> </select> </div> <div class="field-row"> <label>CVV</label><br /> <input type="text" name="cvv" id="cvv" class="demoInputBox cvv-input required"> </div> <p class="sub-head">Billing Address:</p> <div class="field-row col2 float-left"> <label>Address Line1</label> <input type="text" class="demoInputBox required" name="addressLine1" id="addressLine1"> </div> <div class="field-row col2 float-right"> <label>Address Line2</label> <input type="email" class="demoInputBox" name="addressLine2" id="addressLine2"> </div> <div class="field-row col2 float-left"> <label>Country</label> <input type="text" class="demoInputBox required" name="country" id="country"> </div> <div class="field-row col2 float-right"> <label>State</label> <input type="text" class="demoInputBox required" name="state" id="state"> </div> <div class="field-row col2 float-left"> <label>City</label> <input type="text" class="demoInputBox required" name="city" id="city"> </div> <div class="field-row col2 float-right"> <label>Zip</label> <input type="text" class="demoInputBox required" name="zip" id="zip"> </div> <div class="clear-float"> <input id="token" name="token" type="hidden" value=""> <input type="button" id="submit-btn" class="btnAction" value="Send Payment"> <div id="loader"> <img alt="loader" src="./images/LoaderIcon.gif" /> </div> </div><div id="error-message"></div> </form> </div> </body> </html> 

Javascript for credit card validation

Most of the fields are mandatory. The form validation script helps to verify user’s input before payment.

I have used the jQuery CreditCardValidator script to validate the card details. It will check the card number format, CVC, card expiration and more. 

Note: To pass the card validation, any three-digit code for CVC and a future date for card expiry.

function validate() { var valid = true; $(".demoInputBox").css('background-color', ''); var message = ""; var cardHolderNameRegex = /^[a-z ,.'-]+$/i; var cvvRegex = /^[0-9]{3,3}$/; var cardHolderName = $("#cardHolderName").val(); var cardHolderEmail = $("#cardHolderEmail").val(); var cardNumber = $("#cardNumber").val(); var cvv = $("#cvv").val(); $(".required").each(function() { if($(this).val()=='') { $(this).css('background-color', '#FFFFDF'); valid = false; } }); if(!valid) { message += "<div> Highlighted fields are required.</div>"; } if (cardHolderName != "" && !cardHolderNameRegex.test(cardHolderName)) { message += "<div>Card Holder Name is invalid</div>"; $("#cardHolderName").css('background-color', '#FFFFDF'); valid = false; } if (!cardHolderEmail.match( /^([\w-\.]+@([\w-]+\.)+[\w-]{2,4})?$/)) { message += "<div>Email is invalid</div>"; $("#cardHolderEmail").css('background-color', '#FFFFDF'); valid = false; } if (cardNumber != "") { $('#cardNumber').validateCreditCard(function(result) { if (!(result.valid)) { message += "<div>Card Number is Invalid</div>"; $("#card-number").css('background-color', '#FFFFDF'); valid = false; } }); } if (cvv != "" && !cvvRegex.test(cvv)) { message += "<div>CVV is Invalid</div>"; $("#cvv").css('background-color', '#FFFFDF'); valid = false; } if (message != "") { $("#error-message").show(); $("#error-message").html(message); $("#submit-btn").show(); $("#loader").hide(); } return valid; } 

Step 4: 2Checkout JS to request token

This HTML code contains the 2co.js include at the end. This JS script is for getting TCO token by hitting the API’s tokenRequestURL. 

This request requires card details with seller account id, sandbox publishable key. It also specifies the name of the success/error callback functions.

The success callback gets the token and includes it to the HTML form. f anything went wrong, the error-callback handles the case and acknowledge the customer.

This token helps to authenticate the request before processing the payment. Without this token, the payment request will return error by saying unauthorized.

<!-- jQuery library --> <script src="vendor/jquery/jquery-3.2.1.min.js"></script> <script src="vendor/jquery-creditcardvalidator/jquery.creditCardValidator.js"></script> <script src="./assets/js/validation.js"></script> <!-- 2Checkout JavaScript library --> <script src="https://www.2checkout.com/checkout/api/2co.min.js"></script> <script> // A success callback of TCO token request var success = function (data) { // Set the token in the payment form $('#paymentForm #token').val(data.response.token.token); $("#error-message").hide(); $("#error-message").html(""); // Submit the form with TCO token $('#paymentForm').submit(); }; // A Error callback of TCO token request. var error = function (data) { var errorMsg = ""; if (data.errorCode === 200) { tokenRequest(); } else { errorMsg = data.errorMsg; $("#error-message").show(); $("#error-message").html(errorMsg); $("#submit-btn").show(); $("#loader").hide(); } }; function tokenRequest() { var valid = validate(); if (valid == true) { $("#submit-btn").hide(); $("#loader").css("display", "inline-block"); var args = { sellerId: $('#seller_id').val(), publishableKey: $('#publishable_key').val(), ccNo: $("#cardNumber").val(), cvv: $("#cvv").val(), expMonth: $("#expiryMonth").val(), expYear: $("#expiryYear").val() }; // Request 2Checkout token TCO.requestToken(success, error, args); } } $(function () { TCO.loadPubKey('sandbox'); $("#submit-btn").on('click', function (e) { tokenRequest(); return false; }); }); </script> 

Step 5: Verify card details and process charges with 2Checkout payment API

After submitting the tokenized form data, then PHP will handle the payment process. There are two major steps during the payment process authorization and charging card.

The payment.php file is the endpoint that verifies payment and charges the card. The below code states how to receive payment form data and request payment.

Before processing payment, this PHP code inserts order into the database table. In this step, it saves the product, currency, customer and billing data into the database.

After placing the order, the inserOrder method will return a unique reference ID. This refers to the merchantOrderId parameter while charging the card.

With a success response, it will update the transaction id and payment status in the order table.

In case of error, the error message returned by the API will acknowledge the customers.

<?php use Phppot\Config; use Phppot\Model\Checkout; session_start(); require_once 'Config.php'; // Check if token is not empty if (! empty($_POST['token'])) { $token = $_POST['token']; $currency = Config::CURRENCY; // Card information $card_num = $_POST['cardNumber']; $card_cvv = $_POST['cvv']; $card_exp_month = $_POST['expiryMonth']; $card_exp_year = $_POST['expiryYear']; // Customer information $customerDetail['name'] = $_POST['cardHolderName']; $customerDetail['email'] = $_POST['cardHolderEmail']; $billingAddress['addrLine1'] = $_POST['addressLine1']; $billingAddress['addrLine2'] = $_POST['addressLine2']; $billingAddress['city'] = $_POST['city']; $billingAddress['state'] = $_POST['state']; $billingAddress['zipCode'] = $_POST['zip']; $billingAddress['country'] = $_POST['country']; // Product information $product['itemNumber'] = $_POST["itemNumber"]; $product['itemPrice'] = $_POST["itemPrice"]; require_once 'Model/Checkout.php'; $checkout = new Checkout(); $orderID = $checkout->insertOrder($customerDetail, $billingAddress, $product); require_once 'Model/TwoCheckoutService.php'; $twoCheckoutService = new TwoCheckoutService(); $twoCheckoutService->verifyAuthentication(); $successMessage = ""; $errorMessage = ""; $paymentResponse = $twoCheckoutService->chargeCard($orderID, $token, $currency, $customerDetail, $billingAddress, $product['itemPrice']); if (! empty($paymentResponse["charge"])) { $charge = $paymentResponse["charge"]; if ($charge['response']['responseCode'] == 'APPROVED') { $transactionId = $charge['response']['transactionId']; $status = $charge['response']['responseCode']; $checkout->updatePayment($transactionId, $status, $orderID); header("Location: return.php?status=success&itemNumber=".$orderID); } else { $_SESSION["transaction_error"] = "Payment is waiting for approval."; header("Location: return.php?status=transaction_failed"); } } else if($paymentResponse["message"]) { if(!empty($paymentResponse["message"])) { $_SESSION["transaction_error"] = $paymentResponse["message"]; } header("Location: return.php?status=transaction_failed"); } } else { header("Location: return.php?status=invalid_token"); } ?> 

Model/Checkout.php

<?php /** * Copyright (C) 2019 Phppot * * Distributed under MIT license with an exception that, * you don’t have to include the full MIT License in your code. * In essense, you can use it on commercial software, modify and distribute free. * Though not mandatory, you are requested to attribute this URL in your code or website. */ namespace Phppot\Model; use Phppot\DataSource; use Phppot\Config; class Checkout { private $ds; function __construct() { require_once __DIR__ . './../lib/DataSource.php'; $this->ds = new DataSource(); } /** * to get the member record based on the subscription_key * * @param string $subscriptionKey * @return array result record */ public function getOrder($orderId) { $query = 'SELECT * FROM tbl_order where id = ?'; $paramType = 'i'; $paramValue = array( $orderId ); $result = $this->ds->select($query, $paramType, $paramValue); return $result; } public function insertOrder($customerDetail, $billingAddress, $product) { $current_time = date("Y-m-d H:i:s"); $query = 'INSERT INTO tbl_order (name, email, item_code, item_price, currency, address_line_1, address_line_2, country, state, city, zip, create_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'; $paramType = 'sssissssssss'; $paramValue = array( $customerDetail["name"], $customerDetail["email"], $product["itemNumber"], $product["itemPrice"], Config::CURRENCY, $billingAddress['addrLine1'], $billingAddress['addrLine2'], $billingAddress['country'], $billingAddress['state'], $billingAddress['city'], $billingAddress['zipCode'], $current_time ); $insertStatus = $this->ds->insert($query, $paramType, $paramValue); return $insertStatus; } public function updatePayment($transactionId, $paymentStatus, $orderID) { $query = "UPDATE tbl_order set txn_id = ?, payment_status = ? WHERE id = ?"; $paramType = 'ssi'; $paramValue = array( $transactionId, $paymentStatus, $orderID ); $this->ds->execute($query, $paramType, $paramValue); } } 

Model/TwoCheckoutService.php

<?php use Phppot\Config; require_once("vendor/2checkout-php-master/lib/Twocheckout.php"); class TwoCheckoutService { function verifyAuthentication() { Twocheckout::verifySSL(false); // Set API key Twocheckout::privateKey(Config::PRIVATE_KEY); // PRIVATE_KEY defined in config.php Twocheckout::sellerId(Config::SELLER_ID); // SELLER_ID defined in config.php Twocheckout::sandbox(true); } function chargeCard($orderID, $token, $currency, $customerDetail, $billingAddress, $itemPrice) { $successMessage = ""; $errorMessage = ""; try { // an array is created with customer sale parameters and passed it in auth() function of Twocheckout_Charge class for authorization. $charge = Twocheckout_Charge::auth(array( "merchantOrderId" => $orderID, "token" => $token, "currency" => $currency, "total" => $itemPrice, "billingAddr" => array( "name" => $customerDetail['name'], "addrLine1" => $billingAddress['addrLine1'], "city" => $billingAddress['city'], "state" => $billingAddress['state'], "zipCode" => $billingAddress['zipCode'], "country" => $billingAddress['country'], "email" => $customerDetail['email'] ) )); $paymentResponse = array( "message" => "", "charge" => $charge ); } catch (Twocheckout_Error $e) { $paymentResponse = array( "message" => $e->getMessage(), "charge" => "" ); } return $paymentResponse; } } 

Config.php

<?php namespace Phppot; class Config { const CURRENCY = 'USD'; const SELLER_ID = ''; const PUBLISHABLE_KEY = ''; const PRIVATE_KEY = ''; public function productDetail() { $product = array( 'WWPS235' => array( "itemName" => 'Kindle Paperwhite (10th gen) - 6" 8GB, WiFi', 'itemPrice' => '130.00' ) ); return $product; } public function monthArray() { $months = array( 'January', 'February', 'March', 'April', 'May', 'June', 'July ', 'August', 'September', 'October', 'November', 'December' ); return $months; } } 

Database script

This is a SQL with the CREATE statement of the tbl_order table. Import this script for your PHP environment to run this example.

-- -- Table structure for table `tbl_order` -- CREATE TABLE `tbl_order` ( `id` int(11) NOT NULL, `name` varchar(25) COLLATE utf8_unicode_ci NOT NULL, `email` varchar(25) COLLATE utf8_unicode_ci NOT NULL, `item_code` varchar(25) COLLATE utf8_unicode_ci NOT NULL, `item_price` float(10,2) NOT NULL, `currency` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `address_line_1` text COLLATE utf8_unicode_ci NOT NULL, `address_line_2` text COLLATE utf8_unicode_ci NOT NULL, `country` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `city` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `state` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `zip` varchar(20) COLLATE utf8_unicode_ci NOT NULL, `txn_id` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `payment_status` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `create_at` datetime NOT NULL, `edit_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); ALTER TABLE `tbl_order` ADD PRIMARY KEY (`id`); ALTER TABLE `tbl_order` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=110; 

Go Live

After testing the payment flow in the Sandbox mode, we can move to production mode.

To go live, change the mode from sandbox to production in the following line.

TCO.loadPubKey('production');

Replace all the test API keys with the live keys in the application config. Also, disable the sandbox mode by setting it as false as shown below.

Twocheckout::privateKey('production-private-key'); Twocheckout::sellerId('production-seller-id'); Twocheckout::sandbox(false); 

2Checkout Payment Form Output

The below screenshot shows the 2Checkout payment form. Most of the form fields are mandatory and so validated by JavaScript before payment.

The product name and price above the form are from the config. We can link your product database to display this information.

This screenshot shows the success response. The table shown in this screenshot has the basic details about the order placed.

Download

↑ Back to Top

Last modified on December 3rd, 2019 by Vincy.

Do you know online consumers in the US spent $517 billion last year? Which is a 15% increase from the previous year? eCommerce is an old domain, but still, it disrupts the tech world.

These days, the live shopping cart software have a remarkable business scope. They are increasing in the number day by day. There is a significant market today to sell products in an online store.

PHP is the main technology that runs the majority of the shopping cart software on the Internet. Developing a full-fledged PHP shopping cart software is not as easy as it looks.

In this article, we are going to see how to create a featured sticky shopping cart script in PHP.  In Shopify like the eCommerce platform, it gives sticky shopping cart checkout as a feature.

I used jQuery and CSS to make the shopping cart unit sticky on a fixed position on page scroll. So, it will make the cart unit be always near to access and encourage the users to checkout.

This article will guide you with simple steps to create a shopping cart in PHP. If you are planning to create a shopping cart with a modern outlook and features,  it will help you to achieve this.

What is inside?

1. Advantages of sticky shopping cart
2. PHP sticky shopping cart example overview
3. File structure
4. Landing page with product gallery and shopping cart components
5. Render product tiles in a gallery
6. Display cart items from PHP Session
7. Manage Cart actions edit, delete, empty
8. Product database table SQL script
9. PHP Shopify like sticky shopping cart example output

eCommerce software with sticky shopping cart checkout has many advantages,

• It gives an enriched modern feel to the application.
• It helps users to see the cart status at any moment.
• It encourages users to checkout and thus increasing the conversion.

This Shopify-like sticky shopping cart software in PHP uses database and PHP sessions. It manages products in the database and shopping cart items in the PHP session.

It has a product gallery, cart HTML table and supports cart actions like add, edit and delete. These action handlers use AJAX to invoke code added with server endpoints. 

The functionalities supported by this example are here with the corresponding description.

Fetching product data from the database:
A PHP MySQL code section accesses product database to read the data like product name, code, price, and preview images. If data found then this will result in an array of products.

Create product gallery with add-to-cart option:
Create a product gallery with an add-to-cart option. A PHP loop iterates product array to form the gallery view. Each product in the gallery has an add-to-cart button.

Manage the cart with PHP session:
I have used the PHP session array to store and manage the cart items.

Handling the add, edit, delete and empty cart:
This example code supports users to add a new product into the cart. A HTML table will display the cart item with edit quantity, remove an entry and more options.

File structure

The below screenshot shows the file structure of this example. 

• view/product-gallery.php – It includes a PHP loop to iterate the products to form the gallery.
• view/shopping-cart.php – It contains a HTML table to display the cart item from the PHP session.
• css/style.css – It has the styles to showcase product gallery. It helps in shopping cart software design containing appropriate cart action controls.
• cart.js – This JavaScript file contains action handlers to process requests via AJAX. It also contains code to fix the shopping cart panel in a fixed position while scrolling.
• handle-cart-ep.php – This is the PHP endpoint invoked from AJAX code to add, edit, remove cart items.
• tblproduct.sql – This SQL contains product database CREATE and INSERT statements.
• DataSource.php – This is a generic file we used for our examples requires a database.
• data – This directory is the product image source used while creating the gallery.

A landing page with product gallery and shopping cart components

This HTML code includes the PHP source to render product gallery, shopping cart software pagee. It also shows a sticky cart icon with the current cart items count.

By clicking this icon, a script will toggle the shopping cart window.

This HTML includes cart.js JavaScript file. It contains all the jQuery AJAX code needed for performing cart actions and UI update.

<?php namespace Phppot; use \Phppot\Cart; session_start(); require_once 'Model/Cart.php'; $cartModel = new Cart(); ?> <HTML> <HEAD> <TITLE>Shopify like sticky shopping cart in PHP</TITLE> <link href="./assets/css/phppot-style.css" type="text/css" rel="stylesheet" /> <script src="./vendor/jquery/jquery.min.js" type="text/javascript"></script> <script src="./vendor/jquery/jquery-ui.js"></script> </HEAD> <BODY> <?php require_once './view/product-gallery.php'; ?> <div id="floating-cart-container"> <div id="cart-icon-container"> <img id="cart-icon" src="./view/images/cart-icon.png" alt="cartimg"> <div id="count"> <?php echo $cartModel->cartSessionItemCount; ?> </div> </div> <div id="shopping-cart"> <div id="tbl-cart"> <div id="txt-heading"> <div id="cart-heading">Shopping Cart</div> <div id="close"></div> </div> <div id="cart-item"> <?php require_once './view/shopping-cart.php'; ?> </div> </div> </div> </div> <script src="./assets/js/cart.js"></script> </BODY> </HTML> 

Render product tiles in a gallery

The product gallery is a HTML container embedded with PHP-MySQL script. The PHP code prepares MySQL select statement to get the product result in an array.

By iterating this array result with a PHP foreach statement, it reads the database row data. With this product data, it forms the gallery tile on each loop iteration.

The gallery tile shows products name, price and a preview image in a card-like view. Also, it contains an add-to-cart button.

This’s button’s click event invokes AJAX to add the particular product to the cart session. Each product has a unique code which is the reference to create and manage each cart session index.

Below code shows the HTML used to show a gallery view for this shopping cart software example.

<?php require_once __DIR__ . './../Model/Product.php'; $productModel = new Product(); ?> <div id="product-grid"> <div class="txt-heading">Products</div> <?php $productResult = $productModel->getAllProduct(); if (! empty($productResult)) { foreach ($productResult as $key => $value) { ?> <div class="product-item" data-name="<?php echo $productResult[$key]["name"]; ?>" data-price="<?php echo "$" . $productResult[$key]["price"]; ?>"> <div class="product-image"> <img src="<?php echo $productResult[$key]["image"]; ?>" id="<?php echo $productResult[$key]["code"]; ?>"> </div> <div> <strong><?php echo $productResult[$key]["name"]; ?></strong> </div> <div class="product-price"><?php echo "$" . $productResult[$key]["price"]; ?></div> <input type="button" id="add_<?php echo $productResult[$key]["code"]; ?>" value="Add to cart" class="btnAddAction" onClick="cartAction('add', '<?php echo $productResult[$key]["code"]; ?>')" /> </div> <?php } } ?> </div> 

Add-to-Cart from Product Gallery

The add-to-cart button in each product tile is the trigger to add a cart item into the PHP session. When the user clicks on the ‘Add to Cart’ button, it calls the cartAction() function to execute the add action via AJAX.

In this function call, it has the product id as an argument. In PHP the code fetches the product code, price to add to the cart session.

In this example, the switch case handles cart actions. It request and process add, edit (item quantity), remove (single cart item) and empty cart.

In the “add” case I checked if the current cart item already exists in the cart session. If so, I will update its quantity, otherwise, I will push the entire item array into the session index. The product code is the session index of each cart item to keep the uniqueness. The addToCart function has the code to add the cart item into the PHP session.

case "add": $cartModel->addToCart(); break; 

Model/Product.php

<?php use \Phppot\DataSource; class Product { private $ds; function __construct() { require_once __DIR__ . './../lib/DataSource.php'; $this->ds = new DataSource(); } function getAllProduct() { $query = "SELECT * FROM tblproduct ORDER BY id ASC"; $result = $this->ds->select($query); return $result; } } 

Display shopping cart items from PHP Session

This code shows the HTML table containing the list of cart items added by the user. The cart data is dynamic from the PHP session.

In shopping-cart.php file, it iterates the $_SESSION[“cart”] array and displays the cart row. Each row has data like product title, price, quantity. It also has the option to edit the item quantity and to delete a single item from the cart.

This cart window is sticky that lets the users access the cart and see the status at any time. Also, it shows the total item price by summing up the individual cart items.

<?php namespace Phppot; use \Phppot\Cart; require_once __DIR__ . './../Model/Cart.php'; $cartModel = new Cart(); ?> <input type="hidden" id="cart-item-count" value="<?php echo $cartModel->cartSessionItemCount; ?>"> <?php if ($cartModel->cartSessionItemCount > 0) { ?> <table width="100%" id="cart-table" cellpadding="10" cellspacing="1" border="0"> <tbody> <tr> <th>Name</th> <th>Quantity</th> <th class="text-right">Price</th> <th class="text-right">Action</th> </tr> <?php $item_total = 0; $i = 1; foreach ($_SESSION["cart_item"] as $item) { ?> <tr> <td><?php echo $item["name"]; ?></td> <td><input type="number" name="quantity" class="quantity" value="<?php echo $item['quantity']; ?>" data-code='<?php echo $item["code"]; ?>' size=2 onChange="updatePrice(this)" /> <input type="hidden" class='total' name="total" value="<?php echo $item["price"]; ?>" /></td> <td align=right class="prc" id="price" <?php echo $i;?>><?php echo $item["price"]; ?></td> <?php $i++; ?> <td class="text-right"><a onClick="cartAction('remove','<?php echo $item["code"]; ?>')" class="btnRemoveAction"><img src="./view/images/icon-delete.png" alt="Remove Item" /></a></td> </tr> <?php $item_total += ($item["price"] * $item['quantity']); } ?> <tr id="tot"> <td colspan="3" align=right><strong>Total (USD): </strong> <span id="total"><?php echo $item_total;?></span></td> <td align="right"><a id="btnEmpty" onClick="cartAction('empty', '');">Empty Cart</a></td> </tr> </tbody> </table> <div id="checkout">Checkout</div> <?php } else { ?> <div id="empty-cart">Your cart is empty</div> <?php } ?> 

Handle shopping cart actions edit, remove, empty on checkout page

In each row of the showing cart tabular window, it displays editable quantity with an input box. Users can increment or decrement the cart item quantity of a particular item.

On changing the quantity, and AJAX code will send the data to get the calculated price based on the new quantity. Then, it will update the price in the row.

There is a remove option for each cart item. By clicking the remove action, an ajax call will request PHP code to perform the remove action. It will pass the product code as an argument to clear the particular cart session index.

Also, the shopping cart window has the option to empty the cart with one single click.

The below code shows the switch cases created to trigger and perform cart actions.

cart.js

function cartAction(action, product_code) { var queryString = ""; if (action != "") { switch (action) { case "add": queryString = 'action=' + action + '&code=' + product_code + '&quantity=' + $("#qty_" + product_code).val(); break; case "remove": queryString = 'action=' + action + '&code=' + product_code; break; case "empty": queryString = 'action=' + action; break; } } jQuery.ajax({ url: "ajax/handle-cart-ep.php", data: queryString, type: "POST", success: function (data) { $("#cart-item").html(data); $("#count").text($("#cart-item-count").val()); }, error: function () {} }); } function updatePrice(obj){ var quantity = $(obj).val(); var code = $(obj).data('code'); queryString = 'action=edit&code=' + code + '&quantity=' + quantity; $.ajax({ type: 'post', url: "ajax/handle-cart-ep.php", data: queryString, success: function(data) { $("#total").text(data); } }); } $(document).ready(function () { $("#cart-icon-container").click(function () { $("#shopping-cart").toggle(); }); var top = parseInt($("#shopping-cart").height())/2; $("#shopping-cart").css("margin-top", "-" + top + "px"); }); 

ajax/handle-cart-ep.php

<?php namespace Phppot; use \Phppot\Cart; require_once __DIR__ . './../Model/Cart.php'; $cartModel = new Cart(); session_start(); if (! empty($_POST["action"])) { switch ($_POST["action"]) { case "add": $cartModel->addToCart(); break; case "edit": $totalPrice = $cartModel->editCart(); print $totalPrice; exit; break; case "remove": $cartModel->removeFromCart(); break; case "empty": $cartModel->emptyCart(); break; } } require_once '../view/shopping-cart.php'; ?> 

Model/Cart.php

This is the model class used to create, edit and clear cart sessions.

<?php namespace Phppot; use \Phppot\DataSource; class Cart { private $ds; public $cartSessionItemCount = 0; function __construct() { require_once __DIR__ . './../lib/DataSource.php'; $this->ds = new DataSource(); if (! empty($_SESSION["cart_item"]) && is_array($_SESSION["cart_item"])) { $this->cartSessionItemCount = count($_SESSION["cart_item"]); } } function addToCart() { $query = "SELECT * FROM tblproduct WHERE code = ?"; $paramType = "s"; $paramArray = array($_POST["code"]); $productByCode = $this->ds->select($query, $paramType, $paramArray); $itemArray = array( $productByCode[0]["code"] => array( 'name' => $productByCode[0]["name"], 'code' => $productByCode[0]["code"], 'quantity' => '1', 'price' => $productByCode[0]["price"] ) ); if (! empty($_SESSION["cart_item"])) { if (in_array($productByCode[0]["code"], $_SESSION["cart_item"])) { foreach ($_SESSION["cart_item"] as $k => $v) { if ($productByCode[0]["code"] == $k) $_SESSION["cart_item"][$k]["quantity"] = $_POST["quantity"]; } } else { $_SESSION["cart_item"] = array_merge($_SESSION["cart_item"], $itemArray); } } else { $_SESSION["cart_item"] = $itemArray; } if (! empty($_SESSION["cart_item"]) && is_array($_SESSION["cart_item"])) { $this->cartSessionItemCount = count($_SESSION["cart_item"]); } } function editCart() { if (! empty($_SESSION["cart_item"])) { $total_price = 0; foreach ($_SESSION["cart_item"] as $k => $v) { if ($_POST["code"] == $k) { $_SESSION["cart_item"][$k]["quantity"] = $_POST["quantity"]; } $total_price = $total_price + ($_SESSION["cart_item"][$k]["quantity"] * $_SESSION["cart_item"][$k]["price"] ); } return $total_price; } if (! empty($_SESSION["cart_item"]) && is_array($_SESSION["cart_item"])) { $this->cartSessionItemCount = count($_SESSION["cart_item"]); } } function removeFromCart() { if (! empty($_SESSION["cart_item"])) { foreach ($_SESSION["cart_item"] as $k => $v) { if ($_POST["code"] == $k) unset($_SESSION["cart_item"][$k]); if (empty($_SESSION["cart_item"])) unset($_SESSION["cart_item"]); } } if (! empty($_SESSION["cart_item"]) && is_array($_SESSION["cart_item"])) { $this->cartSessionItemCount = count($_SESSION["cart_item"]); } } function emptyCart() { unset($_SESSION["cart_item"]); $this->cartSessionItemCount = 0; } } 

Product database table SQL script

The following SQL script has the CREATE and the INSERT query. It will help to put the product table in your development environment.

CREATE TABLE IF NOT EXISTS `tblproduct` ( `id` int(8) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `code` varchar(255) NOT NULL, `image` text NOT NULL, `price` double(10,2) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `product_code` (`code`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ; -- -- Dumping data for table `tblproduct` -- INSERT INTO `tblproduct` (`id`, `name`, `code`, `image`, `price`) VALUES (1, 'FinePix Pro2 3D Camera', '3DcAM01', 'product-images/camera.jpg', 1500.00), (2, 'Luxury Ultra thin Wrist Watch', 'wristWear03', 'product-images/watch.jpg', 300.00), (3, 'XP 1155 Intel Core Laptop', 'LPN45', 'product-images/laptop.jpg', 800.00), (4, 'Water Bottle', 'wristWear02', 'product-images/external-hard-drive.jpg', 600.00); 

Download

↑ Back to Top

Last modified on September 24th, 2019 by Vincy.

Do you know that the opening rate of emails by double opt-in confirmed subscribers is a staggering 40%? According to CampaignMonitor, email marketing generates $38 in ROI for every $1 spent.

Email marketing delivers the highest among any channel for marketing. Even in comparison with channels like print, TV and social media.

Email marketing is the way to go. The primary mode to build your list is using a double opt-in subscription form.

What is inside?

1. Why do we need double opt-in?
2. What is the role of secure hash?
3. Double opt-in subscription form in PHP
4. Sequence flow for double opt-in subscription
5. Double opt-in Subscription form UI
6. PHP AJAX for subscription form submission
7. URL with secure hash
8. A PHP utility class for you
9. Store subscription information to database
10. A database abstraction layer for you
11. Send confirmation email to users
12. Subscription confirmation
13. Conclusion

Use a double opt-in subscription form to signup to a newsletter, blog or a similar service. It has a two-step subscription process.

In the first step, the user will submit his name and email. Then the site will send an email to the user.

In the second step, the user will click the link in the received email. This will confirm his subscription to the site or service.

We call it the double opt-in because the users consent to the subscription twice. First by submitting the information and second by confirming to in by clicking the link in email.

Why do we need double opt-in?

It is the mechanism used to verify if the subscriber owns the input email. You need to do this verification because there is a chance for misuse by submitting emails that they do not own.

Double opt-in vs single opt-in is well debated and results arrived at. Double opt-in wins hands-on in every critical aspect.

What is the role of a secure hash?

In the confirmation email received by the user, there will be a link. This is the second and important step in the opt-in process. The link should be secure.

• It should be unique for every user and request.
• It should not be predictable.
• It should be immune to a brute-force attack.

Double opt-in subscription form in PHP

I will present you a step by step detail on how to build a double opt-in subscription form with a secure hash using PHP.

You will get a production-grade code which you can use real-time in your live website. You can use this to manage your newsletter subscription.

I am releasing this code to you under MIT license. You can use it free even in commercial projects.

Sequence flow for double opt-in subscription

1. Show a subscription form to the user.
2. On AJAX submit, insert a new record in the database.
3. Send an email to the user with a secure hash link.
4. On click, the of the link, update the subscription status.
5. On every step, there will be appropriate validations in place.

Double opt-in Subscription form UI

This is where developers get it wrong. Keep it simple and unobtrusive. For the high conversion, you must keep in minimal.

One field email is enough for the subscription. To address the user in a personal way, you need their name. That’s it. Do not ask for much information on a subscription form.

<!doctype html> <html lang="en"> <head> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no"> <meta name="author" content="Vincy"> <link rel="stylesheet" type="text/css" href="assets/css/phppot-style.css"> <title>Double Opt-In Subscription Form with Secure Hash using PHP</title> </head> <body> <div class="phppot-container"> <h1>Double Opt-in Subscription</h1> <form class="phppot-form" action="" method="POST"> <div class="phppot-row"> <div class="label"> Name </div> <input type="text" id="pp-name" name="pp-name" class="phppot-input"> </div> <div class="phppot-row"> <div class="label"> Email * <div id="email-info" class="validation-message" data-required-message="required." data-validate-message="Invalid email."></div> </div> <input type="text" id="pp-email" name="pp-email" class="required email phppot-input" onfocusout="return validateEmail();"> </div> <div class="phppot-row"> <button type="Submit" id="phppot-btn-send">Subscribe</button> <div id="phppot-loader-icon">Sending ...</div> <div id="phppot-message"></div> </div> </form> </div> <script src="vendor/jquery/jquery-3.3.1.js"></script> <script src="assets/js/subscribe.js"></script></body> </body> </html> 

If you ask for much information, it will drive your users away. The same principle applies when you build a contact form. More or less these two behave in a similar aspect. Check how to build a contact form to know more on it.

You should leave the name field can as optional and only the email field should be as required. This will encourage the user to submit the form and subscribe for the newsletter.

Needless to say, the form should be responsive. Any page or form you build should work in mobile, tablet, laptop and desktop devices. You should optimize to work on any viewport.

Google parses webpages in mobile mode for indexing in the search result. The desktop is an old story and gone are those days. You should always design for the mobile. Make it mobile-first!

PHP AJAX for subscription form submission

I have used AJAX to manage the submission. This will help the user to stay on the page after subscription. You can position this subscription form in a sidebar or the footer.

This is a classic example of where you should use the AJAX. I have seen instances where people use AJAX in inappropriate places, for the sake of using it.

Subscription AJAX endpoint

The AJAX endpoint has three major steps:

1. Verify the user input.
2. Insert a record in the database.
3. Send an email with a link for subscription.

subscribe-ep.php is the AJAX endpoint. It starts with an if condition to check if the submit is via the POST method. It is always good to program for POST instead of the GET by default.

<?php use Phppot\Subscription; use Phppot\SupportService; /** * AJAX end point for subscribe action. * 1. validate the user input * 2. store the details in database * 3. send email with link that has secure hash for opt-in confirmation */ session_start(); // to ensure the request via POST if ($_POST) { require_once __DIR__ . './../lib/SupportService.php'; $supportService = new SupportService(); // to Debug set as true $supportService->setDebug(false); // to check if its an ajax request, exit if not $supportService->validateAjaxRequest(); require_once __DIR__ . './../Model/Subscription.php'; $subscription = new Subscription(); // get user input and sanitize if (isset($_POST["pp-email"])) { $userEmail = trim($_POST["pp-email"]); $userEmail = filter_var($userEmail, FILTER_SANITIZE_EMAIL); $subscription->setEmail($userEmail); } else { // server side fallback validation to check if email is empty $output = $supportService->createJsonInstance('Email is empty!'); $supportService->endAction($output); } $memberName = ""; if (isset($_POST["pp-name"])) { $memberName = filter_var($_POST["pp-name"], FILTER_SANITIZE_STRING); } $subscription->setMemberName($memberName); // 1. get a 12 char length random string token $token = $supportService->getToken(12); // 2. make that random token to a secure hash $secureToken = $supportService->getSecureHash($token); // 3. convert that secure hash to a url string $urlSecureToken = $supportService->cleanUrl($secureToken); $subscription->setSubsriptionKey($urlSecureToken); $subscription->setSubsciptionSatus(0); $currentTime = date("Y-m-d H:i:s"); $subscription->setCreateAt($currentTime); $result = $subscription->insert(); // check if the insert is success // if success send email else send message to user $messageType = $supportService->getJsonValue($result, 'type'); if ('error' != $messageType) { $result = $subscription->sendConfirmationMessage($userEmail, $urlSecureToken); } $supportService->endAction($result); } 

I have used the SupportService class to perform common functions.

Input sanitisation is a must. When you collect information using a public website, you should be careful. You could get infected without your knowledge. There are many bots foraging around the Internet and they click on all links and buttons.

To sanitise, do not invent a new function. Use the function provided by PHP and that is safe to use.

URL with secure hash

Generate a unique url for each user subscription. Use this url to confirm the user’s subscription in the second step. Remember, that’s why we call this double opt-in.

I have used a three step process:

1. Generate a random string token.
2. Convert the token to secure hash.
3. Convert the secure hash to safe url.

I have used hexdec, bin2hex and openssl_random_pseudo_bytes to generate random bits. Which forms a random string.

Then to make the random string a secure hash, I have used the PHP’s built-in password_hash function. Never every try to do something on your own. Go with the PHP’s function and it does the job very well.

Before PHP 7, we had the option to supply a user generated salt. Now PHP 7 release has deprecated it. It is a good move because, PHP can generate a better salt than what you will generate. So stick to PHP 7 and use it without supplying your own salt.

The secure hash will contain all sort of special characters. . You can keep those special characters but need to url encode it. But I always wish to keep urls clean and the encoded chars do not look nice.

So no harm in removing them. So I cleanup those and leave only the safe characters. Then as a secondary precaution, I also encode the resultant string.

Thus after going through multi step process, we get a random, hash secure, safe, encoded URL token. Save the user submitted information in database record along with this token.

A PHP utility class for you

This is a utility class which I use in my projects. I am giving it away free for you all. It has functions that I reuse quite often and will be handy in situations. Every method has detailed comments that explain their purpose and usage method.

<?php /** * Copyright (C) 2019 Phppot * * Distributed under MIT license with an exception that, * you don’t have to include the full MIT License in your code. * In essense, you can use it on commercial software, modify and distribute free. * Though not mandatory, you are requested to attribute this URL in your code or website. */ namespace Phppot; class SupportService { /** * Short circuit type function to stop the process flow on validation failure. */ public function validateAjaxRequest() { // to check if its an ajax request, exit if not $http_request = $_SERVER['HTTP_X_REQUESTED_WITH']; if (! isset($http_request) && strtolower($http_request) != 'xmlhttprequest') { $output = $this->createJsonInstance('Not a valid AJAX request!'); $this->endAction($output); } } /** * Last point in the AJAX work flow. * Clearing tokens, handles and resource cleanup can be done here. * * @param string $output * @param boolean $clearToken */ public function endAction($output) { die($output); } public function setDebug($mode) { if ($mode == true) { ini_set('display_errors', 1); set_error_handler(function ($severity, $message, $file, $line) { if (error_reporting() & $severity) { throw new \ErrorException($message, 0, $severity, $file, $line); } }); } } /** * encodes a message string into a json object * * @param string $message * @param string $type * @return \JsonSerializable encoded json object */ public function createJsonInstance($message, $type = 'error') { $messageArray = array( 'type' => $type, 'text' => $message ); $jsonObj = json_encode($messageArray); return $jsonObj; } public function getJsonValue($json, $key) { $jsonArray = json_decode($json, true); return $jsonArray[$key]; } /** * If you are using PHP, this is the best possible secure hash * do not try to implement somthing on your own * * @param string $text * @return string */ public function getSecureHash($text) { $hashedText = password_hash($text, PASSWORD_DEFAULT); return $hashedText; } /** * generates a random token of the length passed * * @param int $length * @return string */ public function getToken($length) { $token = ""; $codeAlphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; $codeAlphabet .= "abcdefghijklmnopqrstuvwxyz"; $codeAlphabet .= "0123456789"; $max = strlen($codeAlphabet) - 1; for ($i = 0; $i < $length; $i ++) { $token .= $codeAlphabet[$this->cryptoRandSecure(0, $max)]; } return $token; } public function cryptoRandSecure($min, $max) { $range = $max - $min; if ($range < 1) { return $min; // not so random... } $log = ceil(log($range, 2)); $bytes = (int) ($log / 8) + 1; // length in bytes $bits = (int) $log + 1; // length in bits $filter = (int) (1 << $bits) - 1; // set all lower bits to 1 do { $rnd = hexdec(bin2hex(openssl_random_pseudo_bytes($bytes))); $rnd = $rnd & $filter; // discard irrelevant bits } while ($rnd >= $range); return $min + $rnd; } /** * makes the passed string url safe and return encoded url * * @param string $str * @return string */ public function cleanUrl($str, $isEncode = 'true') { $delimiter = "-"; $str = str_replace(' ', $delimiter, $str); // Replaces all spaces with hyphens. $str = preg_replace('/[^A-Za-z0-9\-]/', '', $str); // allows only alphanumeric and - $str = trim($str, $delimiter); // remove delimiter from both ends $regexConseqChars = '/' . $delimiter . $delimiter . '+/'; $str = preg_replace($regexConseqChars, $delimiter, $str); // remove consequtive delimiter $str = mb_strtolower($str, 'UTF-8'); // convert to all lower if ($isEncode) { $str = urldecode($str); // encode to url } return $str; } /** * to mitigate XSS attack */ public function xssafe($data, $encoding = 'UTF-8') { return htmlspecialchars($data, ENT_QUOTES | ENT_HTML401, $encoding); } /** * convenient method to print XSS mitigated text * * @param string $data */ public function xecho($data) { echo $this->xssafe($data); } } 

Store subscription information to the database

Insert a record to the database on submission of the subscription form. We get the user’s name, email, generate a secure hash token, current time, subscription status.

<?php /** * Copyright (C) 2019 Phppot * * Distributed under MIT license with an exception that, * you don’t have to include the full MIT License in your code. * In essense, you can use it on commercial software, modify and distribute free. * Though not mandatory, you are requested to attribute this URL in your code or website. */ namespace Phppot; use Phppot\DataSource; class Subscription { private $ds; private $memberName; private $email; private $subsriptionKey; private $subsciptionSatus; private $createAt; private $supportService; function __construct() { require_once __DIR__ . './../lib/DataSource.php'; $this->ds = new DataSource(); require_once __DIR__ . './../lib/SupportService.php'; $this->supportService = new SupportService(); } public function getMemberName() { return $this->memberName; } public function getEmail() { return $this->email; } public function getSubsriptionKey() { return $this->subsriptionKey; } public function getSubsciptionSatus() { return $this->subsciptionSatus; } public function getCreateAt() { return $this->createAt; } public function setMemberName($memberName) { $this->memberName = $memberName; } public function setEmail($email) { $this->email = $email; } public function setSubsriptionKey($subsriptionKey) { $this->subsriptionKey = $subsriptionKey; } public function setSubsciptionSatus($subsciptionSatus) { $this->subsciptionSatus = $subsciptionSatus; } public function setCreateAt($createAt) { $this->createAt = $createAt; } /** * to get the member record based on the subscription_key * * @param string $subscriptionKey * @return array result record */ public function getMember($subscriptionKey, $subscriptionStatus) { $query = 'SELECT * FROM tbl_subscription where subscription_key = ? and subscription_status = ?'; $paramType = 'si'; $paramValue = array( $subscriptionKey, $subscriptionStatus ); $result = $this->ds->select($query, $paramType, $paramValue); return $result; } public function insert() { $query = 'INSERT INTO tbl_subscription (member_name, email, subscription_key, subscription_status, create_at) VALUES (?, ?, ?, ?, ?)'; $paramType = 'sssis'; $paramValue = array( $this->memberName, $this->email, $this->subsriptionKey, $this->subsciptionSatus, $this->createAt ); $insertStatus = $this->ds->insert($query, $paramType, $paramValue); return $insertStatus; } public function updateStatus($subscriptionKey, $subscriptionStatus) { $query = 'UPDATE tbl_subscription SET subscription_status = ? WHERE subscription_key = ?'; $paramType = 'is'; $paramValue = array( $subscriptionStatus, $subscriptionKey ); $this->ds->execute($query, $paramType, $paramValue); } /** * sends confirmation email, to keep it simple, I am just using the PHP's mail * I reccommend serious users to change it to PHPMailer and set * appropriate headers */ public function sendConfirmationMessage($mailTo, $urlSecureToken) { // following is the opt-in url that will be sent in email to // the subscriber. Replace example.com with your server $confirmOptInUrl = 'http://example.com/confirm.php?q=' . $urlSecureToken; $message = '<p>Howdy!</p> <p>This is an automated message sent for subscription service. You must confirm your request to subscribe to example.com site.</p> <p>Website Name: example</p> <p>Website URL: http://example.com</p> <p>Click the following link to confirm: ' . $confirmOptInUrl . '</p>'; $isSent = mail($mailTo, 'Confirm your subscription', $message); if ($isSent) { $message = "An email is sent to you. You should confirm the subscription by clicking the link in the email."; $result = $this->supportService->createJsonInstance($message, 'message'); } else { $result = $this->supportService->createJsonInstance('Error in sending confirmation email.', 'error'); } return $result; } } 

The reason for storing the current time is to have an expiry for every link. We can set a predefined expiry for the double opt-in process.

For example, you can set one week as expiry for a link from the moment you generate it. The user has to click and confirm before that expiry period.

Subscription status is by default stored as ‘0’ and on confirmation changed to ‘1’.

A database abstraction layer for you

It is my PHP abstraction for minor projects. This works as a layer between controller, business logic and the database. It has generic methods using which we can to the CRUD operations. I have bundled it with the free project download that is available at the end of this tutorial.

Send confirmation email to users

After you insert the record, send an email will to the user to perform the double opt-in confirmation. The user will have a link in the email which he has to click to confirm.

Keep the email simple. It is okay to have text instead of fancy HTML emails. PHP is capable of generating any email and you can code complex email templates. But the spam engines may not like it.

If you wish to go with HTML emails, then keep the HTML code ratio to as least as possible. As this is also one factor using which the spam engines flag the emails.

Then remember not to use the spam stop words. There are words like “free”, “win”, “cash”, “promo” and “income”. There is a long list and you can get it on the Internet by searching for “email spam filter word list”.

I have used PHP’s mail() function to send the email. I recommend you to change it to PHPMailer to send SMTP based email if you plan to use this code in production.

Subscription confirmation

Create a public landing page and you may use .htaccess for a neat URL mapping. This URL should map with the URL sent to the user and the PHP file that is going to process the request.

As a first step, GET the token and to verify the user against the database. Check,

1. if such a token exists,
2. it is not expired,
3. the user is not already subscribed
4. add more validation as you deem fit.

<?php use Phppot\Subscription; use Phppot\SupportService; /** * For confirmation action. * 1. Get the secure has from url * 2. validate it against url * 3. update the subscription status in database accordingly. */ session_start(); // to ensure the request via POST require_once __DIR__ . '/lib/SupportService.php'; $supportService = new SupportService(); // to Debug set as true $supportService->setDebug(true); $subscriptionKey = $_GET['q']; require_once __DIR__ . '/Model/Subscription.php'; $subscription = new Subscription(); $result = $subscription->getMember($subscriptionKey, 0); if (count($result) > 0) { // member found, go ahead and update status $subscription->updateStatus($subscriptionKey, 1); $message = $result[0]['member_name'] . ', your subscription is confirmed.'; $messageType = 'success'; } else { // securiy precaution: do not reveal any information here // play subtle with the reported message $message = 'Invalid URL!'; $messageType = 'error'; } ?> <!doctype html> <html lang="en"> <head> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no"> <meta name="author" content="Vincy"> <link rel="stylesheet" type="text/css" href="assets/css/phppot-style.css"> <title>Double Opt-In Subscription Confirmation</title> </head> <body> <div class="phppot-container"> <h1>Double Opt-in Subscription Confirmation</h1> <div class="phppot-row"> <div id="phppot-message" class="<?php echo $messageType; ?>"><?php echo $message;?></div> </div> </div> </body> </body> </html> 

If validation fails, do not reveal any information to the user. You should only say that it has failed.

More important do not say, not such email found. This will allow finding who has subscribed to your service. Whenever a validation fails, the displayed message should not reveal internal information.

On validation success, update the subscription status. Then show a happy success message to the user.

Conclusion

I have presented you with a production-grade double opt-in subscription form. I have followed a most secure hash generation method for confirmation URL email. I present it to you under the MIT license. The intention is to be the most permissible. You can download it free and change the code. You can even use it in your commercial projects. I have used the most secure code as possible. You can use this in your live site to manage newsletter subscription. In the coming part, I will include unsubscribe and enhance it further. Leave your comments below with what sort of enhancements you are looking for.

Download

↑ Back to Top

Last modified on September 5th, 2019 by Vincy.

Contact form is an important element in your website. It encourages communication and acts as a bridge between you and your users. It allows users to post their feedback, comments, questions and more.

It helps you to get ideas and opinions that lead your business to growth. It is one among the important aspects that will decide your success.

There are many popup contact form plugins available online. These plugins help to add a contact form in your application with a modern outlook. You should choose them with care.

I have developed a light-weight contact form component named Iris. It is an interactive, integrative component. You can plug-in this component with your application without putting much effort. If you are searching for a secured simple contact form component, then Iris is the one you are looking for.

You should also read through the simple secure spam-free contact form. It details on the critical elements of a contact form and how they should be designed. It is a highly recommended read from me.

View Demo

Developing a PHP contact form is a simple job. We have seen so many examples for creating a contact form in PHP. The simple contact form example has the basic code skeleton. It has minimal fields and a simple mail sending script.

Generally, the contact form has the name, email, message and more fields. The fields may vary based on the application’s nature or its purpose. 

In some application there may be a lengthy contact form with elaborate list of fields phone, fax and more. But, sleek forms with minimal inputs encourage users to interact.

If you want to get more information, then make it via optional custom fields. Contact form with custom fields will help users who have no objection to give extra data.

In this article, we are going how to code for showing a PHP contact form as a popup dialog. I used the jQuery core to display a popup dialog with the contact form HTML.

What is inside?

1. Purpose of the contact form on a web application
2. Things to remember while creating a contact form
3. About this example
4. File structure
5. Create HTML interface for the contact form
6. PHP script to send contact email
7. PHP contact form popup with jQuery Output
8. Conclusion

There are various ways to allow users to contact you via your application interface. Contact form is one of a popular component of an application. It lets users contact the website owner or admin.

Generally, contact forms help the users to send their thoughts, doubts. Some of the main purposes of this contact form interface are here as listed below.

• To collect feedback, comments from the users or customers.
• For getting the advantages of user interaction with your application.
• To receive user’s support request.
• To allow users to send inquiries for paid services or customization.
• To get biodata, proof and more references as attachments.

While creating a contact form in your application, you have to remember the following. These points will help to create a safe contact for an interface for your application.

IMPORTANT – Read this!

• Secure your platform from the bots. Yes! Internet is full of automated bots hungry for spreading spam.
• Prevent abnormal frequent hits which may stress your server.
• Ensure data sanitization before processing user data
• Check request origin to prevent automated software to post the form data.
• Validate on both client and server side.
• No design is design. Do not thrust the UI upon the users, let its focus be on good communication and ease of use.

As the internet is an open world, it allows anonymous users. So, there is the possibility of malicious access. So, this kind of safety measures will reduce the risk.

It is not only applicable for the contact form but also for all the interfaces that get data from the end-user.

The contact form pops up to collect name, email, subject and message from the users. While running this example, the landing page will not show the popup on page load. Rather, it shows a clickable contact icon.

By clicking this icon, a contact form will popup with the name, email and more fields. For displaying the popup interface, I used jQuery library functions. It will add an overlay on top of the webpage while showing the popup.

In this example, all the contact details are mandatory. I have added a minimal validation script in JavaScript to validate the form data. This script is to check the availability of the contact data before posting it to the server-side.

When the user submits the form, the PHP code will receive the posted form data. Then, it processes the mail function with this contact information.

In this example, I have used the PHP mail function to send the contact email. If you want to send email using SMPT, the linked article has the code for implementing it.

Merits and demerits of a popup contact form dialog

There are both advantages and disadvantages of showing a popup contact form dialog.

The advantage is to let the user stay on his current page with any navigation or page refresh. The popup dialog interface will give a modern outlook for your application

Some web users hate popups. This is the main disadvantages. Also, it is a little bit of effort taking work to make a popup interface mobile friendly.

File Structure

Below screenshot shows the file structure of this PHP contact form example. It shows the custom files and libraries used in this code.

It has a very minimal amount of dynamic code that is for sending the contact email. Other than that, it has more of the HTML, CSS, JavaScript code.

The index.php is the landing page which contains HTML to display clickable contact icon. It also has a hidden contact form container and a jQuery script to popup the form.

The vendor directory contains the jQuery library source.

This section is to learn how to create HTML to display the contact form interface in a jQuery popup.

It has the code to show the contact icon which popups contact form on its click event. The click event on the outside of the contact form layout will close the popup dialog.

I have added CSS and jQuery script to reflect the appropriate UI changes based on the user’s click action event. It helps to toggle the contact form popup dialog and acknowledge the user accordingly.

<!DOCTYPE html> <html> <head> <title>How to display PHP contact form popup using jQuery</title> <script src="./vendor/jquery/jquery-3.2.1.min.js"></script> <link rel="stylesheet" href="./css/style.css" /> </head> <body> <div id="contact-icon"> <img src="./icon/icon-contact.png" alt="contact" height="50" width="50"> </div> <!--Contact Form--> <div id="contact-popup"> <form class="contact-form" action="" id="contact-form" method="post" enctype="multipart/form-data"> <h1>Contact Us</h1> <div> <div> <label>Name: </label><span id="userName-info" class="info"></span> </div> <div> <input type="text" id="userName" name="userName" class="inputBox" /> </div> </div> <div> <div> <label>Email: </label><span id="userEmail-info" class="info"></span> </div> <div> <input type="text" id="userEmail" name="userEmail" class="inputBox" /> </div> </div> <div> <div> <label>Subject: </label><span id="subject-info" class="info"></span> </div> <div> <input type="text" id="subject" name="subject" class="inputBox" /> </div> </div> <div> <div> <label>Message: </label><span id="userMessage-info" class="info"></span> </div> <div> <textarea id="message" name="message" class="inputBox"></textarea> </div> </div> <div> <input type="submit" id="send" name="send" value="Send" /> </div> </form> </div> </body> </html> 

Below code shows the styles created for this PHP contact form UI. I have used very less CSS for this example to make it generic for any theme. You can override the below style to customize the form design for your website theme.

body { color: #232323; font-size: 0.95em; font-family: arial; } div#success { text-align: center; box-shadow: 1px 1px 5px #455644; background: #bae8ba; padding: 10px; border-radius: 3px; margin: 0 auto; width: 350px; } .inputBox { width: 100%; margin: 5px 0px 15px 0px; border: #dedede 1px solid; box-sizing: border-box; padding: 15px; } #contact-popup { position: absolute; top: 0px; left: 0px; height: 100%; width: 100%; background: rgba(0, 0, 0, 0.5); display: none; color: #676767; } .contact-form { width: 350px; margin: 0px; background-color: white; font-family: Arial; position: relative; left: 50%; top: 50%; margin-left: -210px; margin-top: -255px; box-shadow: 1px 1px 5px #444444; padding: 20px 40px 40px 40px; } #contact-icon { padding: 10px 5px 5px 12px; width: 58px; color: white; box-shadow: 1px 1px 5px grey; border-radius: 3px; cursor: pointer; margin: 60px auto; } .info { color: #d30a0a; letter-spacing: 2px; padding-left: 5px; } #send { background-color: #09F; border: 1px solid #1398f1; font-family: Arial; color: white; width: 100%; padding: 10px; cursor: pointer; } #contact-popup h1 { font-weight: normal; text-align: center; margin: 10px 0px 20px 0px; } .input-error { border: #e66262 1px solid; } 

jQuery Script to show Contact form popup and validate form fields

Below script shows the jQuery callback function added for the document ready event.

It has two event handling functions. One is to show contact form popup dialog on the click event of the contact icon.

The other is to handle the form submit to validate contact data entered by the user.

The validation script focuses on minimalistic filter appliances. It helps to prevent users from sending the form with an empty data or with invalid data (email) format.

<script> $(document).ready(function () { $("#contact-icon").click(function () { $("#contact-popup").show(); }); //Contact Form validation on click event $("#contact-form").on("submit", function () { var valid = true; $(".info").html(""); $("inputBox").removeClass("input-error"); var userName = $("#userName").val(); var userEmail = $("#userEmail").val(); var subject = $("#subject").val(); var message = $("#message").val(); if (userName == "") { $("#userName-info").html("required."); $("#userName").addClass("input-error"); } if (userEmail == "") { $("#userEmail-info").html("required."); $("#userEmail").addClass("input-error"); valid = false; } if (!userEmail.match(/^([\w-\.]+@([\w-]+\.)+[\w-]{2,4})?$/)) { $("#userEmail-info").html("invalid."); $("#userEmail").addClass("input-error"); valid = false; } if (subject == "") { $("#subject-info").html("required."); $("#subject").addClass("input-error"); valid = false; } if (message == "") { $("#userMessage-info").html("required."); $("#message").addClass("input-error"); valid = false; } return valid; }); }); </script> 

There are many ways of sending email in PHP. In this example, I used the in-built mail function to send the contact email.

Before sending the mail, we have to set the header, recipient, and the other parameters.

Below PHP script gets the posted contact form data using $_POST request array. In PHP, it sets the From data with mail header using the name, email posted via the form.

In this code, we can see the PHP filter_var() applied to sanitize the form data before processing.

Once the email sent, the PHP mail() function will return boolean true. If so, it shows a success message to the user.

<?php if (! empty($_POST["send"])) { $name = filter_var($_POST["userName"], FILTER_SANITIZE_STRING); $email = filter_var($_POST["userEmail"], FILTER_SANITIZE_EMAIL); $subject = filter_var($_POST["subject"], FILTER_SANITIZE_STRING); $message = filter_var($_POST["message"], FILTER_SANITIZE_STRING); $toEmail = "to_email@gmail.com"; $mailHeaders = "From: " . $name . "<" . $email . ">\r\n"; if (mail($toEmail, $subject, $message, $mailHeaders)) { ?> <div id="success">Your contact information is received successfully!</div> <?php } } ?> 

In a previous article, we have seen an example to send an email with Gmail SMTP using PHPMailer library.

PHP contact form popup with jQuery Output

The figure shows the screenshot of the contact form popup dialog. You can see the clickable icon that is behind the overlay.

I have taken this screenshot by sending the form with an empty message and invalid email format. In the following screenshot, you can see the corresponding error message in the popup dialog. This is how this form alerts users about the improper data entered by them.

After sending the contact email, this message helps to acknowledge the user. This acknowledgment will toggle off the contact form popup.

Conclusion

Contact form in your application will help to gather information from the user. It will give you valuable feedback, ideas from the end-user to grow your business.

We have seen the advantages and disadvantages of having a popup contact form interface in an application. Also, we have seen lot of information about the purposes, basic need to integrate a contact form in an application.

The example code we have created for this article will reduce your effort to create a contact form for your application. It is a basic solution for the one who wants to deploy a medium to interact with the site users.

View Demo Download

↑ Back to Top

Last modified on August 7th, 2019 by Vincy.

There are various ways to extract images from a given URL. PHP contains built-in functions for extracting data including the images with a URL.

This article is for PHP code to extract images from URLs existing in an excel file.

I have used PhpSpreadsheet to read the URLs from an Excel file. Then, I created cURL script to extract images from the URL.

PhpSpreadsheet library supports Excel read-write operations. It provides enormous features like formatting content, manipulating data and more. It has a rich set of built-in classes and thereby makes the development process easy.

Working with spreadsheets is a common need while handling excel data via programming. PhpSpreadsheet library reduces the developer’s effort on building applications with excel data handing.

We have already seen several examples of URL extract using PHP. Also, we have created code for getting video thumbnail from Youtube URL.

What is inside?

1. Uses of extracting images from URL from Excel
2. Advantages of PhpSpreadsheet Library
3. Existing PHP libraries used to import-export
4. File Structure
5. About this example
6. PHP code to load and extract image data
7. Render extracted images in a gallery
8. Database script
9. Extract images from URL in excel using PhpSpreadsheet Output

Extracting of images from URL from an excel file will be helpful in many scenarios. Below list shows some scenarios.

1. To import a large volume of images into your application’s media library.
2. To migrate media files from one domain to another.
3. To restore the Excel backup images into a database.
4. To create a dynamic photo gallery without a database.

Advantages of PhpSpreadsheet Library

PhpSpreadsheet has many features and thereby has more advantages of using it.

• It provides methods to prepare reports, charts, plans and more.
• It has an option the read, write from a specified row, column and sheet of a spreadsheet document.
• It is suitable for handling a large amount of data.
• It helps to manage checklists, calendars, timesheets, schedules, proposal plans.
• It provides security to protect spreadsheet data from editing.
• It supports encryption to prevent the spreadsheet data from viewing. 

Existing PHP libraries used to import-export

There are many PHP libraries available in the market support spreadsheet data handling.

• PortPHP supports import-export data between Excel, CSV and database storages. It has readers, writers and converters to process data exchange and manipulation.
• The Spout is a PHP library used to read write spreadsheets in an efficient way. It supports three types of spreadsheets XLS, CSV, ODS.

File structure

Below screenshot shows the file structure of this example. The ExcelImportService class file is an integral part of this example. It loads PhpSpreadsheet library and covers all the operations related to the excel image extract.

The excel_template folder contains an input Excel file with image URLs. This example code loads this file to extract images from the URL.

Instead of using this fixed excel template, you can also allow users to choose an excel file. By adding a HTML form with a file input option user can choose their excel to explore extract.

About this example

This example loads an input Excel file in an Import service class. This sample excel file will contain image URLs.

In this example, I have used PhpSpreadsheet library to read the excel data. This library method helps to get the URLs and store into an array.

Then I iterate this URL array in a loop to extract the image data. I used PHP cURL script to extract images. In a previous tutorial, we have seen how to run PHP cURL script to extract content from a remote URL.

Finally, this code will store the extracted images into a directory and save the path to the database. In a previous article, we import excel data into a database without images. Also, we have seen examples to import data from CSV to a database.

PHP code to load and extract image data

This PHP code loads the ExcelImportService class to load and import image data from an excel.

This is the main PHP class created for this example. It handles all operations during the excel image extract.

<?php use \Phppot\ExcelImportService; require_once 'Class/ExcelImportService.php'; $excelImportService = new ExcelImportService(); $excelDataArray = $excelImportService->loadExcel(); if (! empty($excelDataArray)) { $isNewData = $excelImportService->importImages($excelDataArray); if ($isNewData) { $message = "Images extracted from excel successfully!"; } else { $message = "No new images found during the excel extract!"; } } $imageResult = $excelImportService->getAllImages(); ?> 

ExcelImportService.php

This class loads the PhpSpreadsheet library. It also has the DataSource instance in the class level.

The database access request from this class uses this instance. It is for saving the extracted image path to the database.

Note: Download PhpSpreadsheet library from Github without dependencies. Then run the get the dependencies via composer by using the following command.

composer require phpoffice/phpspreadsheet 

In this class, the loadExcel() function loads the input excel to read the URLs as an array. It returns this array to extract image blob via cURL request. 

The extractImage() function executes the cURL script. It gets the image resource data from the remote URL read from Excel. Then it writes the file into a target as specified in this example.

After putting the extracted images into a folder, then the code saves the to the image database table. The saveImagePath() method contains the insert query and parameters to invoke DataSource insert.

<?php namespace Phppot; use \Phppot\DataSource; require 'Vendor/PhpSpreadsheet/autoload.php'; class ExcelImportService { private $ds; function __construct() { require_once __DIR__ . './DataSource.php'; $this->ds = new DataSource(); } private function isUrlExist($url) { $query = 'SELECT * FROM tbl_images where remote_url = ?'; $paramType = 's'; $paramValue = array($url); $count = $this->ds->numRows($query, $paramType, $paramValue); return $count; } private function extractImage($url) { $path = pathinfo($url); $imageTargetPath = 'uploads/' . time() . $path['basename']; $ch = curl_init(); curl_setopt($ch, CURLOPT_URL, $url); curl_setopt($ch, CURLOPT_VERBOSE, 1); curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1); curl_setopt($ch, CURLOPT_AUTOREFERER, false); curl_setopt($ch, CURLOPT_HTTP_VERSION, CURL_HTTP_VERSION_1_1); curl_setopt($ch, CURLOPT_HEADER, 0); curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0); // <-- important to specify curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 0); // <-- important to specify $resultImage = curl_exec($ch); curl_close($ch); $fp = fopen($imageTargetPath, 'wb'); fwrite($fp, $resultImage); fclose($fp); $imageInfo["image_name"] = $path['basename']; $imageInfo["image_path"] = $imageTargetPath; return $imageInfo; } private function saveImagePath($imageInfo, $remoteUrl) { $query = "INSERT INTO tbl_images (image_name,image_path, remote_url) VALUES (?, ?, ?)"; $paramType = 'sss'; $paramValue = array($imageInfo["image_name"], $imageInfo["image_path"], $remoteUrl); $this->ds->insert($query, $paramType, $paramValue); } public function loadExcel() { //create directly an object instance of the IOFactory class, and load the xlsx file $xlsFile ='Excel_Template/imageURLs.xlsx'; $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($xlsFile); //read excel data and store it into an array $excelData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true); $rowCount = count($excelData); $urlArray = array(); for($i=2;$i<$rowCount;$i++) { $url = $excelData[$i]['A']; if(!empty($url)) { $urlArray[] = $url; } } return $urlArray; } public function importImages($excelDataArray) { $isNewData = false; foreach($excelDataArray as $url) { $isUrlExist = $this->isUrlExist($url); if (empty($isUrlExist)) { $imageInfo = $this->extractImage($url); if(!empty($imageInfo)) { $this->saveImagePath($imageInfo, $url); } $isNewData = true; } } return $isNewData; } public function getAllImages() { $query = 'SELECT * FROM tbl_images'; $result = $this->ds->select($query); return $result; } }

DataSource.php

This is a common PHP class that we have used in many examples. It contains functions to execute the database operations planned for the example code. It establishes the database connection at its constructor.

Model classes used in our PHP examples load this class and instantiate it to access the database.

<?php namespace Phppot; /** * Generic datasource class for handling DB operations. * Uses MySqli and PreparedStatements. * * @version 2.3 */ class DataSource { // PHP 7.1.0 visibility modifiers are allowed for class constants. // when using above 7.1.0, declare the below constants as private const HOST = 'localhost'; const USERNAME = 'root'; const PASSWORD = ''; const DATABASENAME = 'phpsamples'; private $conn; /** * PHP implicitly takes care of cleanup for default connection types. * So no need to worry about closing the connection. * * Singletons not required in PHP as there is no * concept of shared memory. * Every object lives only for a request. * * Keeping things simple and that works! */ function __construct() { $this->conn = $this->getConnection(); } /** * If connection object is needed use this method and get access to it. * Otherwise, use the below methods for insert / update / etc. * * @return \mysqli */ public function getConnection() { $conn = new \mysqli(self::HOST, self::USERNAME, self::PASSWORD, self::DATABASENAME); if (mysqli_connect_errno()) { trigger_error("Problem with connecting to database."); } $conn->set_charset("utf8"); return $conn; } /** * To get database results * @param string $query * @param string $paramType * @param array $paramArray * @return array */ public function select($query, $paramType="", $paramArray=array()) { $stmt = $this->conn->prepare($query); if(!empty($paramType) && !empty($paramArray)) { $this->bindQueryParams($sql, $paramType, $paramArray); } $stmt->execute(); $result = $stmt->get_result(); if ($result->num_rows > 0) { while ($row = $result->fetch_assoc()) { $resultset[] = $row; } } if (! empty($resultset)) { return $resultset; } } /** * To insert * @param string $query * @param string $paramType * @param array $paramArray * @return int */ public function insert($query, $paramType, $paramArray) { $stmt = $this->conn->prepare($query); $this->bindQueryParams($stmt, $paramType, $paramArray); $stmt->execute(); $insertId = $stmt->insert_id; return $insertId; } /** * To execute query * @param string $query * @param string $paramType * @param array $paramArray */ public function execute($query, $paramType="", $paramArray=array()) { $stmt = $this->conn->prepare($query); if(!empty($paramType) && !empty($paramArray)) { $this->bindQueryParams($stmt, $paramType="", $paramArray=array()); } $stmt->execute(); } /** * 1. Prepares parameter binding * 2. Bind prameters to the sql statement * @param string $stmt * @param string $paramType * @param array $paramArray */ public function bindQueryParams($stmt, $paramType, $paramArray=array()) { $paramValueReference[] = & $paramType; for ($i = 0; $i < count($paramArray); $i ++) { $paramValueReference[] = & $paramArray[$i]; } call_user_func_array(array( $stmt, 'bind_param' ), $paramValueReference); } /** * To get database results * @param string $query * @param string $paramType * @param array $paramArray * @return array */ public function numRows($query, $paramType="", $paramArray=array()) { $stmt = $this->conn->prepare($query); if(!empty($paramType) && !empty($paramArray)) { $this->bindQueryParams($stmt, $paramType, $paramArray); } $stmt->execute(); $stmt->store_result(); $recordCount = $stmt->num_rows; return $recordCount; } } 

Render extracted images in a gallery

This is the HTML code to display the extracted images in the UI. I embed PHP code with this HTML to display the image path from the database dynamically.

The getAllImages() method fetches image results from the database. It returns an array of images extracted from the Excel. This array data iteration helps to render images in a gallery view.

<!doctype html> <html> <head> <link rel="stylesheet" type="text/css" href="CSS/style.css"> <title>Extract Images from URL in Excel using PHPSpreadSheet with PHP</title> </head> <body> <div id="gallery"> <div id="image-container"> <h2>Extract Images from URL in Excel using PHPSpreadSheet with PHP</h2> <?php if (! empty($message)) { ?> <div id="txtresponse"><?php echo $message; ?></div> <?php } ?> <ul id="image-list"> <?php if (! empty($imageResult)) { foreach ($imageResult as $k => $v) { ?> <li><img src="<?php echo $imageResult[$k]['image_path']; ?>" class="image-thumb" alt="<?php echo $imageResult[$k]['image_name'];?>"></li> <?php } } ?> </ul> </div> </div> </body> </html> 

After a successful image extract, this UI will acknowledge the user. It shows an appropriate message based on the image extract result.

If you extract an older excel that was already done, then the notification will say “No new images found”.

The following styles are used to present the extracted images in a gallery.

body { font-family: Arial; color: #212121; text-align: center; } #gallery { width: 1057px; margin: 0 auto; } #image-list { list-style-type: none; margin: 0; padding: 0; } #image-list li { margin: 10px 20px 10px 0px; display: inline-block; } #image-list li img { width: 250px; height: 155px; } #image-container { margin-bottom: 14px; } #txtresponse { padding: 10px 40px; border-radius: 3px; margin: 10px 0px 30px 0px; border: #ecdeaa 1px solid; color: #848483; background: #ffefb6; display: inline-block; } .btn-submit { padding: 10px 30px; background: #333; border: #E0E0E0 1px solid; color: #FFF; font-size: 0.9em; width: 100px; border-radius: 0px; cursor: pointer; position: absolute; } .image-thumb { background-color: grey; padding: 10px; } 

Database script

This SQL script is for creating the required database table in your environment. It has the create a statement of the tbl_images database table. This table is the storage the point to store the image local path.

Run this script before executing this example. You can also get the SQL script from the downloadable source code added with this article.

CREATE TABLE IF NOT EXISTS `tbl_images` ( `id` int(11) NOT NULL AUTO_INCREMENT, `image_name` varchar(50) NOT NULL, `image_path` varchar(50) NOT NULL, `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=263 ; 

The screenshot below shows the image gallery output. These images are from the uploads folder of this example. This is the local location to store the extracted images from the database.

This screen shows the user acknowledgment message above the gallery view. This acknowledgment varies based on the input excel file data.

If the input excel is too older and extracted already, then the below message will notify the user.

Hope this article helps you to image extract from URLs present in excel. The example presented is the simplest way of demonstrating image extract from Excel.

Download

↑ Back to Top