Advantages of stored procedures, triggers, indexes

A stored procedure is a set of SQL commands that can be compiled and stored in the server. Once this has been done, clients don’t need to keep re-issuing the entire query but can refer to the stored procedure. This provides better overall performance because the query has to be parsed only once, and less information needs to be sent between the server and the client. You can also raise the conceptual level by having libraries of functions in the server. However, stored procedures of course do increase the load on the database server system, as more of the work is done on the server side and less on the client (application) side. Triggers will also be implemented.

A trigger is effectively a type of stored procedure, one that is invoked when a particular event occurs. For example, you can install a stored procedure that is triggered each time a record is deleted from a transaction table and that stored procedure automatically deletes the corresponding customer from a customer table when all his transactions are deleted.

Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. If a table has 1,000 rows, this is at least 100 times faster than reading sequentially. If you need to access most of the rows, it is faster to read sequentially, because this minimizes disk seeks.

Get image src using JavaScript

The very simple method in javascript to swap images of named imag tags

For example,

<img name=”image1″ src=”image.jpg” />
<script>document["image1"].src=”image1.jpg”;</script>

So If I name a Div tag that wraps the image can I use that to specify the image tag in question?

Something like..

<div id=”namedDiv”><img src=”image.jpg” /></div>

The answer is possible, every dom node have childNodes property which is an array of nodes. You can pick first one.

<script>
var imgDiv=document.getElementById['namedDiv'];
imgDiv.childNodes[0].src=”image1.jpg”;
</script>

Depending on the markup and the browser, the <img> element may not be the only child. For example, if there is any whitespace such as a line break then many browsers other than IE will create a text node.

The easiest way is to use the getElementsByTagName method of the wrapper element, which returns a NodeList, and get the first element in that NodeList:

<script>
var div = document.getElementById(“namedDiv”);
var image = div.getElementsByTagName(“img”)[0];
image.src = “image1.jpg”;
</script>

You can shorten that if you don’t mind making it slightly harder to follow:

<script>
document.getElementById(‘namedDiv’).getElementsByTagName(‘img’)[0].src = ‘image1.jpg’;
</script>

but that makes it a bit harder to debug when you make a mistake ;-)

I hope this will be useful to you. Please post your comments here

PHP Form Validation functions

Recently i have involve myself in another application development. Regular Hungred Dot Com visitors will notice that the site currently offer advertisement space through this form. But really, we as a developers are always looking for such snippets or writing them out from scratch every single time regardless of how many time we know we have store it somewhere in our laptop! Man, its really frustrating searching on Google and find all sort of solution and trying to figure out whether the regular expression implemented is expensive or complete. So i came out with an idea to ease my life a bit and other developers by putting up an article such as this for my/our references. (This can be made into a class if you like to)

Validate Email

We can perform an email validation through this function.

	function isValidEmail($email){
		return eregi('^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$', $email);
	}

PHP 5.2 and above.

function fnValidateEmail($email)
{
  return filter_var($email, FILTER_VALIDATE_EMAIL);
}

Sanitize Email

We can further sanitize our email to ensure that everything is alright.

function fnSanitizeEmaill($string) {
     return  preg_replace( '((?:\n|\r|\t|%0A|%0D|%08|%09)+)i' , '', $string );
}

PHP 5.2 and above.

function fnSanitizeEmaill($url)
{
  return filter_var($url, FILTER_SANITIZE_EMAIL);
}

Validate Email Exist

This is not possible but certain validation can be use to validate email existence.

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;}
}

Validate Number Only

We can use PHP built-in function to validate whether a given value is a number.

function fnValidateNumber($value)
{
	#is_ double($value);
	#is_ float($value);
	#is_ int($value);
	#is_ integer($value);
	return is_numeric($value);
}

PHP 5.2 and above.

function fnValidateNumber($value)
{
	#return filter_var($value, FILTER_VALIDATE_FLOAT); // float
	return filter_var($value, FILTER_VALIDATE_INT); # int
}

Sanitize Number

We can force all value to be only numeric by sanitize them.

function fnSanitizeNumber($str)
{
	#letters and space only
	return preg_match('/[^0-9]/', '', $str);
}

PHP 5.2 and above.

function fnSanitizeNumber($value)
{
	#return filter_var($value, FILTER_SANITIZE_NUMBER_FLOAT); // float
	return filter_var($value, FILTER_SANITIZE_NUMBER_INT); # int
}

Validate String Only

Sometimes to validate name we can use this function to restrict only letters and spaces.

function fnSanitizeStringr($str)
{
	#letters and space only
	return preg_match('/[^A-Za-z\s ]/', '', $str);
}

