15 Ways to Optimize Your SQL Queries

Previous article was on 10 Ways To Destroy A SQL Database that sort of teaches you what mistakes many company might make on their database that will eventually lead to a database destroy. In this article,  you will get to know 15 ways to optimize your SQL queries. Many ways are common to optimize a query while others are less obvious.

Indexes

Index your column is a common way to optimize your search result. Nonetheless, one must fully understand how does indexing work in each database in order to fully utilize indexes. On the other hand, useless and simply indexing without understanding how it work might just do the opposite.

Symbol Operator

Symbol operator such as >,<,=,!=, etc. are very helpful in our query. We can optimize some of our query with symbol operator provided the column is indexed. For example,

SELECT * FROM TABLE WHERE COLUMN > 16

Now, the above query is not optimized due to the fact that the DBMS will have to look for the value 16 THEN scan forward to value 16 and below. On the other hand, a optimized value will be

SELECT * FROM TABLE WHERE COLUMN >= 15

This way the DBMS might jump straight away to value 15 instead. It's pretty much the same way how we find a value 15 (we scan through and target ONLY 15) compare to a value smaller than 16 (we have to determine whether the value is smaller than 16; additional operation).

Wildcard

In SQL, wildcard is provided for us with '%' symbol. Using wildcard will definitely slow down your query especially for table that are really huge. We can optimize our query with wildcard by doing a postfix wildcard instead of pre or full wildcard.

#Full wildcard
SELECT * FROM TABLE WHERE COLUMN LIKE '%hello%';
#Postfix wildcard
SELECT * FROM TABLE WHERE COLUMN LIKE  'hello%';
#Prefix wildcard
SELECT * FROM TABLE WHERE COLUMN LIKE  '%hello';

That column must be indexed for such optimize to be applied.

P.S: Doing a full wildcard in a few million records table is equivalence to killing the database.

NOT Operator

Try to avoid NOT operator in SQL. It is much faster to search for an exact match (positive operator) such as using the LIKE, IN, EXIST or = symbol operator instead of a negative operator such as NOT LIKE, NOT IN, NOT EXIST or != symbol. Using a negative operator will cause the search to find every single row to identify that they are ALL not belong or exist within the table. On the other hand, using a positive operator just stop immediately once the result has been found. Imagine you have 1 million record in a table. That's bad.

COUNT VS EXIST

Some of us might use COUNT operator to determine whether a particular data exist

SELECT COLUMN FROM TABLE WHERE COUNT(COLUMN) > 0

Similarly, this is very bad query since count will search for all record exist on the table to determine the numeric value of field 'COLUMN'. The better alternative will be to use the EXIST operator where it will stop once it found the first record. Hence, it exist.

Wildcard VS Substr

Most developer practiced Indexing. Hence, if a particular COLUMN has been indexed, it is best to use wildcard instead of substr.

#BAD
SELECT * FROM TABLE WHERE  substr ( COLUMN, 1, 1 ) = 'value'.

The above will substr every single row in order to seek for the single character 'value'. On the other hand,

#BETTER
SELECT * FROM TABLE WHERE  COLUMN = 'value%'.

Wildcard query will run faster if the above query is searching for all rows that contain 'value' as the first character. Example,

#SEARCH FOR ALL ROWS WITH THE FIRST CHARACTER AS 'E'
SELECT * FROM TABLE WHERE  COLUMN = 'E%'.

Index Unique Column

Some database such as MySQL search better with column that are unique and indexed. Hence, it is best to remember to index those columns that are unique. And if the column is truly unique, declare them as one. However, if that particular column was never used for searching purposes, it gives no reason to index that particular column although it is given unique.

Max and Min Operators

Max and Min operators look for the maximum or minimum value in a column. We can further optimize this by placing a indexing on that particular columnMisleading We can use Max or Min on columns that already established such Indexes. But if that particular column is frequently use, having an index should help speed up such searching and at the same time speed max and min operators. This makes searching for maximum or minimum value faster. Deliberate having an index just to speed up Max and Min is always not advisable. Its like sacrifice the whole forest for a merely a tree.

Data Types

Use the most efficient (smallest) data types possible. It is unnecessary and sometimes dangerous to provide a huge data type when a smaller one will be more than sufficient to optimize your structure. Example, using the smaller integer types if possible to get smaller tables. MEDIUMINT is often a better choice than INT because a MEDIUMINT column uses 25% less space. On the other hand, VARCHAR will be better than longtext to store an email or small details.

Primary Index

The primary column that is used for indexing should be made as short as possible. This makes identification of each row easy and efficient by the DBMS.

String indexing

It is unnecessary to index the whole string when a prefix or postfix of the string can be indexed instead. Especially if the prefix or postfix of the string provides a unique identifier for the string, it is advisable to perform such indexing. Shorter indexes are faster, not only because they require less disk space, but because they also give you more hits in the index cache, and thus fewer disk seeks.

