Simple transaction example using PHP and mysql_query

<?php
// A simple transaction example using PHP and mysql_query

//——————————————–
@mysql_connect(“localhost”,”myusername”, “mypassword”) or die(mysql_error());
@mysql_select_db(“mydatabase”) or die(mysql_error());
$query = “INSERT INTO mytable (firstname, lastname) values (‘Fred’,’Bloggs’)”;

begin(); // begin transaction
$result = @mysql_query($query);

if(!$result) {
rollback(); // failed so roll back transaction
echo “Rolled back”;
exit;
}
else
{
commit(); // Committ transaction
echo “Transaction completed”;
}
//——————————————–

//——————————————–
function begin() {
@mysql_query(“BEGIN”);
}

//——————————————–
function commit() {
@mysql_query(“COMMIT”);
}

//——————————————–
function rollback() {
@mysql_query(“ROLLBACK”);
}

?>

The above code illustrates the use of transactions with mysql_query code.

SQL Transactions in phpMyAdmin

A transaction is a collection of SQL statements.  So a task may, for example, consist of a number of update statements.  If one of these should fail you may end up with some tables correctly updated but others not correctly updated particularly if one update has data from a previous update.

To overcome this you define the SQL statements as a transaction. If one SQL statement should fail, you roll back the data to its initial state.  Only when all the statements are successful will you commit the transaction.

When you are entering SQL statements into mySQL you may use the begin / commit  / rollback transaction commands.

So, say we are working with a person table and do an INSERT statement, we can either commit or rollback the transaction.

The problem with phpMyAdmin is that all the lines have to be in one command so it is not easy to demonstrate.

However, we can demonstrate it using the following SQL statements and check the results for each.

Copy the following lines in to the SQL editor in phpMyAdmin:

begin;
INSERT INTO person (firstname, lastname) VALUES (“fred”, “blogs”);
commit;

Examine the person table.  We should see that a new record has been created.

Now copy the following lines into the SQL editor in phpMyAdmin:

begin;
INSERT INTO person (firstname, lastname) VALUES (“fred”, “blogs”);
rollback;

In this case the transaction has been rolled back and the record has not been created.

Setting up a digtial download store

We have already written a short pdf document at http://www.withinweb.com/global/hints_tips_and_tricks/digital_files.pdf which provides some hints, tips and tricks for selling digital downloads.

Here are a few other issues that you may want to consider when setting up a digital online shopping system

Make sure your system is secure

It probably goes without saying that the shopping system has to be secure but in addition it needs to have a good method to protect the files you are going to download.

Select a good payment system

You should choose a payment system that has the facilies to feedback the results back you your web site.  Most payments systems will have some kind of notification method and some are more reliable than others.  There will be quite a bit of integration needed between your shopping system and the payment system which may restrict your choice.

Set up your own system on your web site or use a 3rd party web site

There are now a number of 3rd party web sites that you can subscribe to and they will handle all the selling of your digital product,  Many of these charge a fee which may be monthly or based on file size or some other factor.  You will need to judge whether your sells will be sufficient to cover the costs of the fees.

Select a good web hosting company

You need a web server that has all the facilites that you need, for example mySQL database, PHP support.  It also has to be reliable so that you know your site is running every day of the year.  It also helps if the web hosting company has good technical support.

Google introduces Adwords Express

Google AdWords Express is a cut down version of Google AdWords system.  The express version has fewer options which can be an advantage for those who have small business and don’t want to get into the complexities of setting individual parameters for keywords or get involved in the many other options that are available.

The user has to have a Google+ local page and then they can create their campaign.  There is no concept of bidding for keywords, you just set up a budget.  The introduction of Google AdWords Express is to improve the local listing experiences.

Posted in SOE

Links to your site using Google Webmaster

