Rapidweaver – PHP & MySQL

If I write all my SQL statements in PHP code is this ideal for performance?

My first question in response to this is what is considered “ideal” for performance? Having worked as a technical performance specialist for a variety of companies, we need to look more closely at what is considered adequate performance. The true measure of performance is the time it takes to generate a report, respond to a query and so on (response times). Each one of these have different expectations on response times. For example, having a report processing millions of records in a database to summarize is expected to take several seconds/minutes and in some cases hours to complete. However, an online transaction processing (OLTP) solution cannot have the end user waiting minutes or hours to complete the purchase of a product.

Expectations should be clearly drawn up before working on performance related issues.

Before working on performance related issues you need to understand the architecture that you are dealing with and understanding how your application interacts with the hosts and the server processes.

Architecture

Recently I have been developing applications with databases (MySQL) and PHP. Your server architecture will play a huge role in how your application should be developed. For example, some hosting companies provide both the web server and the database server on the same machine. Access to the database is made via internal memory structures which are quick. However, some hosting companies separate the web servers from the databases and as a result applications need to establish a connection to the database and then send the database SQL statements across the network. This can significantly slow down the application.

An application connected to a database has two very important measurements:

• Response time
• Throughput

The response time is the time lag between an application sending a request and receiving a response. For example, picture a water pipe. You put water in at one end and you time the water arriving at the other end. This is your half your response time. It does not matter how much water you put in it will still arrive at the same time. The response time is directly proportional to the length of the pipe i.e. the distance it needs to cover.

Throughput on the other hand is the size of the pipe. Imagine a straw sized pipe. The amount of water reaching the other end is limited by the volume of water contained by the pipe. The larger the pipe the more water will pour through. This is your throughput. The time it takes to pour the water through on both pipes is still the same.

Localhost

Localhost is a synonym for a local based connection to the server process. This is very quick as access is typically via memory. In cases where the database server is on the same machine as the application, the pipe analogy above would give us a very large pipe with both ends close together.

Host

In cases where the database server exists on a separate host than the application then there are several things you need to consider:

• The response time between sending a request and receiving the response
• The throughput of data available from the database server

Imagine that a response time of 100ms between sending a message across the network and received by the database. The database processes the information and sends its response back to the calling application. This may seem like a small amount of time, but what happens, as many applications do, it sends another SQL statement based on the response from the first query. Several 100ms roundtrips add up very quickly.

Likewise, imagine a lot of data is either sent to the database server or received back from the database server. Imagine a 3Mbps (mega bits per second) connection (this translates to approximately 300 kilobytes per second). To transfer 1MB of data will take approximately 3 seconds. If the database server exists in the same data center as the application host it typically has a 1Gbps connection (or approximately 100Mbytes per second) which is often advertised. This can be quite misleading because all traffic inside the data center will use this 1Gbps connection and your application will only have a share of this.

If database server and application exist on different machines is there anything that can be done to improve performance?

Indeed there is. Database servers have been designed to allow stored procedures to be created and executed. If you shift the work from the application into the database then you can reduce substantially the response times and the data transfer between the application and database.

Consider the following scenario. You are building logic into the application whereby you are attempting to add a new contact into your database. To avoid duplicate entries you want to first verify that the contact name does not already exist in the database. If it does you then update the remainder of the information i.e. perform and update. If there is not existing entry then you insert a new record into the database. Without using stored procedures you would have the application send the SQL statement (does contact record already exist) across the network to the database. The database then responds back to the application that it either exists or it does not. It then makes a decision based on the response from the database and either generates an INSERT SQL statement or an UPDATE SQL statement. In this case two statements are transferred over the network, parsed by the database server and then executed. The results are then sent back and more SQL statements are executed. In the case of a stored procedure a call is made to the stored procedure which has already been parsed and simply executes. Upon completion of execution it then returns a response to the calling application. This can significantly improve performance, especially if the response times are large and the volume of data exchanged is large which is used to make some form of decision.

Another advantage of using stored procedures for any UPDATE, DELETE or INSERT statements is that all the applications business logic is maintained within the database. The business logic is the logic that is followed when data is being added into a database.