Limit The Result

Another common way of optimizing your query is to minimize the number of row return. If a table have a few billion records and a search query without limitation will just break the database with a simple SQL query such as this.

SELECT * FROM TABLE

Hence, don't be lazy and try to limit the result turn which is both efficient and can help minimize the damage of an SQL injection attack.

SELECT * FROM TABLE WHERE 1 LIMIT 10

Use Default Value

If you are using MySQL, take advantage of the fact that columns have default values. Insert values explicitly only when the value to be inserted differs from the default. This reduces the parsing that MySQL must do and improves the insert speed.

In Subquery

Some of us will use a subquery within the IN operator such as this.

SELECT * FROM TABLE WHERE COLUMN IN (SELECT COLUMN FROM TABLE)

Doing this is very expensive because SQL query will evaluate the outer query first before proceed with the inner query. Instead we can use this instead.

SELECT * FROM TABLE, (SELECT COLUMN FROM TABLE) as dummytable WHERE dummytable.COLUMN = TABLE.COLUMN;

Using dummy table is better than using an IN operator to do a subquery. Alternative, an exist operator is also better.

Utilize Union instead of OR

Indexes lose their speed advantage when using them in OR-situations in MySQL at least. Hence, this will not be useful although indexes is being applied

SELECT * FROM TABLE WHERE COLUMN_A = 'value' OR COLUMN_B = 'value'

On the other hand, using Union such as this will utilize Indexes.

SELECT * FROM TABLE WHERE COLUMN_A = 'value'
UNION
SELECT * FROM TABLE WHERE COLUMN_B = 'value'

Hence, run faster.

Summary

Definitely, these optimization tips doesn't guarantee that your queries won't become your system bottleneck. It will require much more benchmarking and profiling to further optimize your SQL queries. However, the above simple optimization can be utilize by anyone that might just help save some colleague rich bowl while you learn to write good queries. (its either you or your team leader/manager)

10 Ways To Destroy A SQL Database

Database is the asset of most online or internet based company. Everyone looks at how to improve and secure their databases to protect or improve their company. While everyone is searching for remedies or enhancement pills for their company, there are often simple mistakes made by some companies (especially the small to middle ones) that might just destroy their businesses. Rather than looking at how we can protect our database, this article will look at ways to destroy it instead! (through mistake, of course)

Don't Monitor Error Log

The first line of defense that any database would have. Error log may indicates first time problem occurs or warnings that your database might be facing problem. These troubles can be easily avoided or missed depending on what you do. Be my guest and ignore error log will definitely help to destroy your database.

Many company databases are designed in a way to enforce availability. Hence, there will surely be primary and slaves databases in such company. These databases also contain error files. However, if you would like your secondary databases or slave databases go out of sync with the primary database, be sure to ignore the error file and give it some time. Depending on the size of your company, the amount of data lost caused by the lost of synchronization might just cost you dearly when some hero DBA shutdown the primary database without issuing "stop slave" first on the slave database or wait till some errant SQL come down the line. Although this might take some times to destroy your database but its worth to think about using it.

Don't Fine Tune Queries

You have a big server, lots of memory and fast disk, so don't have to worry. Continue with this attitude and you are on your way to success (destroy it!). Developers writing bad code that caused full table scan and trying their best to trash your query cache, overloading your Innodb buffer cache with useless blocks. Plus hitting disk instead of main memory as much as possible? Well, there won't be ANY problem since every piece of hardware is the latest, fastest and most powerful ones! Let's just wait and see till your database fall on its knees! Especially when data is getting larger! That's the best time we see this happen!

Don't Document Procedures and Configurations

Ah! No documentation won't cause a single problem! No problem at all my friend! Why the need to enforce such tedious job when 'we' can maintain the job? Just you wait when the 'we' becomes 'i' and 'i' becomes 'who'. Employees come and go nowadays. People always look for a better future in life and no matter what happens to you, it's really none of their concern. Man! What so difficult? Let's just hire an expert to take the job. Oh boy! That's a great solution! Let's see when he presses the wrong button 🙂

Don't Backup

Another great way to destroy your database is to avoid making backups! Hardware failure is a common thing in data center. Hard disk fail, power supply down, plugs get pulled, basically anything you can imagine. Don't backup regularly can just do the thing!

Its not only hardware that might assist you in your task. Developers or DBA who accidentally deletes data can also be of help. Deleting columns, table, rows, data or even database! These sort of things do happen and it happens quite frequently. Other than deleting data, mistakes made on program might just transfer the wrong data to the wrong place. All of these are just things that might happen to any IT firms. Well, there is always never happen before situation for some of you. Just follow your instinct (backup suck!) on this and you will do just fine.

Don't Use Memory Wisely