If you want to find which web site links to your site, one way is to your Google Webmaster. (http://wwwgoogle.com/webmaster)

The site has to be verified in the webmaster site and then :

On the Webmaster Tools Home page, click the site you want.
On the left-hand menu, click Traffic, and then click Links to Your Site.

Posted in SOE

About htpasswd – The file to store passwords

The htpasswd file is used when password protecting a website or a directory using HTTP Authentication and Apache’s htaccess files.

The htpasswd file contains username in plain text (unencrypted) and a hashed (encrypted) password.

Each line contains a username and a password separated by a colon “:”. You can not see the actual passwords as they are hashed (encrypted) using a complex algorithm. The default algorithm is different from platform to platform. On Windows the passwords are hashed using MD5, and on Linux its based on a system function called “crypt()”.

Normally the htpasswd file is named .htpasswd, but you can name your password file what every you like. You could call it “passwords.txt”, however, Apache is usually configured to prevent access to .ht* files – starting with “.ht”. If you name your password file “passwords.txt”, a user could access it, and retrieve all valid usernames. Since the passwords are hashed he can’t use them directly, but it will help him gain access using brute force.

It is therefore recommended to name a password file .htpasswd.

Hashed passwords can be generated with the command-line tool htpasswd (htpasswd.exe on Windows) which is part of a normal Apache installation. You can also create passwords yourself using PHP.

get_magic_quotes_gpc in PHP6

In PHP6, get_magic_qu0tes_gpc will be depreciated, but you may still want to be able to test for it if you do not know which version of PHP it will be running on.

Here are two possible ways of how this might be done.  Both of these create a new function called is_get_magic_quotes_gpc to make it easier to search and replace in a number of files.

First method :

<?php

/**
Returns true if have to make allowances for magic quotes or not
Returns true if magic quotes are on false otherwise
Test which version of PHP you have and make allowences as appropriate
**/
function is_get_magic_quotes_gpc() {

if ( version_compare(phpversion(), ‘6’, ‘<‘) ) {

if ( get_magic_quotes_gpc() ) {
return true;
}
else
{
return false;
}

}
else
{
return false;
}

}
?>

Second method :

<?php

/**
Returns true if have to make allowances for magic quotes or not
Returns true if magic quotes are on false otherwise
Test if the function exists or not and then make allowances as appropriate
**/
function is_get_magic_quotes_gpc() {

if ( function_exists(“get_magic_quotes_gpc”) )
{
if ( get_magic_quotes_gpc() ) {
return true;
}
else
{
return false;
}
}
else
{
return false;
}

}

?>

Both of these can be tested using :

<?php

if ( is_get_magic_quotes_gpc() ) {
echo(“yes”);
}
else
{
echo(“no”);
}

echo(“<p>php version : ” . phpversion() . “</p>”);

?>

Creating a different rollover for an href using a class

Here are a couple of ways of creating a different rollover effect for an href link.

Method 1 using a class

<style type=”text/css”>
a.leftLink
{
color: #00f;
text-decoration: none
}

a.leftLink:hover
{
color: #f90;
text-decoration: underline
}
</style>

and this is used as follows :

<a href=”#”>This is a link</a>

or

Method 2 using a class in a div

and this is used as follows :

<style type=”text/css”>
.leftLink { margin-top:5px;margin-bottom:5px;font-size:small; }
.leftLink a { color:White; }
.leftLink a:hover { color:#ff9933; }
</style>

<div><a href=”#”>This is a link</a></div>

Signing up for PayPal Express Checkout

To work with PayPal digital goods you need to use PayPal Express Checkout system and then get the API credentials from the PayPal site.

Use the following link to sign up with PayPal :

Express Checkout sign up

https://merchant.paypal.com/cgi-bin/marketingweb?cmd=_render-content&content_ID=merchant/digital_goods

You select the Express Checkout option and then either create a new account or convert your existing account into a Business account.

I have had some problems in converting an existing account to a Business account that will work with express checkout and I have found that the best way is to create a new PayPal account from scratch using the above link.

This sets up your account to work with Express Checkout.

Now you can get your signature details as follows :

Log into you PayPal account.

Click on the ‘Profile‘ tab.

Click on ‘My Selling Preferences‘ on the left hand side of the page.

For API Access, click on the ‘Update‘ link.

This takes you to the ‘API Access‘ page.

Click on ‘Request API credentials’ link and this takes you to the ‘Request API Credentials‘.

You need to select the ‘Request API signature’ so that you can obtain the three components of :

API Username
API password
API signature

Make sure that the Request API signature radio button is selected.

Then click on ‘Agree and Submit‘ button.

Copy all the details and store in a secure place on your PC.

Too many mySQL connections

If you have the situation in mySQL where there is the possibility of large number of connections, you can test for that specific error and then redirect to an different page :

<?php $link = mysql_connect("localhost", "mysql_user",
"mysql_password");
if (mysql_errno() == 1203) {
// 1203 == ER_TOO_MANY_USER_CONNECTIONS (mysqld_error.h)
header("Location: http://your.site.com/alternate_page.php");
exit;
}
?>