Sanitize String

We can sanitize it instead of validate user input.

function fnSanitizeStringr($str)
{
	#letters and space only
	return preg_replace('/[^A-Za-z\s ]/', '', $str);
}

PHP 5.2 and above. built-in function by PHP provides a much more powerful sanitize capability.

function fnSanitizeStringr($str)
{
	return filter_var($str, FILTER_SANITIZE_STRIPPED); # only 'String' is allowed eg. '

HELLO
' => 'HELLO'
}

Validate Alphanumeric Characters

This validates alphanumeric characters.

function fnValidateAlphanumeric($string)
{
	return preg_match('/[^a-zA-Z0-9\s]/', '', $string);
}

Sanitize Alphanumeric Characters

This sanitize alphanumeric characters. eg. “HELLO! Do we have 90 idiots running around here?” => “HELLO Do we have 90 idiots running around here”

function fnSanitizeAlphanumeric($string)
{
	return preg_replace('/[^a-zA-Z0-9\s]/', '', $string);
}

Validate URL Exist

This function will check whether a given URL exist and not only validate it.

	function url_exist($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;
	}

Validate URL Format

This function will validate a given url to ensure the format is correct.

function fnValidateUrl($url){
return preg_match('/^(http(s?):\/\/|ftp:\/\/{1})((\w+\.){1,})\w{2,}$/i', $url);
}

PHP 5.2 and above.

function fnValidateUrl($url)
{
  return filter_var($url, FILTER_VALIDATE_URL);
}

Sanitize URL

PHP 5.2 and above.

function fnSanitizeUrl($url)
{
  return filter_var($url, FILTER_SANITIZE_URL);
}

Validate Image Exist

This function will check whether a given image link exist and not only validate it.

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

Validate IP Address

This function will validate an IP address.

function fnValidateIP($IP){
	return preg_match('/^(([1-9]?[0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5]).){3}([1-9]?[0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])$/',$IP)
}

PHP 5 and above. This can also specific validation for IPV4 or IPV6.

function fnValidateIP($ip)
{
  return filter_var($ip, FILTER_VALIDATE_IP);
}

Validate Proxy

This function will let us detect proxy visitors even those that are behind anonymous proxy.

function fnValidateProxy(){
	if ($_SERVER['HTTP_X_FORWARDED_FOR']
	   || $_SERVER['HTTP_X_FORWARDED']
	   || $_SERVER['HTTP_FORWARDED_FOR']
	   || $_SERVER['HTTP_VIA']
	   || in_array($_SERVER['REMOTE_PORT'], array(8080,80,6588,8000,3128,553,554))
	   || @fsockopen($_SERVER['REMOTE_ADDR'], 80, $errno, $errstr, 30))
	{
		exit('Proxy detected');
	}
}

Validate Username

Before we validate whether a given username is matches the one in our database, we can perform a validation check first to prevent any unnecessary SQL call.

function fnValidateUsername($username){
	#alphabet, digit, @, _ and . are allow. Minimum 6 character. Maximum 50 characters (email address may be more)
	return preg_match('/^[a-zA-Z\d_@.]{6,50}$/i', $username);
}

Validate Strong Password

Another good thing is to validate whether a particular password given by the user is strong enough. You can do that using this function which required the password to have a minimum of 8 characters, at least 1 uppercase, 1 lowercase and 1 number.

function fnValidatePassword($password){
	#must contain 8 characters, 1 uppercase, 1 lowercase and 1 number
	return preg_match('/^(?=^.{8,}$)((?=.*[A-Za-z0-9])(?=.*[A-Z])(?=.*[a-z]))^.*$/', $password);
}

Validate US Phone Number

This function will validate US phone number for US users.

function fnValidateUSPhone($phoneNo){
	return preg_match('/\(?\d{3}\)?[-\s.]?\d{3}[-\s.]\d{4}/x', $phoneNo);
}

Validate US Postal Code

This function validate US postal code.

function fnValidateUSPostal($postalcode){
	#eg. 92345-3214
	return preg_match('/^([0-9]{5})(-[0-9]{4})?$/i',$postalcode);
}

Validate US Social Security Numbers

This function validate US Social Security Numbers.

function fnValidateUSSocialSecurityCode($ssb){
	#eg. 531-63-5334
	return preg_match('/^[\d]{3}-[\d]{2}-[\d]{4}$/',$ssn);
}

Validate Credit Card

This function validate credit card format.

function fnValidateCreditCard($cc){
	#eg. 718486746312031
	return preg_match('/^(?:4[0-9]{12}(?:[0-9]{3})?|5[1-5][0-9]{14}|6011[0-9]{12}|3(?:0[0-5]|[68][0-9])[0-9]{11}|3[47][0-9]{13})$/', $cc);
}