Server nowadays have huge memory installed with it. Technology advancement has made everything more powerful than before. Furthermore, it is that affordable that many companies can afford big and fast memory! With such powerful memory backup, we can assure that whatever developers throw in, the server and database will surely be able to take it! We can safely assume MySQL knows our database memory requirement! We just have to run the wizard installation and viola! Everything is automatic nowadays! There won't be such thing as misallocation of memory. The system is perfect.

Good to know. You have everything that required (even attitude) in preparing to bomb up your database.

Don't Worry About Indexes

Indexes is the most effective way to destroy your database. However, you must know the trick to do this. There are two ways to succeed. The first one required you to do absolutely nothing. No indexing is required purely full scan table. However, this required certain criteria to be meet but it should do the trick. If this doesn't suit your taste, you can try a faster way by creating useless or unwanted indexes and ensure that your table have tons of records. This can surely improve the process of destroying your database.

Don't Normalize Your Database Design

If you are just starting to build a system, you can consider skipping normalization in your database design. Skipping normalization can help contribute to bad database design which is part of the plan to destroy a database. Furthermore, without normalization, there is a good chance your system can be inaccurate, slow, and inefficient, and it might not even produce the data you expect.

Don't Make Policies For Database Patch

Its true that majority company doesn't update their databases immediately after a new security vulnerability patch has been released. It would be irresponsible for a company to deploy a patch in production without first running it through quality assurance. Furthermore, some companies didn't event bother to have policies to update their database. If you think that databases are a little more isolated than the desktop, there's less of a security concern and thinks that your databases are more secure because they're behind firewalls and and have a good perimeter security, you are on the right track of destroying a database.

Don't Bother Caching

My database can take tons of crap anything throw towards it. Its the fastest computer (why bother to have technology advancement when we already have the fastest? Dot) on the planet. Cache or no cache won't destroy my database. Its the fastest (ya ya, i get it.). The dramatics performance gain using cache table might not interest you. Scalability, flexibility, availability and performance are just some benefits that caching can gives. Multitier architecture, what bullshit. Your server will NEVER go down and you will NEVER required cache table to be available. Millions of hits on the server database might just do the trick on helping you achieve what you want in this article. It will definitely work better when your table has few millions record and a few lousy queries. (it might not even required millions of hit to kill it)

Don't Use Fast or Reliable Disk

Using something like a single disk or mirror will definitely makes your I/O the main bottleneck on your system. With the help of a single disk, you can expect OS and your database fighting for resources, serving one user at a time while others waiting for their turn. To make things worst, you can try utilize RAID-5 instead of RAID-10. May be you already are!  Well, to  compare between these two, RAID-5 only performed reasonably well on read while RAID-10 exceed almost two times better than RAID-5 on writes. RAID-5 only can handle 1 fails and any drive die will approximately caused 64% degration in read performance until the faulty drive is discovered. Furthermore during recovery, read performance for a RAID5 array is degraded by as much as 80% compared to RAID-5 which only degrade performance on the faulty disk itself. There are more 'advantages' on RAID-5 but i will just stop here. RAID-5 seems to be good with destroying than RAID-10 don't you think so?

Conclusion

The points discuss here might just happen to large data or traffic internet site (well, your site will eventually grow to have big data, hopefully). However, the conclusion to all these jokes are more valuable; Learn to save your ass. No one will.

Integrate Paypal Express Checkout Solution

As a web developer, there will surely be someday where you wish to integrate Paypal into one of your products or services. The most appropriate way is to read the documentation provided by Paypal. But reading it doesn't mean you will understand the documentation with one shot and this call for a lot of research and finding before your Paypal will work. I went through this process these few days that is why there wasn't much article written in the process.  Although it wasn't really difficult but going through the process of reading first before looking into their sample codes really wasn't the correct way of approaching this solution. Instead, looking into the sample code will definitely brings light to integrating Paypal express checkout solution (well, you still have to read a bit). In this article, i will try to demonstrate Paypal express checkout solution as simple as possible for you guys to be able to DIY.

Paypal Express Checkout

What is Paypal Express checkout solution? Paypal Express Checkout makes it easier for your customers to pay and allows you to accept PayPal while retaining control of the buyer and overall checkout flow. This means that you can integrate a payment solution with Paypal that retain most of the interaction on your website other than user login and verifying the product they are purchasing. Paypal express checkout also provides you with the ability to create recurring payment which can really eliminate the need to repurchase the exact service or product every single time. However, Paypal express checkout solution doesn't have the ability to allow your user to use credit card for purchases. Your customers must have Paypal in order to purchase with this solution. Credit card solution will only be available together with Paypal in Website Payment Pro solution. Hopefully this clear some doubt and help you select what solution you really need.

Integrate Paypal Express Checkout Solution - Step 1

Firstly, you might wonder where exactly are the correct documentation out of all the places in Paypal. You can get the documentation and Sample at the respective links. The sample is contain at the section PayPal API: Name-Value Pair Interface as i believe this will give you a better understanding on the flow of Paypal express check out solution. The sample files will required you to throw them  into your server and run (go to the browser and key in the url you have thrown the folder into) as it will simulate some of the payment flow you might want. Then you will look into the code and see how they are achieved. Please take note that localhost might not work for you as it will required you to have curl installed.