On the flip side, maintaining code at two locations for an application can become complex, the application code and the database stored procedures. Their interactions need to be fully understood as a change in one location can have substantial implications in the others.

Look out for further whitepapers on developing database stored procedures and other performance enhancement tips and tricks.

Ronan Cashell is a Senior Database Administrator/Technical Project manager with over 20 years of experience working in IT both as a database administrator and a UNIX/Linux administrator. He is a certified Oracle DBA and certified Linux administrator.

Many people have asked if there was a way to have both their private Dropbox account and their company Dropbox account co-existing under the same Mac OS X login.

I have put together the steps involved along with an automator process which you can download below.

Before we start consider the following:

• Each dropbox account will have its own running process
• Each dropbox account should have its own unique account login
• Each dropbox process should have its own dedicated “Dropbox” folder

In my example, I will be showing how this works using a Mac OS X machine. I have 2 separate dropbox accounts and have created two separate Dropbox folders. The first account will keep the default /Users/macosuser/Dropbox folder. The second account will use /Users/macosuser/emailaddress as its Dropbox folder. Under this folder, Dropbox will automatically create a folder called Dropbox.

Start a Terminal session allowing a command line to be typed in.

The first thing we need to do is to change the location of the HOME environment variable. This is pointing by default to /Users/macosuser where macosuser is your username on the Mac. If we change that to /Users/macosuser/account for example:

Last login: Mon Nov  7 19:00:19 on ttys004
macbook:~ macosuser$ export HOME=/Users/macosuser/test/
macbook:~ macosuser$ mkdir $HOME
macbook:~ macosuser$ /Applications/Dropbox.app/Contents/MacOS/Dropbox &
[1] 22774
bash-3.2$

If this is the first time you are running this you will be prompted to either configure a new account or an existing account for Dropbox.After each reboot of the machine you will need to manually restart the Dropbox as explained above. However, once the account is configured you will no longer be prompted for each restart.

Automated Service

We have also developed a Finder service which allows you to select a folder in Finder and enable the Dropbox Setup which automates the steps above.

If you wish to get a copy of this service please fill in your email address and we will email you a link to the downloadable file. These emails are not passed on to 3rd parties and are used to notify you of changes to “Dropbox Setup” or any new products that we release.

When a web browsers sends a request to a web server, the web server automatically determines based on the file extension what needs to be done with that requested file. A file with the extension .html is streamed back to the web browser that handles and displays the content. However, what the web server is doing automatically is determining based on the file extension the type of content (called MIME type) and sending headers back to the web browser the MIME type. For instance, a request for a .html file has the web server generate a HTTP header with the MIME type “text/html”. The web browser analyses the MIME type to determine what should be done with it.

The example above is quite simple, but what if we request a .pdf document. The web server will return in the HTTP header the MIME type “application/pdf” and the browser determines what to do with the information. In this scenario the browser may startup PDF reader or if PDF reader is not installed will popup a message allowing the file to be saved.

Furthermore, the information sent is a stream of data and not a file. So when a request is sent to a web server for a .pdf document, the web server generates automatically the HTTP header with the correct MIME type and then streams the contents of the file to the web browser. It does not transfer the file itself.

Understanding this is a basic building block for building secure and functional websites.

To have a look at file extensions and their associated MIME types have a look here

UNIX and Linux systems often return an errno, a numerical identifier to indicate the source of a problem. I have put together a mini application which allows you to add in the errno and it will return the UNIX/Linux explanation of the error.

Check it out here

After reviewing the RW forum, we have noticed that there are many issues with the RW Contact form. An approach we have come up with is to create a PHP script that tests for as many of the features being used by the contact form and see if these are working rather than testing through the contact form.

To download the script click here.

Instructions

1. Download the file
2. Unzip the file
3. A PHP script forminfo.php is generated and this should be uploaded to your web server
4. To test http://youwebserver/locationoffile/forminfo.php?email=mac@me.com

The results of executing the script are as follows (the green icons showing success and the red a failure). In the case of webserver we are trying to determine whether your ISP is using Windows IIS or Apache as the server. The red just indicates which one is not being used. Should you have comments or suggestions on improvements please do not hesitate in contacting us at (info at it-iss dot com):