Validate Date

This is a date format MM-DD-YYYY or MM-DD-YY validation which validate from year 0000-9999.

function fnValidateDate($date){
	#05/12/2109
	#05-12-0009
	#05.12.9909
	#05.12.99
	return preg_match('/^((0?[1-9]|1[012])[- /.](0?[1-9]|[12][0-9]|3[01])[- /.][0-9]?[0-9]?[0-9]{2})*$/', $date);
}

This is a date format YYYY-DD-MM or YY-MM-DD validation which validate from year 0000-9999.

function fnValidateDate($date){
	#2009/12/11
	#2009-12-11
	#2009.12.11
	#09.12.11
	return preg_match('#^([0-9]?[0-9]?[0-9]{2}[- /.](0?[1-9]|1[012])[- /.](0?[1-9]|[12][0-9]|3[01]))*$#'', $date);
}

Validate Hexadecimal Colors

This is a good validation for people who allows their user to change color in their system.

function fnValidateColor($color){
	#CCC
	#CCCCC
	#FFFFF
	return preg_match('/^#(?:(?:[a-f0-9]{3}){1,2})$/i', $color);
}

Make Query Safe

This function help sanitize our data to be SQL injection safe.

function _clean($str){
return is_array($str) ? array_map('_clean', $str) : str_replace('\\', '\\\\', htmlspecialchars((get_magic_quotes_gpc() ? stripslashes($str) : $str), ENT_QUOTES));
}

//usage call it somewhere in beginning of your script
_clean($_POST);
_clean($_GET);
_clean($_REQUEST);// and so on..

Make Data Safe

This function help to keep us protected against XSS, JS and SQL injection by removing tags.

function _clean($str){
return is_array($str) ? array_map('_clean', $str) : str_replace('\\', '\\\\', strip_tags(trim(htmlspecialchars((get_magic_quotes_gpc() ? stripslashes($str) : $str), ENT_QUOTES))));
}

//usage call it somewhere in beginning of your script
_clean($_POST);
_clean($_GET);
_clean($_REQUEST);// and so on..

Summary

A paranoid way to perform a form validation would be to validate first then sanitize your values for precautions. If you think the above snippets were suck or you have any good or awesome snippets to share. Please throw your comment and share with us!

Author: Clay Lua

What’s difference between display: none and visibility: hidden?

just when you tread into the world of dhtml or javascript, you can require the display property of CSS more than expected. So it becomes necessary to clearly understand some of the facts behind this powerful language. Here is the difference between the visibility and display property of an element.

visibility: hidden hides the element, but it still takes up space in the layout.

display: none removes the element completely from the document. It does not take up any space, even though the HTML for it is still in the source code.

Author: Realin !

Optimising MYSQL

Hello,

I’d like to share with the Web Collections my knowledge about tuning / tweaking mysql.

First of all, sorry if my English is not perfect. Maybe some sentences may be difficult to understand. So do not hesitate to ask me for explanations :-p

Secondly,
DO NOT CHANGE your setting without understanding what you do.
Make a backup of your my.cnf before editing the /etc/my.cnf
DO THESE CHANGES AT YOUR OWN RISKS.

My article is only to help you to more well understand how tuning mysql.

Optimising mysql is very well commented on the net, and you’ll find huge information on how to do this. There is never “best parameters”, the best parameters is those fits your needs, box hardware, mysql usage…
So, I’ll not give the best parameters but rather how to define these ones. Make some tests, and you’ll quickly find your own parameters.

I’ll give you at the end of this post some web pointers which may help you.

There a lot of available parameters but only few one are very important to tweak your mysql box.

The most important variables are (for me, and it is not exhaustive)

- max_connections
- wait_timeout
- thread_cache_size
- table_cache
- key_buffer_size
- query_cache_size
- tmp_table_size

First of all, how to find your variable, and the mysql usage ?

*VARIABLES

from mysql :
show variables;

or from command line :
mysqladmin variables

*PROCESS / STATUS

from Mysql :
show status;

or from command line
mysqladmin –i10 processlist extended-status

*SOME USEFUL COMMAND FOR YOU BOX USAGE

>Top

>ps –axfu

>vmstat 1

* OPTIMISING MYSQL

To obtain the stat of your mysql server since it has been loaded, run mysqladmin processlist extended-status as mentionned above.

1 – The two most important variables : Table_cache and Key_buffer_size

* If Opened_tables is big, then your table_cache variable is probably too small.
table_cache 64
Open_tables 64
Opened_tables 544468

This is the first serious problem. “The table_cache is the number of open tables for all threads. MySQL, being multi-threaded, may be running many queries on the table at one time, and each of these will open a table.” Therefore, even though we only have a few tables, we will need many more open_tables.

The Opened_tables value is high and shows the number of cache misses. Getting the table_cache size correct is one of the two best things you can do to improve performance.

* If Key_reads is big, then your key_buffer_size variable is probably too small. The cache hit rate can be calculated with Key_reads/Key_read_requests.
key_buffer_size 16M
Key_read_requests 2973620399
Key_reads 8490571
(cache hit rate = 0.0028)

“The key_buffer_size affects the size of the index buffers and the speed of index handling, particularly reading.” The MySQL manual (and other sources) say that “Key_reads/Key_read_request ratio should normally be < 0.01.” This is the other most important thing to get correct. Here the value seems to be correct (< 0.01)

Also check key_write_requests and key_writes.  The key_writes/key_writes_request should normally be < 1 (near 0.5 seems to be fine)

Here is a very interesting web pointer : http://www.databasejournal.com/featu…0897_1402311_3

2 – Others important settings are : Wait_timeout, max_connexion, thread_cache

A little explanation :
Generaly you have a lot of mysql process that are sleeping because wait_timeout are not set low. So I make sure that the wait_timeout is set to a very low value: 15 seconds (for me) . That means MySQL would close any connection that was idle for more than 15 seconds.

The problem is you also have to increment your max_connexion (mine is set to 300) to be sure there is not a lot of idle clients holding connections and blocking out new clients from connecting and getting real work done. The pbm is that the box has to create new threads (MySQL is a multi-threaded server) at a very high rate. That may sucks up a measurable amount of CPU time.

So the solution is to use the Thread_cache (from mysql doc) :
“How many threads we should keep in a cache for reuse. When a client disconnects, the client’s threads are put in the cache if there aren’t more than thread_cache_size threads from before. All new threads are first taken from the cache, and only when the cache is empty is a new thread created. This variable can be increased to improve performance if you have a lot of new connections. (Normally this doesn’t give a notable performance improvement if you have a good thread implementation.) By examing the difference between the Connections and Threads_created you can see how efficient the current thread cache is for you.”

* If Threads_created is big, you may want to increase the thread_cache_size variable. The cache hit rate can be calculated with Threads_created/Connections.
thread_cache_size 0
Threads_created 150022
Connections 150023

This is the second problem that should be fixed. A cache size of zero is the default for my-medium.cnf but the recommended size in my-large.cnf is 8.

you may try this formula : table_cache = opened table / max_used_connection

3 – Finally, you may also have a look at : tmp_table_size and Handler_read_rnd / Handler_read_rnd_next

* If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size variable to get the temporary tables memory-based instead
of disk based.

tmp_table_size 32M
Created_tmp_disk_tables 3227
Created_tmp_tables 159832
Created_tmp_files 4444

Created_tmp_disk_tables are the “number of implicit temporary tables on disk created while executing statements” and Created_tmp_tables are
memory-based. Obviously it is bad if you have to go to disk instead of memory. About 2% of temp tables go to disk, which doesn’t seem too bad
but increasing the tmp_table_size probably couldn’t hurt either.

* If Handler_read_rnd is big, then you probably have a lot of queries that require MySQL to scan whole tables or you have joins that don’t use
keys properly.

Handler_read_rnd 27712353
Handler_read_rnd_next 283536234

These values are high, that we could probably stand to improve the indexes and queries.

I hope this will help some of you to more understand how it is possible to optimise MYSQL to fit your needs, hardaware box, or mysql current usage.

Maybe there is others tweaks to perform, but I know well only these ones. I did setup using these ones on differents mysql box, and generally it did help us to increase performance without have to change hardware (our boxes have 2GB ram)

I forgot to tell you two or three importants things like :

Used MySQL memory = key_buffer + max_connections * (join_buffer + record_buffer + sort_buffer + thread_stack + tmp_table_size)

Notice the max_connexion and the multiplier. connexion increase = memory usage increase too.

Notice key_buffer for a given memory : more you add mem to key buffer, less connexion is less is key buffer, more connexion is

If you change one of these settings for a high value, you system may swap. If you system swap, try lot decrease these values

Also, about table_cache :
Increasing the size of the table cache may really help you. But you must be careful not to make the value too large. All operating systems have a limit on the number “open file pointer” (sorry in french it is called pointer, maybe descriptors is the good translation) a single process may have.
If MySQL tries to open a lot of files, the OS may refuse it and MySQL will generate error message in the error log.

Author: Pascal

« Older entries