Integrate Paypal Express Checkout Solution - Step 2

Once the sample are placed into your server and you have play around, the next thing you might wonder is the exact file you will required to run your own Paypal express checkout solution. And here are the files you will only need.

  • APIError - display error
  • CallerService - main player that initial the talk
  • constants - all the required variables
  • SetExpressCheckout - display for step 1 of the process
  • GetExpressCheckoutDetails - display for step 2 of the process
  • DoExpressCheckoutPayment -  display for step 3 of the process + send final request to paypal
  • ReviewOrder - request handler for step 1 and responsible to redirect to step 2

The files i am looking at are all PHP files.  Well, the above file respective function should be self explained. The first 3 files(APIError, CallerService and Constants) are the files imported into the ReviewOrder and  DoExpressCheckoutPayment files as they are required to talk to Paypal. Once we understand this it is time to go into a more complicated stuff.

Integrate Paypal Express Checkout Solution - Step 3

To illustrate what is going on in the sample file, we will look at the following diagram provided by Paypal.

From left to right, we have 5 interfaces user will see. And two of them are display from Paypal where it is colored in blue (2nd and 3rd interface). Hence, we left with 3 interfaces which are SetExpressCheckout, GetExpressCheckoutDetails and DoExpressCheckoutPayment which is 1st, 4th and 5th interface respectively.  So we are all clear with the display files right now. Next we will need to know where ReviewOrder will appear. There are altogether 4 Calcuts as written on the diagram. The ReviewOrder will be triggered on the 2nd and 3rd Calcuts where SetExpressCheckout API and GETExpressCheckoutDetails API is being fired. Don't worry about what does these API means at the moment. Just treat them as a method that will tell Paypal what they do.

Integrate Paypal Express Checkout Solution - Step 4

I guess everyone should understand how Paypal work looking at the sample file and the explanation above.  Next i will explain some of the important things you will need to know since writing all the codes here is meaningless as they are the same for every sample files. It just makes it more confusing to read. Firstly, for each request made to Paypal, you will always see the following line in the sample file.

$resArray=hash_call("SetExpressCheckout",$nvpstr);

where $nvpstr is the name-value pair string passed into the method hash_call. What this function hash_call does it to send the request to Paypal to notify them the action you performing. In this case, SetExpressCheckout API is being performed here. There are also other API as mention previous such as GetExpressCheckoutDetails API and DoExpressCheckoutPayment API. These are the three API you will need to talk to Paypal in each stage shown on the previous diagram. So we should all clear about what does API mean that are written all over the Paypal documentation. The next important step is to know what name-value pair does each API required you to send in order for Paypal to understand you.

Integrate Paypal Express Checkout Solution - Step 5

Here we will see what does each API in the process of express checkout required. For SetExpressCheckout, you will required to have the following name-value pair in your string.

  • AMT
  • CURRENCYCODE
  • RETURNURL
  • CANCELURL
  • PAYMENTACTION

That is all! But in the sample it gives you more than just the above which is pretty good to understand what can be dump into the nvp string for it to display what you want on the paypal website where your user gets redirected.

For GetExpressCheckoutDetails API is pretty simple. It will just required you to have a token passed into the nvp string and this token can be retrieved via $_GET method where Paypal send it through there.

Lastly, for DoExpressCheckoutPayment API, you will need to provide the following nvp for it to work.

  • TOKEN
  • PAYERID
  • AMT
  • CURRENCYCODE
  • PAYMENTACTION

And that's it! The value forGetExpressCheckoutDetails and DoExpressCheckoutPayment API are provided by Paypal during the process while SetExpressCheckout data are given by you.

Summary