A recent posting on the RapidWeaver forum was asking for a stack to encrypt information as it is transmitted to the server. The best way of handling the encryption of information over the network is to use SSL encryption using HTTPS. I have prepared a mini screencast showing the impact of using HTTP calls versus HTTPS showing the network traffic at a packet level which shows the contents being transmitted. In the case of HTTP the information is transmitted plain text and in the case of HTTPS it is encrypted and undecipherable.

Here is the screencast (you can make out below “name=this+is+a+test”) showing the contents that was transmitted over the network:

I have also included the output for the low level packet trace to show that the output really is unreadable.

The is the unencrypted method:

21:48:02.137395 IP 192.168.1.65.64545 > linux.http: Flags [S], seq 3423171503, win 65535, options [mss 1460,nop,wscale 3,nop,nop,TS val 65236850 ecr 0,sackOK,eol], length 0
E..@..@.@......A.....!.P.	s........................
..or........
21:48:02.137954 IP 192.168.1.65.64545 > linux.http: Flags [.], ack 2963052628, win 65535, options [nop,nop,TS val 65236850 ecr 343869153], length 0
E..4..@.@......A.....!.P.	s....T...........
..or....
21:48:02.138022 IP 192.168.1.65.64545 > linux.http: Flags [P.], seq 0:560, ack 1, win 65535, options [nop,nop,TS val 65236850 ecr 343869153], length 560
E..d..@.@......A.....!.P.	s....T...........
..or....POST /rw/form/process.php HTTP/1.1
Host: linux
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; en-US; rv:1.9.2.13) Gecko/20101203 Firefox/3.6.13
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
Accept-Language: en-us,en;q=0.5
Accept-Encoding: gzip,deflate
Accept-Charset: ISO-8859-1,utf-8;q=0.7,*;q=0.7
Keep-Alive: 115
Connection: keep-alive
Referer: http://linux/rw/form/
Cookie: PHPSESSID=vod37o1rogncu9dpje2mpjlurcr8nucd
Content-Type: application/x-www-form-urlencoded
Content-Length: 19

name=this+is+a+test
21:48:02.142814 IP 192.168.1.65.64545 > linux.http: Flags [.], ack 219, win 65535, options [nop,nop,TS val 65236850 ecr 343869155], length 0
E..4,.@.@......A.....!.P.	u................
..or....
^C
4 packets captured
12 packets received by filter
0 packets dropped by kernel

And this is the encrypted method:

