A few interesting mySQL functions

You can see the process list, find out who is doing what and kill a process if needed.

If you log into PHPmyAdmin and enter the following into the SQL text box :

show processlist;

You will see a list of the processes.

If you are using the mysql command line environment then you can do it at the command line as well

mysql> show processlist;

Once you know the process ID you can then kill the process using the ID number as follows :

mysql>kill 349

Here are some further functions that may be of interest :

show status;

show table status like ‘%’;

The above gives you create time and other information.

All can be run from within PHPmyAdmin or from the mysql command line.

An INSERT INTO with other calculations

This mySQL “Insert from another table with select” example shows how you can concatenate two of the selected fields into one inserted field.

In this example we are inserting data into tbllistings using data from tblrawdata but we are doing other processing on fields before it is inserted.  Obviously you can use any of the other mySQL select string, arithmetic and mathematical operators and functions when you do your select statement and then insert those into the new table.

INSERT INTO tbllistings (
.. etc
concat(property_id, agent_id),
.. etc
FROM tblrawdata;

Simple transaction example using PHP and mysql_query

// 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”;
commit(); // Committ transaction
echo “Transaction completed”;

function begin() {

function commit() {

function 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:

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

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:

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

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.