I believe the above explanations were pretty clear. But i still used quite a hell lots of time working on it *SLAP MYSELF*! This article is intended to provide any newbie on Paypal to get the hang of integrating Paypal without the need to spend time on reading and learning all about Paypal integration. However, the sample provided by Paypal is not secure and is only used to serve as a demonstration on 'how integration can be made easy'. I believe this article will be pretty useful for anyone to understand how Paypal work rather than reading few thousand words given by Paypal and never direct you to the correct sources or code (other than more documentation). Guess what? I found this Paypal Integration Wizard which is a wizard that creates all the above codes for you! :[

Using WordPress dbDelta Function

Many of us who develop WordPress plugin might have come across dbDelta function. dbDelta function is usually used when one wish to create table for your WordPress plugin. However, this function might not be that easy to deal with since it is not an official function in WordPress. Nonetheless it is a powerful function that majority of us would want to utilize. In this article, we will talk about dbDelta function and how we can ensure that it perform what it is made to do.

dbDelta Function

Like i mention before in one of my article, dbDelta function has the ability to examine the current table structure, compares it to the desired table structure, and either adds or modifies the table as necessary, so it can be very handy for updates of our plugin. However, unlike many WordPress function, dbDelta function is the most picky and troublesome one.  In order for dbDelta function to work, a few criteria will have to be met.

  1. You have to put each field on its own line in your SQL statement.
  2. You have to have two spaces between the words PRIMARY KEY and the definition of your primary key.
  3. You must use the key word KEY rather than its synonym INDEX and you must include at least one KEY.

Well, the above criteria's seem easy to achieve. But wait till it hits you.

Strengthen and Weakness of dbDelta Function

The strength of this function is that we are assure that any modification to our table structure will be shown on the plugin. Hence, we won't have to worry about our user plugin not being updated whenever we change our table structure to accommodate new features. This function which is build by the WordPress community is definitely much more secure than other function that an individual came up with to solve such problem. Hence, the function itself is much more reliable. Using dbDelta also removes the need to request each individual instruction to be execute separately. Code can be sum up and dump into dbDelta function for it to run.

On the other end, like i mention earlier, this can be a real pain in the ass. dbDelta function is not very tolerant against mistakes. Hence, any mistakes made on your SQL query might just fail this function. Furthermore, certain restriction is given to you in order to utilize this function. If you accidentally break such restriction, the function will fail. Moreover, no documentation were provided for this method which makes it much more time consuming to get the hang of it. In case you haven't notice, dbDelta will only update new fields or keys. This means that if you decide to remove any particular field or keys on your table and hoping dbDelta will help you out with it, you are wrong.  And if dbDelta function fail to work for you, debugging it might just be as headache since printing out the message on dbDelta might not work well for you. To make things worst,  Wordpress will mark an error on your plugin if you try to exit(0) on some part of the script instead of stopping and display the printing message.

Using dbDelta function

Initially using dbDelta function wasn't that bad. We just have to be very careful with the spaces given. An example given by WordPress would be the one shown below,

$sql = "CREATE TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT,
	  time bigint(11) DEFAULT '0' NOT NULL,
	  name tinytext NOT NULL,
	  text text NOT NULL,
	  url VARCHAR(55) NOT NULL,
	  UNIQUE KEY id (id)
	);";

require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta($sql);

Well, if you copy directly and change the field name, it should work nicely for you. But take note of the spaces given. Here are a few example that will cause dbDelta function to fail.

$sql = "CREATE  TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT,
	  time bigint(11) DEFAULT '0' NOT NULL,
	  name tinytext NOT NULL,
	  text text NOT NULL,
	  url VARCHAR(55) NOT NULL,
	  UNIQUE KEY id (id)
	);";

require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta($sql);

The above contains an extra space between CREATE and TABLE. Hence, instead of one space we have two and dbDelta fail. The same thing might happen if there are an extra space between TABLE and your table name. Another good example might occur on the key level.

$sql = "CREATE  TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT,
	  time bigint(11) DEFAULT '0' NOT NULL,
	  name tinytext NOT NULL,
	  text text NOT NULL,
	  url VARCHAR(55) NOT NULL,
	  UNIQUE KEY id (id, time)
	);";

require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta($sql);

The above fail due to this:

UNIQUE KEY id (id, time)

the correct writing should be this:

UNIQUE KEY id (id,time)

where there are no spaces between the commons. On the other hand, try to avoid having any spaces between each commons such as these

$sql = "CREATE  TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT , 
	  time bigint(11) DEFAULT '0' NOT NULL , 
	  name tinytext NOT NULL , 
	  text text NOT NULL , 
	  url VARCHAR(55) NOT NULL , 
	  UNIQUE KEY id (id, time)
	);";

require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta($sql);

It is always safe to ensure that all keyword are separated by one space and between each commas there shouldn't be any spacing. Another thing to take note is that every table creation should have a KEY in order for it to work. And like the criteria stated, each field should have its own line like the one shown on WordPress example. And at the end of each instruction there should be semicolon to be safe!

The above are things you should be cautious when using dbDelta. However, i did learn some tricks when reading the code of dbDelta function. If you are creating multiple table or query with dbDelta, it can be done using one call.

#$table_name = 'test1'
$sql = "CREATE  TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT , 
	  time bigint(11) DEFAULT '0' NOT NULL , 
	  name tinytext NOT NULL , 
	  text text NOT NULL , 
	  url VARCHAR(55) NOT NULL , 
	  UNIQUE KEY id (id, time)
	);";
#$table_name = 'test2'
$sql .= "CREATE  TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT , 
	  time bigint(11) DEFAULT '0' NOT NULL , 
	  name tinytext NOT NULL , 
	  text text NOT NULL , 
	  url VARCHAR(55) NOT NULL , 
	  UNIQUE KEY id (id, time)
	);";