listening on en0, link-type EN10MB (Ethernet), capture size 65535 bytes
21:48:37.078451 IP 192.168.1.65.64548 > linux.https: Flags [S], seq 550062483, win 65535, options [mss 1460,nop,wscale 3,nop,nop,TS val 65237199 ecr 0,sackOK,eol], length 0
E..@..@.@......A.....$.. .I........................
..p.........
21:48:37.079122 IP 192.168.1.65.64548 > linux.https: Flags [.], ack 3513206321, win 65535, options [nop,nop,TS val 65237199 ecr 343877888], length 0
E..4m.@.@......A.....$.. .I..gF1...........
..p...).
21:48:37.079382 IP 192.168.1.65.64548 > linux.https: Flags [P.], seq 0:190, ack 1, win 65535, options [nop,nop,TS val 65237199 ecr 343877888], length 190
E.....@.@......A.....$.. .I..gF1...........
..p...)............Mo....sb]...@...N...IE....xq.... yD[......YF!+.........".T.3......H...
.......8.......5.9...	.....E.D.3.2...........A...../.........
...$...
.....linux.
.................#..
21:48:37.175933 IP 192.168.1.65.64548 > linux.https: Flags [.], ack 2310, win 65414, options [nop,nop,TS val 65237200 ecr 343877912], length 0
E..4|.@.@......A.....$.. .JR.gO6...........
..p...).
21:48:37.199070 IP 192.168.1.65.64548 > linux.https: Flags [P.], seq 190:388, ack 2310, win 65535, options [nop,nop,TS val 65237200 ecr 343877912], length 198
E.....@.@......A.....$.. .JR.gO6...........
..p...)...............`.B....r.Z.........	..........`"........TP.[kK....C2... .A......D.H....2L,.R.$;G..S....+!......."......j.F..E6Z...X.+.b....P...........0..=..q .f...X..Q51V...Mh..C0m....'...*..GDkv...~
21:48:37.206204 IP 192.168.1.65.64548 > linux.https: Flags [.], ack 2369, win 65535, options [nop,nop,TS val 65237200 ecr 343877920], length 0
E..4..@.@......A.....$.. .K..gOq...........
..p...)
21:48:37.207794 IP 192.168.1.65.64548 > linux.https: Flags [P.], seq 388:985, ack 2369, win 65535, options [nop,nop,TS val 65237200 ecr 343877920], length 597
E...?.@.@......A.....$.. .K..gOq...........
*..z....Y...|5L2.?>...J...3.......](...a.."......7P..l..j.M..!P20.l..F.Y..f.Cxt..)?6.....&v..b8.-.....M....\dV.$}....e...}.\..d........{5...-..P2Q.z...7C.. N........g.8Y..`}...6.......H.Fi.R.I./@ 0.%.=.W....x,t...*k:sa.Q...jA..xH...;....P|....=......TNT.D..r*.x6.zU?.q.......([;...n4.2..O..).........!.....Z.....l..Y...-.XPh.....x..0.....4v>.).J.-[...c$c.\.......)z.\.X...R
21:48:37.213308 IP 192.168.1.65.64548 > linux.https: Flags [.], ack 2614, win 65535, options [nop,nop,TS val 65237200 ecr 343877922], length 0
E..4!.@.@......A.....$.. .Mm.gPf...........
..p...)"
^C
8 packets captured
106 packets received by filter
0 packets dropped by kernel

I am working on a tutorial which will allow emails to be added into a form and written into a database. There are many parts to this so I will be breaking down this tutorial into different sections:

• RapidWeaver and forms
• Forms and PHP
• PHP and MySQL
• MySQL and SQL
• Ajax and PHP

This is a very short introduction to how MySQL works in client/server mode. It explains how a client connects on the same machine, on other machines in a heterogeneous environment.

MySQL offers transaction processing. What does this mean? It means that if you start a transaction and perform UPDATEs, DELETEs and INSERTs but for some reason the processing halts. These will be rolled back to the state prior to starting the processing.

Lets imagine we have a data model with a parent/child relationship. We need to remove entries from the child table, then from the parent table, then add entries into the parent table and finally add entries into the child table. If we do not do this as a transaction and the processing fails during this operation we do not know what state the table has been left in.

If we are satisfied with the state of the table then we can COMMIT the transaction. If we are not happy we can ROLLBACK the transaction.

The first thing to make sure is that the storage engine that you use when creating the tables in MySQL support transactions. The most common storage engine in MySQL supporting transactions is InnoDB. MySQL cluster tables NDB also support transactions but these are used if a high availability cluster of databases has been configured.

Good PHP development would be to use something like this while coding:


// Start the transaction
mysql_query("BEGIN");
try {
mysql_query("DELETE FROM childtable");
mysql_query("DELETE FROM parenttable");
mysql_query("INSERT INTO parenttable (...) VALUES (...)");
mysql_query("INSERT INTO childtable (...) VALUES (...)");
mysql_query("COMMIT");
} catch (Exception $e) {
mysql_query("ROLLBACK");
}


I have same many times users of Formloom running into difficulty during the applying of data into the database. In most cases this is directly related to the use of reserved words for field names. For example, the word REPEAT is reserved in MySQL. This can be handled by escaping the entries but this does not appear to be done in Formloom. As a result, the best practice is to avoid the use of the reserved words. I have compiled a list of all the MySQL reserved words for releases 5.0, 5.1 and 5.5.

Click here to see this post with all the reserved words. Some reserved words have been added in specific versions of MySQL and these can be seen with the asterisk “*” before the icon indicating that it is a reserved word. If you hover over this it will provide more details concerning the specific versions that it was either included or deprecated.