#$table_name = 'test3'
$sql = "CREATE  TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT , 
	  time bigint(11) DEFAULT '0' NOT NULL , 
	  name tinytext NOT NULL , 
	  text text NOT NULL , 
	  url VARCHAR(55) NOT NULL , 
	  UNIQUE KEY id (id, time)
	);";
require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta($sql);

The above is similar to having one instruction per dbDelta such as this:

require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
#$table_name = 'test1'
$sql = "CREATE  TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT , 
	  time bigint(11) DEFAULT '0' NOT NULL , 
	  name tinytext NOT NULL , 
	  text text NOT NULL , 
	  url VARCHAR(55) NOT NULL , 
	  UNIQUE KEY id (id, time)
	);";
dbDelta($sql);
#$table_name = 'test2'
$sql = "CREATE  TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT , 
	  time bigint(11) DEFAULT '0' NOT NULL , 
	  name tinytext NOT NULL , 
	  text text NOT NULL , 
	  url VARCHAR(55) NOT NULL , 
	  UNIQUE KEY id (id, time)
	);";
dbDelta($sql);
#$table_name = 'test3'
$sql .= "CREATE  TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT , 
	  time bigint(11) DEFAULT '0' NOT NULL , 
	  name tinytext NOT NULL , 
	  text text NOT NULL , 
	  url VARCHAR(55) NOT NULL , 
	  UNIQUE KEY id (id, time)
	);";
dbDelta($sql);

Hence, you might want to practice the initial one to make your code run more efficient. Another interesting thing to take note is that every last instruction will not be required to have semicolon. Hence,

#$table_name = 'test2'
$sql .= "CREATE  TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT , 
	  time bigint(11) DEFAULT '0' NOT NULL , 
	  name tinytext NOT NULL , 
	  text text NOT NULL , 
	  url VARCHAR(55) NOT NULL , 
	  UNIQUE KEY id (id, time)
	);";
#$table_name = 'test3'
$sql = "CREATE  TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT , 
	  time bigint(11) DEFAULT '0' NOT NULL , 
	  name tinytext NOT NULL , 
	  text text NOT NULL , 
	  url VARCHAR(55) NOT NULL , 
	  UNIQUE KEY id (id, time)
	)";
require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta($sql);

will work. This means that if you have only 1 SQL instruction for dbDelta to run, you can safely remove the semicolon. But if you have many SQL instructions, only the last instruction can exclude semicolon since dbDelta function use semicolon as delimiter for splitting each instruction and remove the last array data if it is empty. Hence, leaving the last semicolon will reduce the step required to complete dbDelta function.

Conclusion

dbDelta in WordPress can be really useful. We just need to be careful not to make those silly mistakes that might just cost us our precious time debugging it. Although it can't remove fields for us, it definitely help us save a lot of time by adding in new fields!

Use PHP To Check Whether Remote URL, Email Or Image Link Exist

In PHP, we have a built-in function file_exist that can help us to verify whether a particular file exist in our directory. However, do you ever have the need to check whether a particular URL, email or image link exist? We can use regular express to validate that the syntax of an email is correct but won't it be nice to reduce the amount of spam mail received? How about those images you have on your site? Won't you want to check whether there is a broken image or url link? Well, i have! Its always good to be informed in advance than meeting dissatisfy visitors. Anyway, in this article you will get to find and learn some of the more efficient and quicker ways to verify whether a particular link exist to use on your web application.

Check Remote Image Link Exist

There are many ways to check whether a particular image link exist after the introduce of PHP 5, GetImageSize. GetImageSize allows us to take in a remote link to retrieve the size of the image. Hence, we can do a simple check such as the one shown below,

$external_link = 'http://www.example.com/example.jpg';
if (@GetImageSize($external_link)) {
echo  "image exists ";
} else {
echo  "image does not exist ";
}

The above work well for any server that had GD installed. But there are more problem than just the one mention. This method is actually inefficient as it will download the entire image into your server before checking it. Thus, making the process very long. There might also be security risk as mention on Secure File Upload Check List that many image format allow comment to be embedded within the image and these comment might just be some PHP code that hacker has written. In short, this method download file from remote server to your server and take the risk of hacker using it to run malicious code after it has been downloaded on your server. BOMB! So if you are using the above method i advice you to change it and if you insist to use this, you can provide more validation checking for it. Just drop it.

So how do we check Image link in a more secure and quick environment? If you are using curl, you can try the following script:

function checkRemoteFile($url)
{
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL,$url);
    // don't download content
    curl_setopt($ch, CURLOPT_NOBODY, 1);
    curl_setopt($ch, CURLOPT_FAILONERROR, 1);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
    if(curl_exec($ch)!==FALSE)
    {
        return true;
    }
    else
    {
        return false;
    }
}

Like i said, this will depend on curl. However, it is secure and quick! How about the rest of us? Lucky, PHP also provides another method called file_get_contents. file_get_contents returns the file in a string, starting at the specified offset up to maxlen bytes. On failure, file_get_contents() will return FALSE. With these in mind we can create a function to check that the file downloaded is valid by taking only 1 bytes of information from the file. Hence, whatever evil things exist on the file will only be able to run 1 byte and furthermore the function returns a string where code cannot be run. Thus, this will gives us a simple solution such as the one below,

function url_exists($url) {
if(@file_get_contents($url,0,NULL,0,1))
{return 1;}
else
{ return 0;}
}

The above one is more secure than the initial one that we have and it has no dependency. The speed for this method is also faster than the initial one. But is there a faster one than the curl version?

Check For Remote URL Link

There are many ways to check whether a remote url link exist. However, checking Remote URL required the page to return certain header code to indicate that the page is successfully loaded (200). Hence, you might not want to use the method for checking image link for url link. Furthermore, For different cases you might be interested with different solution. Assuming you are just checking whether an existing domain exist, you can use the following code

function url_exists($url){
    if(strstr($url,  "http:// ")) $url = str_replace( "http:// ",  " ", $url);
    $fp = @fsockopen($url, 80);
    if($fp === false) return false;
    return true;
}

which is written by adam at darkhousemedia dot com. The above method definitely run faster than fopen but for https and domain names but for path url such as 'http://example.com?p=231', the above won't work although the speed is definitely one of the fastest.

Nonetheless, there are still better alternative than the one presented. We can just tried to check the header with the following code:

function url_exists($url){
     if ((strpos($url,  "http ")) === false) $url =  "http:// " . $url;
     if (is_array(@get_headers($url)))
          return true;
     else
          return false;
}

The above work perfectly without the need to worry about complex code. However, the above method only work for HTTP and PHP 5 and above, other lower version of PHP will not. Therefore, we will need some modification on the above method to cater for lower PHP version.

function is_valid_url($url)
{
    $url = @parse_url($url);
    if (!$url)
    {
        return false;
    }
    $url = array_map('trim', $url);
    $url['port'] = (!isset($url['port'])) ? 80 : (int)$url['port'];
    $path = (isset($url['path'])) ? $url['path'] : '';
    if ($path == '')
    {
        $path = '/';
    }
    $path .= (isset($url['query'])) ?  "?$url[query] " : '';
    if (isset($url['host']) AND $url['host'] != gethostbyname($url['host']))
    {
        if (PHP_VERSION  >= 5)
        {
            $headers = get_headers( "$url[scheme]://$url[host]:$url[port]$path ");
        }
        else
        {
            $fp = fsockopen($url['host'], $url['port'], $errno, $errstr, 30);
            if (!$fp)
            {
                return false;
            }
            fputs($fp,  "HEAD $path HTTP/1.1\r\nHost: $url[host]\r\n\r\n ");
            $headers = fread($fp, 4096);
            fclose($fp);
        }
        $headers = (is_array($headers)) ? implode( "\n ", $headers) : $headers;
        return (bool)preg_match('#^HTTP/.*\s+[(200|301|302)]+\s#i', $headers);
    }
    return false;
}

The code here is the more detail version of the previous one which is created by SecondV on forums Dot digitalpoint Dot com. The above code cater for lower PHP version while still using the same approach of getting the return header value. Furthermore, it also validate the URL by using the parse_url method. f_open can also be used to check remote URL link.

function image_exist($url) {
	if (@fclose(@fopen( $url,  "r "))) {
	 // true;
	} else {
	 // false;
	}
}

However, this method required allow_url_fopen to be enabled on your php.ini file or else it will fail. Furthermore, i will not prefer this method over the previous one as it seems more sense that the page return success due to header code to indicate a success.

Check Email Exist

We can't actually check whether a given email exist or not as it really depend on how the SMTP is being setup for each respective mail server. Nonetheless, we are still able to check whether a given domain exist to reduce the number of invalid ones. PHP has a function checkdnsrr which does the work nicely.

function email_exist($email) {
	list($userid, $domain) = split( "@ ", $email);
	if (checkdnsrr($domain,  "MX ")) { return true;} else { return false;}
}

Using the function above will help us to verify whether a particular domain exist to trick user that you have an email checker if they really intend to fake one. However, Windows doesn't support such function yet. Hence, we will need to create such function just for Windows server.

if(!function_exists('checkdnsrr'))
function checkdnsrr($hostName, $recType = '')
{
 if(!empty($hostName)) {
   if( $recType == '' ) $recType =  "MX ";
   exec( "nslookup -type=$recType $hostName ", $result);
   // check each line to find the one that starts with the host
   // name. If it exists then the function succeeded.
   foreach ($result as $line) {
     if(eregi( "^$hostName ",$line)) {
       return true;
     }
   }
   // otherwise there was no mail handler for the domain
   return false;
 }
 return false;
}

Once you have cater for both Linux and Windows server, you may want to create a full flag function to check on email such as the one shown below:

function check_email($email)
{
	$email_error = false;
	$Email = htmlspecialchars(stripslashes(strip_tags(trim($email)))); //parse unnecessary characters to prevent exploits
	if ($Email ==  " ") { email_error = true; }
	elseif (!eregi( "^([a-zA-Z0-9._-])+@([a-zA-Z0-9._-])+\.([a-zA-Z0-9._-])([a-zA-Z0-9._-])+ ", $Email)) { email_error = true; }
	else {
	list($Email, $domain) = split( "@ ", $Email, 2);
		if (! checkdnsrr($domain,  "MX ")) { email_error = true; }
		else {
		$array = array($Email, $domain);
		$Email = implode( "@ ", $array);
		}
	} 
	if (email_error) { return false; } else{return true;}
}

Now we know why we need to verify our email after we sign up for any particular services online! Since we cannot check whether a particular email exist, we will force to send out verification email to our user before they are able to access our portal. However, if you are interested to check through PHP forcefully, you may want to visit webdigi. They create a mail class to verify an email through checking the port of mail SMTP 25 but like i said previously it really depend on how each mail server is being design. This might not work. But we can still force user to verify their email through a simple script shown below,

function check_email($email)
{
	$email_error = false;
	$Email = htmlspecialchars(stripslashes(strip_tags(trim($email)))); //parse unnecessary characters to prevent exploits
	if ($Email ==  " ") { email_error = true; }
	elseif (!eregi( "^([a-zA-Z0-9._-])+@([a-zA-Z0-9._-])+\.([a-zA-Z0-9._-])([a-zA-Z0-9._-])+ ", $Email)) { email_error = true; }
	else {
	list($Email, $domain) = split( "@ ", $Email, 2);
		if (! checkdnsrr($domain,  "MX ")) { email_error = true; }
		else {
		$array = array($Email, $domain);
		$Email = implode( "@ ", $array);
		}
	} 
	if (email_error) { return false; } else{return true;}
}
function EmailValidation($email) {
    if (check_email($email)) {
    $domain = explode(  "@ ", $email );
        if ( @fsockopen ($domain[1],80,$errno,$errstr,3)) {
			$code =  "here we place a secret key  with the email address: $email ";
			mail($email,  "Your Verification Code ",  "Please click the following URL to verify your email:\n\n  ". $_SERVER['PHP_SELF']. "/?v=$code amp;email=$email ", "From: \ "example\ "  <[email protected] > ");
			echo  "Your account needs to be verify. We have send you an email, click the link provided and you are verified. ";
			return true;
		} else {
            return false; //if a connection cannot be established return false
        }
    } else {
        return false; //if email address is an invalid format return false
    }
} 
function EmailForm(){
    if(empty($_POST['email'])){
        echo  " <form action= ".$_SERVER['PHP_SELF']. " method='post' >
         <table border='0' >
         <tr >
         <td >Email </td >
         <td > <input name='email' type='text' id='email' / > </td >
         </tr >
         <tr >
         <td > amp;nbsp; </td >
         <td > <input type='submit' name='Submit' value='Validate' / > </td >
         </tr >
         </table >
         </form > ";
    } elseif(isset($_POST['email'])) { 
        if(EmailValidation($_POST['email'])) {
            echo  "An email has been sent to you. Please follow the instructions to activate your account. ";
        } else {
            echo  "Your email address appears to be invalid. Please try again. ";
        }
    }else elseif(isset($_GET['v'])  amp; amp; isset($_GET['email'])) {
		$clean['emai'] = $_GET['email']; //need to filter these data to be clean
		$clean['v'] = $_GET['v']; //need to filter these data to be clean
		$code =  "here we place a secret key  with the email address: $email ";
		$code = md5($code);
		if ($clean['v'] != $code) {
			echo  "The Verification Code is invalid. Please Try Again. ";
			exit(0);
		}else
		echo  "The email  ".$clean['emai']. " has been verified ";
	}else { 
        echo  "An error has occured, please contact the administrator. "; 
    }
} 
EmailForm();

Might be a bit confusing but i believe you will get the above code since its quite simple. Instead of breaking them into different pages, i sum them up on a single one.

Quick check whether link is broken

Here is another tips to check whether a link is broken.

$file = 'http://www.domain.com/somefile.jpg';
$file_headers = @get_headers($file);
if($file_headers[0] == 'HTTP/1.1 404 Not Found') {
    $exists = false;
}
else {
    $exists = true;
}

And the same version on a curl version

function url_exists($url) {
    if (!$fp = curl_init($url)) return false;
    return true;
}

credit goes to havran @ http://www.php.net/manual/en/function.file-exists.php#74469

Summary

The above solution can help many people to verify the content that the user has entered. Remember it is not safe to trust user input and such verification can come in handle. On the other hand, this can also help us to check broken and invalid links so that we get the information we need from our users. The information above might not be solid but it is good enough for me and hoping it will work for you.