div#bodyRight { margin: 5px 25px 25px 225px; width: 75%; }

Your two column webpage will look like

<div id="topBanner">Page Title</div>

<div id="bodyLeft">Add menu items here</div>

<div id="bodyRight">Add your content here</div>

</body>

Three column webpage with CSS

Create a file like threecol.css. Put the following code

div#bodyLeft { position: absolute; padding-left: 10px; width: 25%; }

div#bodyCenter { margin-left: 190px; margin-right: 190px; width: 50%; position: absolute; }

div#bodyRight { position: absolute; padding-right: 10px; width: 25%; right: 0%; }

div#topBanner { text-align: center; padding-bottom: 15px; }

Your three column webpage will look as follows

<div id="topBanner">Page Title</div>

<div id="bodyLeft">Add menu items here</div>

<div id="bodyCenter">Add your content here</div>

<div id="bodyRight">Add your content here</div>

Then, add your text and/or graphics to the file.

Table Less Design: Layouts with CSS

A three column webpage with CSS positioning

    
        <div id="leftnavigation"></div>
        <div id="rightnavigation"></div>
        <div id="content"></div>

The CSS should look like this:


#leftnavigation 
{
  position : absolute;
  left : 0;
  width : 150px;
  margin-left : 10px;
  margin-top : 20px;
  color : #000000;
  padding : 3px;
}

#rightnavigation 
{
  position : absolute;
  left : 80%;
  top : 20px;
  width : 140px;
  padding-left : 10px;
  z-index : 3;
  color : #000000;
  padding : 3px;
} 

#content {
  top : 0px;
  margin : 0px 25% 0 165px;
  padding : 3px;
  color : #000000;
} 


----------


More CSS Layouts:


Three Column layout with header and footer:

The CSS


html, body { margin: 0; padding: 0; }

#header { 

  width: 800px;

  float: left;

}

#maincontainer {

  width: 800px;

  float: left;

}

#nav {

  width: 200px;

  float: left;

}

#main {

  float: right;

  width: 600px;

}

#footer {

  width: 800px; 

  float: left;

}
 



The XHTML



<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />

<title>2 Column Header and Footer Layout</title>

<link href="styles.css" rel="stylesheet" type="text/css" />

</head>



<body>

<div id="header"><h1>Header</h1></div>

<div id="maincontainer">

<div id="main">

<p>Whee</p>

<p>

</div>

<div id="nav">

<ul>

	<li><a href="">Link</a></li>

	<li><a href="">Link</a></li>

	<li><a href="">Link</a></li>

	<li><a href="">Link</a></li>

</ul>

</div>

</div>

<div id="footer"><h4>Footer</h4></div>

</body>

</html>


Web page design standards




Try to follow the following rules while creating a web-page. 


1. Decide what you want to use HTML or XHTML. Better to go 

with XHTML. Which XHTML? Strict or transitional?  Go for 

strict one, that way you will be in the most standard way.



2. If you are new into XHTML try transitional first, when you are 

comfortable in Transitional style, switch to Strict.

 Rememebr, strict will not support many non-standard tags
 
and coding you usually use, so it may sound a bit difficult.



3. Some XHTML rules


a. Simply use a Doctype.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 
Transitional//EN"	"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
b. Use lower case for all tags

c. enclose all attribute values

d. close all tags even <hr/> <br/>

e. Try to use css for layout

f. Even assign values to empty attributes like checked='checked', readonly='true'

g. nest attributes correctly



4. Page Layout


Most recent and most standard way is to use pure css (div,span tags) to create page layouts

However, not all browsers are that advanced. With css layout horizontal alignment is problemetic. 

Also, placing page footers in the right place becomes difficult.



A good way is, try css first. If you face problems use tables for the outer layout and for internal layout (under the table) never use tables just use css(div). 
Use table internally only to display tabular data not for layout.




5. Always use both id and name tags. use id tag before name tag. 


6. For javascript use Use the Document.getElementById DOM that is compatible in all browsers


7. Always use attributes or set properties in div tag not in the td tag when you use div inside td. But use valign on the td, it does not work with div




Page layout with minimal use of table



<table>
	<tr>
		<td colspan="2">
			<div class="header">
			Header
			</div>
		</td>
	</tr>
	<tr>
		<td>
			<div class="navigation">
			Navigation
			</div>
		</td>
		<td>
			<div class="content">
			Content
			</div>
		</td>
	</tr>
	<tr>
		<td colspan="2" style="vertical-align: bottom">
			<div class="footer">
			Footer
			</div>
		</td>
	</tr>
</table>



8. <center> is deprecated and centering a block in css is difficult. So rather use the following css






div.container {

text-align: center;

}



div.text {

margin-left: auto;

margin-right: auto;

text-align: left; /* overrule inheritance */


}











What you really need to learn in Javascript?


What you really need to learn in Javascript? 


1. Where you can place JavaScript codes? 


2. Some basic programming: 


<script language="javascript" type="text/javascript">

</script> 



<script language="javascript" type="text/javascript"> 

	document.write('<b>Hello World</b>'); 

</script> 


This will only be displayed in javascript enabled browsers.


<script language="javascript" type="text/javascript"> 

	document.write('<a href="script.htm">Page Requiring Javascript</a>'); 

</script> 


3. Variables and their scopes 


<script language="javascript" type="text/javascript"> 

	var hello = 'Hello World'; 

	document.write(hello);

</script> 



var mynum = 5; 


var smokes = false; 



var riches = null; 


var today = new Date;
 

Example use of variables: 


<script language="javascript" type="text/javascript"> 

	var questions = '<p>If you have any questions about this 

 	please <a href="mailto:me@myaddress.com">email me</a>.</p>'; 

	document.write(questions);

</script> 


Another block can refer to questions variables without reassigning the value


<script language="javascript" type="text/javascript"> 

	document.write(questions);

</script> 



variables declared within a function is recognized only withing that function. Variables declared outside of a 
function is recognized anywhere in the webpage within javascript code from the declaration place. 



4. Operators 


Assignment operators 


<script language="javascript" type="text/javascript"> 

	var rich = 5000; 

	var lotsOfMoney = 100000; 

	rich = lotsOfMoney; 

	document.write(rich);

</script> 


Arithmetic and concatenation operators 


five = two + three; 

profit = income - expenses; 

income = sales * price;

payment = total / instalments; 

option = randnum % choices; 

b = ++a;

c = a++;

d = --a;

e = a--; 



Combination of Operators like c/c++ 


joy += happiness; 

price -= discount; 

capital *= interest; 

pie /= slices;

options %= choice; 


Example use:



<script language="javascript" type="text/javascript"> 

	var singlePrice = 8;

	var bulkPrice = singlePrice * 9;

	document.write('<p>Buy our Widgets $'

	+singlePrice+' for one, $'+bulkPrice+' for ten</p>'); 

</script> 




5. Comparing Variables, Logical statements 


<script language="javascript" type="text/javascript"> 

	var red = 5;

	var blue = 3;

	var match = null;

	if (red == blue) 

	{

		match = 'equal'; 

	} 

	else 

	{

		match = 'unequal'; 

	}

	document.write(red + ' and ' + blue + ' are ' + match); 

</script> 



Other comparison operators: 


if (red > blue)

if (red >= blue)

if (red < blue)

if (red <= blue)

if (red != blue) 




Combining more than one comparison 


if ((red == blue) || (red == green)) 



<script language="javascript" type="text/javascript"> 

	var red = 5;

	var blue = 3;

	var green = 3;

	var match = null;

	
	if ((red == blue) && (red == green)) 

	{

		match = 'equal';

	} 

	else 

	{

		purple = 'unequal';

	}

	document.write(red + ' and ' + blue + ' are ' + match);

</script> 



Comparison in short


red == blue ? match = 'equal' : match = 'unequal'; 



instead of


if (red == blue) 

{

	match = 'equal';

} 

else 

{

      match = 'unequal'; 

}



Example Use:



<script language="javascript" type="text/javascript">

	var discPrice = 25;

	var regPrice = 25;

	var discount = regPrice - discPrice;

	if (discount > 0)

		document.write('<p>Save $'+discount+ ' off the normal price of $' +regPrice+ 'now only $'+discPrice+'.</p>');

	else

		document.write('<p>Buy now at our regular cheap price of $' + regPrice+'.</p>' ); 

</script>



6. Switch statement in Javascript, very similar to C/C++/Java


use switch instead of multiple if/else if


<script language="javascript" type="text/javascript">

	var red = 1;

	var result = null;

	switch (red) 

	{

		case 1: result = 'one'; break; 

		case 2: result = 'two'; break;

		default: result = 'unknown';

	}

	document.write(result);

</script> 



Example:


<script language="javascript" type="text/javascript"> 

	var message = 0;

	switch (message) 

	{

		case 1: document.write('Merry Christmas'); break;

		case 2: document.write('Happy New Year'); break; 

		case 3: document.write('Happy Easter'); break;

		case 4: document.write('Happy Holidays'); break;

		default: document.write('Welcome');

	}

</script> 



7. Function 

	
	
Defining a function 


function myCode() 

{

	document.write('<b>Hello World</b>'); 

} 


calling a function


myCode()


Example:


function displayMessage() 

{

	switch (message) 

	{

		case 1: document.write('Merry Christmas'); break;

		case 2: document.write('Happy New Year'); break;

		case 3: document.write('Happy Easter'); break;

		case 4: document.write('Happy Holidays'); break;

		default: document.write('Welcome');

	}

}	 



var message = 0;

displayMessage(); 



parameter passing


function writeSentence(argument1,argument2) 

{

	document.write('The '+argument1+' is '+argument2+'.<br />'); 

} 




var a = 'table';

var b = 'chair';

var c = 'red';

var d = 'blue';

writeSentence(a,c);

writeSentence(b,c);

b = 'other ' + b;

writeSentence(b,d); 

writeSentence('table',b); //passing the value directly


Example:


function displayMessage(m) 

{

	switch (m) 

	{

		case 1: document.write('Merry Christmas'); break;

		case 2: document.write('Happy New Year'); break;

		case 3: document.write('Happy Easter'); break;

		case 4: document.write('Happy Holidays'); break;

		default: document.write('Welcome');

	}

} 



In Javascript functions can also return values 


function validField(fld) 

{

	if (fld == '') return false; 

	return true;

} 



function validField(fld) 

{

	return (fld != '');

} 


How to receive returned values and process 

document.write(myField + ' is ');

if (!validField(myField)) 

{

	document.write('not '); 

}

document.write('empty'); 


8. Alert and confirm 


	
alert('Alert Message');  


Will display a message box with the message. Very useful in debugging javascript applications. 


use confirm(), when you need user agreement on an issue. like: 


if (confirm('Select a button'))

{

	alert('You selected OK'); 

}

else 

{

	alert('You selected Cancel'); 

}



9. comments 



// Scrolling Ad Javascript

// copyright 3rd September 2004, by Stephen Chapman 

// permission to use this Javascript on your web page is 

// granted provided that all of the code in this script (including 

// these comments) is used without any alteration 



or



/* Scrolling Ad Javascript

copyright 3rd September 2004, by Stephen Chapman

permission to use this Javascript on your web page is

granted provided that all of the code in this script (including

these comments) is used without any alteration */ 




10. Debugging JavaScript


    Test in different browsers like IE, Mozilla, Firfox, Netscape 

    Enable Javascript and script debugging

    Script debugging usually reside under tools menu under browsing or web development sub-options


    
    
    Using alert to check variable values or if you can reach to a particular point of your code

    
    use bookmarklets, these are small scripts that can be used as plug in into browsers to provide error information.

    
    
    Use firebug in firefox, also use error console under tools menu to debug javascript error.

    
    Visual interdev provides Javascript debugging you may also enable external debugging by such programs

    
    
11. External javascript



You can place all of your javascript codes to an external file. and use the file scripts/functions from any webpage. 

You just need to provide a reference to that external file.


You can provide reference as follows:


<script language="javascript" type="text/javascript" 

	src="hello.js">

</script> 


Note:  do not include any <script> or </script> in the external file. 



12.  Using <noscript> tag:  this tag may help you to provide some information to the visitors  

when javascript is disabled or not supported by the browsers.



<script language="javascript" type="text/javascript">

	document.write('<b>Hello Javascript World</b>'); 

</script>


<noscript>Hello World Without Javascript</noscript> 


<noscript>

	This page uses Javascript. Your browser either

	doesn't support Javascript or you have it turned off.

	To see this page as it is meant to appear please use

	a Javascript enabled browser.

</noscript>



13. Objects and properties in Javascript 


var strlen = myField.length; 

var str = mynum.toString(); 


function theLetter(num) 

{

	var str = 'abcdefghijklmnopqustuvwxyz';

	return str.substr(num-1,1);

}


document.write(theLetter(5)); 



14. Arrays in Javascript 


var myArray = new Array(); 


var myArray = new Array('message one',

'message two','message three'); 

document.write(myArray[0]); 

myArray[3] = 'message four'; 



function displayMessage(m) 

{

	var greeting = new Array('Welcome','Merry Christmas',

	'Happy New Year','Happy Easter','Happy Holidays');

	if (m < 0 || m > greeting.length) m = 0;

	document.write(greeting[m]); 


} 


15. Loops


for (var i=0; i<10; i++) 

{

	document.write(i);

} 



var x = 0;

while (x<10) 

{

	document.write(x); 

	x++;

} 


var x = 12;

do 

{

	document.write(x); 

	x++;

} while (x<10) 



16. Date and Time in Javascript


//current date

var myDate = new Date; 



myDate.setDate(15);

myDate.setMonth(3); // January = 0

myDate.setFullYear(2006); 


myDate.setDate(myDate.getDate()+7); 




How to implement multiple tab webpages in Ajax and Javascript


The following two examples will demonstrate how to use javascript and/or ajax to implement multiple tab webpages.

http://www.dynamicdrive.com/dynamicindex17/ajaxtabscontent/ 



http://www.barelyfitz.com/projects/tabber/example2.html



I hope to be familiar with these concepts and use them in my work soon.



Handling Checkbox arrays with Javascript::GetElementsByName:XHTML supported


The following code demonstrates how to handle checkboxes with javascript. Note the names of all check boxes are same myInput[]. It could also be myInput. Both works as an array to contain the controls' values. I prefer myInput[] as you can handle this control in PHP as an array. If you use $arr=$_POST['myInput'], $arr will contain the values of the controls.  


Note the use of getElementsByName(). It is easy to manipulate javascript controls using this name. You do not need to send a value/control to the function. And in many times, document.myform.control, does not work right, specially if you want to main XHTML standard[i need to check more on this]. getElementById is also very useful. You may use getElementById for most times, but with checkbox arrays getElementsByName is better choice. 



Also, note that when a form is submitted only the values of the checked check boxes are submitted to the server. You may wish to use hidden controls to keep the values, so that all values are submitted and  based on the checked/non-checked you can ..control your work 



<html>
	<head>
		<script type="text/javascript">
			function getElements()
			  {
			  var x=document.getElementsByName("myInput[]");
			  alert(x.length);
			  alert(x[0].value);
			  alert(x[1].value);
			  alert(x[2].value);


			  }
		</script>
</head>

<body>
	<input name="myInput[]" type="checkbox" size="20" value='10'/>10 

	<input name="myInput[]" type="checkbox" size="20" value='20' />20

	<input name="myInput[]" type="checkbox" size="20" value='30' />30

	





Want to check how the code works, check below 







10

20

30







Javascript by Example


Java Script by Example:


Please check the URL:


http://examples.oreilly.com/jscript2/

Javascript - Form validation



The code will be helpful in validating data entry forms such as: user registration, user creation. validate_required is used by all other functions


function validate_required(field,alerttxt)
	{
		with (field)
		{
			if (value==null||value=='')
			{
				alert(alerttxt);return false
			}
			else {return true}
		}
	}
	
	function validateTicketCreateForm(thisform)
	{
		with (thisform)
		{
			if (validate_required(type,'Type must be filled out!')==false)
			{type.focus();return false}
			
			if (validate_required(subject,'Subject must be filled out!')==false)
			{subject.focus();return false}
						
		}
	}

	function validate_customer_create_form(thisform)
	{
		with (thisform)
		{
			if (validate_required(firstName,'First name must be filled out!')==false)
			{firstName.focus();return false}
			
			if (validate_required(lastName,'Last name must be filled out!')==false)
			{lastName.focus();return false}
			
			if (validate_required(phone,'Phone must be filled out!')==false)
			{phone.focus();return false}
			
		}
	}
	
	
	function validateUserCreateForm(thisform)
	{
		with (thisform)
		{
						
			if (validate_required(firstName,'First name must be filled out!')==false)
			{firstName.focus();return false}
			
			if (validate_required(lastName,'Last name must be filled out!')==false)
			{lastName.focus();return false}
			
			if (validate_required(shortName,'Short name must be filled out!')==false)
			{shortName.focus();return false}
			
			if (validate_required(username,'Username must be filled out!')==false)
			{username.focus();return false}
			
			
			if (validate_required(password,'Password must be filled out!')==false)
			{password.focus();return false}
			
			if (password.value.length < passwordLength.value) {alert("Password must be at least "+ passwordLength.value +" char long");	password.focus();return false}
			
			if (validate_required(password2,'Please re-enter password!')==false)
			{password2.focus();return false}
			
			if (password.value != password2.value)
			{
				alert("Password did not match");
				password2.focus();
				return false			
			}
			
			
		}
	}



JavaScript - Miscellaneous Validations



	function validateDomainName(strValue) 
	{
		return /www\.[a-z0-9_\-]+\.[a-z]{2, 3}/ig.test(strValue);		
	}

	function validateDomainName(strValue) 
	{
		return /www\.[a-z0-9_\-]+\.[a-z]{2, 3}/ig.test(strValue);		
	}


	//not 100% right, will fix later
	function checkDateFormat()
	{
		var pattern = new RegExp(0|1[0-9]/[0-3][0-9]/[0-9][0-9]);
		if(document.getElementById('timestampStarted').value.match(pattern))
		{
			var date_array = document.getElementById('timestampStarted').value.split('/');
			
			var month = date_array[0];
			var day = date_array[1];
			var year = date_array[2];

			// This instruction will create a date object
			source_date = new Date(year,month,day);
	
			if(month != source_date.getMonth())
			{
				alert('Month is not valid!');
				return false;
			}
	
			if(day != source_date.getDate())
			{
				alert('Day is not valid!');
				return false;
			}
			
			if(year != source_date.getYear())
			{
				alert('Year is not valid!');
				return false;
			}
		}
		else
		{
			alert('Date format is not valid!');
			return false;
		}
	
		return true;
	}



	function validateEmail( strValue) 
	{
		var objRegExp  = /(^[a-z]([a-z_\.]*)@([a-z_\.]*)([.][a-z]{3})$)|(^[a-z]([a-z_\.]*)@([a-z_\.]*)(\.[a-z]{3})(\.[a-z]{2})*$)/i;

  		//check for valid email
  		return objRegExp.test(strValue);
	}


//AJAX Example
function ajaxFunctionGeneral()
	{
		var page_request = false

		if (window.XMLHttpRequest) // if Mozilla, IE7, Safari etc
			page_request = new XMLHttpRequest()
		else if (window.ActiveXObject)
		{ // if IE
			try 
			{
				page_request = new ActiveXObject("Msxml2.XMLHTTP")
			} 
			catch (e)
			{
				try
				{
					page_request = new ActiveXObject("Microsoft.XMLHTTP")
				}
				catch (e)
				{
				}
			}
		}
		else		
			return false
		return page_request;
	}

	function reboot(deviceId)
	{
		var response=confirm('Are you sure to reboot');
		
		if(!response)
			exit;
		
		page_request = ajaxFunctionGeneral();
		if (!page_request) return false;
		
		page_request.onreadystatechange=function()
      {
      	if(page_request.readyState==4)
        	{
        		var response=page_request.responseText;
        		var displayArea = document.getElementById('showDiagnosticsResults');
        		displayArea.value = response;
        	}
      }
		page_request.open('GET', '/Inventory/Diagnostics/reboot.php?deviceId='+deviceId, true)
		page_request.send(null)
	}




Javascript Codes



Javascript is a very powerfull scripting language for web-site development. Also, it is in much demand. In Europe Javascript experts earn 29-58 pound/hour [ref: Internet]



	var serviceInstancesArrAtLoad = Array();
	var serviceInstancesArrAtSubmit = Array();
	var tempArr=Array();	
	
	function validate_required(field,alerttxt)
	{
		with (field)
		{
			if (value==null||value=='')
			{
				alert(alerttxt);return false
			}
			else {return true}
		}
	}
	
	function validateTicketCreateForm(thisform)
	{
		with (thisform)
		{
			if (validate_required(type,'Type must be filled out!')==false)
			{type.focus();return false}
			
			if (validate_required(subject,'Subject must be filled out!')==false)
			{subject.focus();return false}
						
		}
	}

	function validate_customer_create_form(thisform)
	{
		with (thisform)
		{
			if (validate_required(firstName,'First name must be filled out!')==false)
			{firstName.focus();return false}
			
			if (validate_required(lastName,'Last name must be filled out!')==false)
			{lastName.focus();return false}
			
			if (validate_required(phone,'Phone must be filled out!')==false)
			{phone.focus();return false}
			
		}
	}
	
	
	function validateUserCreateForm(thisform)
	{
		with (thisform)
		{
						
			if (validate_required(firstName,'First name must be filled out!')==false)
			{firstName.focus();return false}
			
			if (validate_required(lastName,'Last name must be filled out!')==false)
			{lastName.focus();return false}
			
			if (validate_required(shortName,'Short name must be filled out!')==false)
			{shortName.focus();return false}
			
			if (validate_required(username,'Username must be filled out!')==false)
			{username.focus();return false}
			
			/*
			if (validate_required(password,'Password must be filled out!')==false)
			{password.focus();return false}
			
			if (password.value.length < passwordLength.value) {alert("Password must be at least "+ passwordLength.value +" char long");	password.focus();return false}
			
			if (validate_required(password2,'Please re-enter password!')==false)
			{password2.focus();return false}
			
			if (password.value != password2.value)
			{
				alert("Password did not match");
				password2.focus();
				return false			
			}
			*/
			
		}
	}

	function validate_assign_group_form(thisform)
	{
		with (thisform)
		{
			if (validate_required(groupId,'Group name must be filled out!')==false)
			{groupId.focus();return false}
			
			if (validate_required(userId,'Action on invalid user!')==false)
			{userId.focus();return false}		
		}
	}
	
	function validateGroupCreateForm(thisform)
	{
		with (thisform)
		{
			if (validate_required(name,'Group name must be filled out!')==false)
			{name.focus();return false}
			
		}
	}
	
	function validateAssignGroupPermissionForm(thisform)
	{
		with (thisform)
		{
			if (validate_required(taskId,'Task module name must be filled out!')==false)
			{taskId.focus();return false}
			
		}
	}
	
	function validateAssignTaskPermissionForm(thisform)
	{
		with (thisform)
		{
			if (validate_required(groupId,'Group name must be filled out!')==false)
			{groupId.focus();return false}
			
		}
	}
	
	function validateUserEditForm(thisform)
	{
		with (thisform)
		{
						
			if (validate_required(firstName,'First name must be filled out!')==false)
			{firstName.focus();return false}
			
			if (validate_required(lastName,'Last name must be filled out!')==false)
			{lastName.focus();return false}
			
			if (validate_required(shortName,'Short name must be filled out!')==false)
			{shortName.focus();return false}
			
			if (validate_required(username,'Login id must be filled out!')==false)
			{username.focus();return false}
						
			if (password.value.length>0)
			{
				if (password.value.length < passwordLength.value) {alert("Password must be at least "+ passwordLength.value +" char long");	password.focus();return false}
				
				if (validate_required(password2,'Please re-enter password!')==false)
				{password2.focus();return false}
			}
			
			if (password.value != password2.value)
			{
				alert("Password did not match");
				password2.focus();
				return false
			}
			
		}
	}
	
	function validateInventoryCreateForm(thisform)
	{
		with (thisform)
		{
			if (validate_required(serial,'serial must be filled out!')==false)
			{serial.focus();return false}
			
			if (validate_required(hardwareAddress,'Hardware Address must be filled out!')==false)
			{hardwareAddress.focus();return false}
		}
	}
	
	function validateInventoryTypeCreateForm(thisform)
	{
		with (thisform)
		{
			if (validate_required(name,'Name must be filled out!')==false)
			{name.focus();return false}
		}
	}
	
	function validateInventoryModelCreateForm(thisform)
	{
		with (thisform)
		{
			if (validate_required(name,'Name must be filled out!')==false)
			{name.focus();return false}
			
			if (validate_required(manufacturerId,'Manufacturer must be filled out!')==false)
			{manufacturerId.focus();return false}
			
			if (validate_required(typeId,'Type must be filled out!')==false)
			{typeId.focus();return false}
			
			
		}
	}
	
	function validateInventoryManufacturerCreateForm(thisform)
	{
		with (thisform)
		{
			if (validate_required(name,'Name must be filled out!')==false)
			{name.focus();return false}
		}
	}
	
	
	function validateServiceCreateForm(thisform)
	{
		with (thisform)
		{
			if (validate_required(name,'Name must be filled out!')==false)
			{name.focus();return false}
			
			if (validate_required(typeId,'Type must be filled out!')==false)
			{typeId.focus();return false}
			
			
		}
	}
	
	function validateHostingCreateForm(thisform)
		{
			with (thisform)
			{
				if (validate_required(serviceId,'Service must be filled out!')==false)
				{serviceId.focus();return false}
				
				if (validate_required(storageSpace,'Storage space must be filled out!')==false)
				{storageSpace.focus();return false}
			}
		}
		
	function validateDomainName(strValue) 
	{
		return /www\.[a-z0-9_\-]+\.[a-z]{2, 3}/ig.test(strValue);
		
		//var objRegExp  = /^\w+([\.-]?\w+)*(\.\w{2,3})/;

  		//check for valid domain
  		//return objRegExp.test(strValue);
	}
	
	function validateDomainServiceCreateForm(thisform)
	{
		with (thisform)
		{
			if (validate_required(name,'Domain name must be filled out!')==false)
			{name.focus();return false}
			
			/*if (validateDomainName(name.value)==false)
			{alert('Please enter valid domain name');name.focus();return false;}*/
			
			if (validate_required(registrar,'Registrar must be filled out!')==false)
			{registrar.focus();return false}
			
			if (validate_required(registrationTimestamp,'Registration timestamp must be filled out!')==false)
			{registrationTimestamp.focus();return false}
			
			if (validate_required(expiryTimestamp,'Expiry timestamp must be filled out!')==false)
			{expiryTimestamp.focus();return false}
			
			if (validate_required(username,'User name must be filled out!')==false)
			{username.focus();return false}
			
			if (validate_required(password,'Password must be filled out!')==false)
			{password.focus();return false}
			
			if (password.value.length < passwordMinLength.value || password.value.length>passwordMaxLength.value)
			{alert('Password must be '+ passwordMinLength.value+ '-'+ passwordMaxLength.value +' chars long');password.focus();return false;}
			
			if (password.value != password2.value)
			{alert('Password must match');password2.focus();return false;}
		}
	}
	
	
	
	
	function validateCommunityCreateForm(thisform)
	{
		with (thisform)
		{
			if (validate_required(name,'Name must be filled out!')==false)
			{name.focus();return false}
		}
	}
	
	function validatePostalCodeCreateForm(thisform)
	{
		with (thisform)
		{
			if (validate_required(postalCode,'Postal code must be filled out!')==false)
			{postalCode.focus();return false}
			
			if (validate_required(communityId,'Community must be filled out!')==false)
			{communityId.focus();return false}
		}
	}
	
	function validateStreetTypeCreateForm(thisform)
	{
		with (thisform)
		{
			if (validate_required(name,'Name must be filled out!')==false)
			{name.focus();return false}
		}
	}
	
	function validateStreetCreateForm(thisform)
	{
		with (thisform)
		{
			if (validate_required(name,'Name must be filled out!')==false)
			{name.focus();return false}
			
			if (validate_required(typeId,'Type must be filled out!')==false)
			{typeId.focus();return false}
			
			if (validate_required(communityId,'Community must be filled out!')==false)
			{communityId.focus();return false}
		}
	}
	
	function validateUnitCreateForm(thisform)
	{
		with (thisform)
		{
			if (validate_required(streetId,'Street must be filled out!')==false)
			{streetId.focus();return false}
			
			if (validate_required(communityId,'Community must be filled out!')==false)
			{communityId.focus();return false}
			
			if (validate_required(number,'Number must be filled out!')==false)
			{number.focus();return false}
			
			if (validate_required(nodeId,'Node must be filled out!')==false)
			{nodeId.focus();return false}
			
		}
	}
	
	function validatePlantCreateForm(thisform)
	{
		with (thisform)
		{
			if (validate_required(name,'Name must be filled out!')==false)
			{name.focus();return false}
		}
	}
	
	function validateCreateTicketCategoryForm(thisform)
	{
		with (thisform)
		{
			if (validate_required(name,'Name must be filled out!')==false)
			{name.focus();return false}
		}
	}
	
	
	function validateStartWorkOnTicketForm(thisform)
	{
		with (thisform)
		{
			if (validate_required(startTimestamp,'Start Timestamp must be filled out!')==false)
			{startTimestamp.focus();return false}
		}
	}
	
	function validateTicketReportForm(thisform)
	{
		with (thisform)
		{
			if (validate_required(date,'Date must be filled out!')==false)
			{date.focus();return false}
		}
	}
	
	
	function validateCloseTicketForm(thisform)
	{
		with (thisform)
		{
			if (validate_required(date,'Date must be filled out!')==false)
			{date.focus();return false}
			
			if (validate_required(time,'Time must be filled out!')==false)
			{time.focus();return false}
			
		}
	}

	function validateCreateWorkOrder(thisform)
	{
		with (thisform)
		{
			if (validate_required(inventoryId,'Inventory must be filled out!')==false)
			{inventoryId.focus();return false}
			
			if (validate_required(assignedTo,'Assigned to must be provided!')==false)
			{assignedTo.focus();return false}
			
			if (validate_required(scheduledDate,'Scheduled date must be provided!')==false)
			{scheduledDate.focus();return false}
			
			if (validate_required(scheduledTime,'Scheduled time must be provided!')==false)
			{scheduledTime.focus();return false}
			
		}
	}
	
	
	
	
	function addService(services,serviceId)
	{
		for (var i = 0; i < services.length; i++) 
		{
			if (services.options[i].selected) 
			{
				serviceIdLength = serviceId.length++;
				serviceId.options[serviceIdLength].text = services.options[i].text;
				serviceId.options[serviceIdLength].value = services.options[i].value;
				
			}
		}
	}
	
	
	function removeService(serviceId)
	{
		for (var i = 0; i < serviceId.length; i++) 
		{
			if (serviceId.options[i].selected) 
			{
				
				serviceId.options[i].text = '';
				serviceId.options[i].value = '';
				serviceId.options[i].selected = false;
				serviceId.options[i].remove();
			}
			
		}
		
	}
	
	function validateAssignInventoryForm()
	{
		
		var serviceArr=document.getElementsByName("serviceArr[]");
		length=serviceArr.length;
		isAnySelected = false;
		
		for (var i=0;i 0)
			   isServiceRequested = 1;
			   
			if (serviceArr[i].checked == true)
			{
			   if (inventoriesHavingSameService[service] >= 0)
			   	inventoriesHavingSameService[service]=inventoriesHavingSameService[service]+1;
			   else 
			   	inventoriesHavingSameService[service]=1;
			   
			   if (servicesHavingSameInventory[inventory] > 0)
			   	servicesHavingSameInventory[inventory]=servicesHavingSameInventory[inventory]+1;
			   else
			   	servicesHavingSameInventory[inventory]=1;
			}
			
			
		}	
		if (   (!isAnySelected) && (isServiceRequested) )
		{
			alert('Service & inventory requested. But no checkbox is selected');
			return false;
		}
		else if (!isAnySelected)
		{
			var response = confirm('No checkbox is selected, do you want to proceed');
			if (!response) return false;
		}
		
		var errMsg ='';
		
		for (k=1;k= 0 )
			{	
				if ( (serviceRequested[k] != inventoriesHavingSameService[k])  )
				{
					if (serviceRequested[k]>0) 
						errMsg = errMsg + "Inventory is not checked properly for service " + k +", Requested:" + serviceRequested[k] +", Assigned:" + inventoriesHavingSameService[k] + "\n";
					else if (serviceRequested[k]==0 && inventoriesHavingSameService[k]>0)
						errMsg = errMsg + "Inventory is not checked properly for service " + k +", Requested:" + serviceRequested[k] +", Assigned:" + inventoriesHavingSameService[k] + "\n";
				}
			}
		}
			
		if (errMsg.length>0)
		{
			alert(errMsg);
			return false;
		}
		return true;
	}
	
	//not 100% right, will fix later
	function checkDateFormat()
	{
		var pattern = new RegExp(0|1[0-9]/[0-3][0-9]/[0-9][0-9]);
		if(document.getElementById('timestampStarted').value.match(pattern))
		{
			var date_array = document.getElementById('timestampStarted').value.split('/');
			
			var month = date_array[0];
			var day = date_array[1];
			var year = date_array[2];

			// This instruction will create a date object
			source_date = new Date(year,month,day);
	
			if(month != source_date.getMonth())
			{
				alert('Month is not valid!');
				return false;
			}
	
			if(day != source_date.getDate())
			{
				alert('Day is not valid!');
				return false;
			}
			
			if(year != source_date.getYear())
			{
				alert('Year is not valid!');
				return false;
			}
		}
		else
		{
			alert('Date format is not valid!');
			return false;
		}
	
		return true;
	}

	//used by assignService.php
	function initializeServiceInstanceArr()
	{
		if (serviceInstancesArrAtLoad.length == 0)
			serviceInstancesArrAtLoad=document.getElementsByName("serviceInstances[]");
		length=serviceInstancesArrAtLoad.length;

		for(i=0;i0 )
			{
				var forwardAddresses = forwardAddress.value.split(',');
				var length=forwardAddresses.length;
				for(var i=0;i=1)
				{
					lengthModelId=modelId.options.length;
					modelId.options[lengthModelId-1]=null;
				}
      
        		for(var i=0;i=1)
				{
					lengthAddressId=addressId.options.length;
					addressId.options[lengthAddressId-1]=null;
				}
      
        		for(var i=0;i=1)
				{
					lengthServiceId=serviceId.options.length;
					serviceId.options[lengthServiceId-1]=null;
				}
      
        		for(var i=0;i passwordMaxLength.value)
			{alert('Password length should be <= '+passwordMaxLength.value );password.focus();return false}
			
						
			if (validate_required(password2,'Please re-enter site password !')==false)
			{password2.focus();return false}
			
			if (password.value != password2.value)
			{
				alert("Site User Passwords must match");
				password2.focus();
				return false
			}
		}
	}
	
	function validateHostingDomainUserCreateForm(thisform)
	{
		with (thisform)
		{
			if (validate_required(domainId,'Domain name must be filled out!')==false)
			{domainId.focus();return false}
			
			
			if (validate_required(username,'Domain user name must be filled out!')==false)
			{username.focus();return false}
			
			if (validate_required(password,'Domain password must be filled out!')==false)
			{password.focus();return false}
						
			if ( password.value.length  < passwordMinLength.value )
			{ alert('Password length should be at least '+passwordMinLength.value); password.focus();return false }
			
			if (password.value.length  > passwordMaxLength.value)
			{alert('Password length should be <= '+passwordMaxLength.value );password.focus();return false}
			
			
			if (validate_required(password2,'Please re-enter site password !')==false)
			{password2.focus();return false}
			
			if (password.value != password2.value)
			{
				alert("Domain User Passwords must match");
				password2.focus();
				return false
			}
		}
	}
	
	
	//outage
	function validateOutageCreateForm(thisform)
	{
		with (thisform)
		{
			if (validate_required(subject,'Subject must be filled out!')==false)
			{subject.focus();return false}
			
			//check community selection
			var communityNamesArr=document.getElementsByName("communityNames[]");
			lengthArr= communityNamesArr.length;			
			var isAnySelected = false;
			
			for (var i=0;i


Introduction to JQuery



 

     Introduction to JQuery
    
 JQuery Tutorials
    
 Google Ajax Libraries API

 


JavaScript DOM: Must knowledge to understand Ajax



 

     Understanding Javascript DOM is the first step to understand Ajax.
    
 DOM provides many methods to access and edit individual components of a web-page/document. The methods are called getters.
    
  < div id = 'test' > Hello < /div > getElementById method can reference to this div element. var testDiv = document.getElementById("test"); CSS #test{} refers to the same area of the document
   
 getElementsByTagName: all the elements with the partcular tag name. Returns an array. var testTag = document.getElementsByTagName("p"); just like p{} in CSS applies to all  tags. 
   
 testTag.length can be useful.
   
 You can also cycle through the elements and take some actions
         for(var i = 0; i < testTag.length ; i++){
         //do something
      }
   
   
 Another example: document.getElementById("test").getElementsByTagName("p"); [CSS #test p{} - will affect the same area in CSS]
   
 DOM provides getAttribute () : can access the value of an attribute. < p id='test' title='justEtc Computer' > Hello <p> : var title= document.getElementById("test").getAttribute("title");
  
 Web-page can be thought of a set of interconected nodes. getElementById, getElementsByTagName, getAttribute - all help in accessing the nodes. 
  
 Three basic types of nodes in web-pages: element, text, attribute. element - building block, text = attribute = content
 Every node is contained under another node. So there will be parent and child relationships. parentNode, childNode - are corresponding methods for accessing parents and childs.
 Example: var test = document.getElementById("test"); var testParent= test.parentNode; var testChild = test.childNodes;
 
 childNodes - returns an array 
 
 var allEle = document.getElementsByTagName("*"); - a collection of all the elements of the web-page.
  
 firstChild - first Child of an element, lastChild - lastChild of an element, previousSibling, nextSibling -  having same parent of the current node (next element), nodeValue, nodeValue - content of a node,  
 
 DOM Setters
 
 Using DOM, you can create elements dynamically and put it in the document. createElement() - method serves the purpose
 
var paragraph = document.createElement("p"); - element created - element node - but not inserted in the document yet

var textNode = document.createTextNode("A new text node from DOM!"); - creates a text node


 setAttribute() - method can be used to set the attributes of a node. example: paragraph.setAttribute("title", "introduction"); 

 appendChild() - append one node under another node;
 
    var pNode = document.createElement("p");
    var textNode = document.createTextElement("How is it going?");
     pNode.setAttribute("title","Test Title");
     pNode.appendChild(textNode);     
     var testDiv = document.getElementById("testDiv");
     testDiv.appendChild(pNode);

 
 removeChild() - helps to remove a child node from a parent node
 


Ajax: An Overview



 

    Ajax: dynamically changes a portion of the current web-page without refreshing the total web-page. Resembles the way IFrame works. So far I know, google uses IFrame to display/refresh maps in web-pages
   
 XMLHttpRequest is the object that serves the purpose of Ajax 
   
 The way it works: create an instance of the XMLHttpRequest object, send request to the back-end web-page that will do some processing and perhaps return some data, receive the response data, dynamically change the content of the target area, you may need to get a reference to the target area using DOM, after sending request - you have to wait for the response to come back
 create an instance of the XMLHttpRequest object: It varies for the different browsers. IE provides ActiveX Control for the purpose. A sample code can be as follows:


  function getAjaxObject(){
    var ajaxObject = false;
    if (window.XMLHttpRequest){
         ajaxObject = new XMLHttpRequest();
    }else if (window.ActiveXObject) {
      try{
        ajaxObject = new ActiveXObject("Msxml2.XMLHTTP");
      }catch(e){
         try{
          ajaxObject = new ActiveXObject("Microsoft.XMLHTTP");
         }catch(e){
           ajaxObject = false;
         }

      }
    }
    return ajaxObject; 
  }


 XMLHttpRequest has three core components. onreadystatechange - event to notify response has come or identify server activity, open - method , send method

 use of onreadystatechange 
if (ajaxObject){
   ajaxObject.onreadystatechange = takeAction; //takeAction - reference to a function
}


 Open method specifies the server side script to handle the request, data to send to the server, method of sending (GET, POST) : Required: type of request (first argument), location of the file in the server (2nd argument) 

 Open method: third argument: true = processing will be done asynchronously, false = synchronous processing - browser will stop processing until response comes [true is usually better]

 send : send method initiates the request : also passes data to the server 
 For GET method the argument can be set to null, for POSt method the argument can be a query string such as "id=500&name=keith&age=18"
 use setRequestHeader() - to provide metadata 
 Sample code:
 var ajaxObject = getAjaxObject();
 if (ajaxObject ){
    ajaxObject.onreadystatechange = takeAction; 
    ajaxObject.open("POST","file.jsp", true);
    ajaxObject.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
    ajaxObject.send("id=500&name=keith&age=18");
 }

 How to receive and process the response:
 The readyState property indicates the current status of the request. 0 - Uninitialized, 1 - Loading, 2 - Loaded, 3 - interactive, 4 - complete
 each time the value of readyState changes onreadystatechange - event is triggered. At value 4, we can collect the response and change the web-page dynamically
 Sample:
 function takeAction(ajaxObject){
  if (ajaxObject.readyState == 4) {
     //do something with the response
  }
 }

 status is another property to consider - it indicates the status of sending request like 404 - not found, 200 = success, 304 = not modified
 The prev function  

 function takeAction(ajaxObject){
  if (ajaxObject.readyState == 4) {
     if (ajaxObject.status == 200 || ajaxObject.status == 304){ //response was sent succesfully 
          //do something with the response
     }
  }
 }

responseText is the response from the server
  function takeAction(ajaxObject){
  if (ajaxObject.readyState == 4) {
     if (ajaxObject.status == 200 || ajaxObject.status == 304){ //response was sent succesfully 
          //do something with the response
          alert(ajaxObject.responseText);
     }
  }
 }

 responseXML can be used when the response was sent as xml and the response header is "text/xml"
 All together
  function getAjaxObject(){
    var ajaxObject = false;
    if (window.XMLHttpRequest){
         ajaxObject = new XMLHttpRequest();
    }else if (window.ActiveXObject) {
      try{
        ajaxObject = new ActiveXObject("Msxml2.XMLHTTP");
      }catch(e){
         try{
          ajaxObject = new ActiveXObject("Microsoft.XMLHTTP");
         }catch(e){
           ajaxObject = false;
         }

      }
    }
    return ajaxObject; 
  }

  function entryPoint(){
    var ajaxObject = getAjaxObject();
     if (ajaxObject ){
    ajaxObject.onreadystatechange = function(){
      takeAction(ajaxObject); 
    };
    ajaxObject.open("POST","file.jsp", true);
    ajaxObject.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
    ajaxObject.send("id=500&name=keith&age=18");
 }
 }
 function takeAction(ajaxObject){
  if (ajaxObject.readyState == 4) {
     if (ajaxObject.status == 200 || ajaxObject.status == 304){ //response was sent succesfully 
          //do something with the response
          alert(ajaxObject.responseText);
          var testDiv = document.getElementById("test");
          testDiv.innerText = ajaxObject.responseText; 
     }
  }
 }

 Processing Response Data
 The usual practice: response data can be in one of three formats: XML, JSON, HTML. And may be plain text.
 XML is the most common. example receive: var data = ajaxObject.responseXML; Here we can use the DOM functions to parse the XML data
 Example:
var data = ajaxObject.responseXML;
data.getElementsByTagName("name")
data.getElementsByTagName("name")[0]
data.getElementsByTagName("name")[0].firstChild
data.getElementsByTagName("name")[0].firstChild.nodeValue
Similarly, you can use other DOM functions

 To change the contents of the web-page dynamically, you can use the DOM methods like -- create, set, innerText, innerHtml (use carefully) methods. Check the JavaScript DOM article [846] in this web-site. adding and removing childs/elements may be required in some situations - DOM also supports that
 You can also send data as JSON from the server side like: JSON format: 


{"person":{
     "name":"Keith Tang",
     "school":"uofm"
  }
}
 Receiving and extracting information from JSON: [content type will be text]
 var data = eval('('+ ajaxObject.responseText +')');
 var name = data.person.name;
 var school = data.person.school; 
 
 Response data as HTML 
 The response can come as HTML. This may be useful, if only one area of the web-page is affected and we want to put the HTML response in that area. Otherwise it may not be great. Also, we need to use innerHTML method that was introduced by IE and later adopted by others. Still, it's not a standard (W3C)
 Example: The content type of response data should be text/html
       if (ajaxObject.status == 200 || ajaxObject.status == 304){ //response was sent succesfully 
          //do something with the response
          alert(ajaxObject.responseText);
          var testDiv = document.getElementById("test");
          testDiv.innerHTML = ajaxObject.responseText; 
     }





PHP Security: Coding that Maintains Security


Php Security

PHP can be included as a module to the web-server, or executed as a separate executable binary. In either case, it can access files, execute commands, open network connections in the server. Further, PHP can be used to write scripts with all the power of the shell users. Hence, anything running on that server may face security problems. Though, careful coding will reduce the risks to a great extent[php.net].


Common security risks in PHP[Abdul Basit, php.net]

Most common are :


1-Invalidated Input Errors
2-Access Control Flaws
3-Session ID Protection
4-Cross Site Scripting (XSS) Attacks
5-SQL Injection Vulnerabilities
6-Error Reporting
7-Data Handling Errors
8-PHP configuration settings



PHP Security when installed as a CGI Binary[php.net]

 Do not place any interpreters into the cgi-bin directory
 Even If PHP is installed as a standalone binary (and in cgi-bin directory), PHP can prevent attacks that may arise from such setting.
Accessing system files: http://my.host/cgi-bin/php?/etc/passwd -- using such URLs can be risky, the part after ? may be treated as command line arguments to the interpreter, and hence, in some cases pose risks

Accessing any web document on server: 

http://my.host/cgi-bin/php/secret/doc.html -- this way can also be risky 


PHP compile time options such as --enable-force-cgi-redirect and runtime configuration directives doc_root and user_dir can be used to prevent such risks.


PHP and IBM DB2


IBM-DB2: Random stuffs.

This is a great book on IBM-DB2. If you are experienced with DBMS systems like SQL Server, Oracle, MySQL, the first thing you may want to look at the features that are supported by IBM-DB2. Then you can try to find out the differences among them. It really will make your learning much faster. Then just skim through page by page, when you are at the end of the book, you will find you are ok to work with IBM-DB2.
http://publib-b.boulder.ibm.com/Redbooks.nsf/RedbookAbstracts/sg244249.html

PHP has concepts like PDO and PDO_ODBC, and generic odbc features that will allow you to connect to and work with DB-2 databases. Generic odbc is the general/procedural way to connect to. PDO and PDO_ODBC are object oriented ways. PDO works with native IBM drivers. PDO_ODBC uses odbc connections to the DB2. For PHP you need to install drivers to support DB2. I worked with PDO_ODBC to connect to DB-2 databases in IBM iSeries servers. You need to install odbc db2 drivers for iseries servers. Then in /etc/odbcinst.ini file you need to mention driver specifications and in /etc/odbc.ini, you need to mention the odbc dsn name, database name, and some other parameters to connect to the database. Though, you may also supply some parameters in the PDO_ODBC connect method.

For details on PHP and DB2, please check:

http://www.redbooks.ibm.com/redbooks/pdfs/sg247218.pdf




A Sample PHP Class



A sample PHP class is provided below. How the class is used? 



When the submit button from the interface (web-page) will be clicked the code in the form-action web-page will call the the create method (static) to create a Ticket data/row in the database table Ticket. The method call will look like Ticket::create(array with field=>value pair, as formed with form submitted data) and you will get an Ticket object created with the inserted row/data.



To update a Ticket, you can create the Ticket object (constructor will do it for you) as you will know the Ticket id at this time. Then use the appropriate set method to update the value in the database. 



To search call the static search method with a list/array of field=>value pair. 



$dbh (read-only) and $dbhWrite(read-write) are global variables that represent connections to the database.
 



//represents a customer submitted Ticket
class Ticket
{
	public $parentTicket;
	private $dbh_;

	//member variables, named after the column names of the databse table - Ticket
	private $id_;
	private $type;
	private $subject_;
	private $timestampCreated_;
	private $timestampUpdated_;
	private $status_;
	private $parentTicketId_;
	private $categoryId_;
	private $categoryName_;
	private $internalNotifylist_;
	private $externalNotifyList_;
	private $customerId_;
	private $customerName_;
	private $creatorId_;
	private $creatorName_;
	private $ownerId_;
	private $assignedTo_;

	//constructor, creates a Ticket object with the table row with id = $id
	public function  __construct($id)
	{
		global $dbh;
		$this->dbh_ = $dbh;
		$this->refreshValues($id);
	}


	public function refreshValues($id)
	{

		$query="Select Ticket.*, TicketCategory.name
  from Ticket left join TicketCategory on Ticket.categoryId 
= TicketCategory.id
				where Ticket.id=$id";

		if ($result=$this->dbh_->query($query))
			if($result->num_rows==1)
			{


				$row=$result->fetch_object();

				$this->id_ = $id;
				$this->type_=$row->type;
				$this->subject_ = $row->subject;
				$this->timestampCreated_ = $row->timestampCreated;
				$this->timestampUpdated_ = $row->timestampUpdated;
				$this->status_ = $row->status;
				$this->parentTicketId_ = $row->parentTicketId;

				if($this->parentTicketId_>0)
					$this->parentTicket=$row->parentTicketId; //new Ticket($this->parentTicketId_);
				else $this->parentTicketId=0;

				$this->categoryId_ = $row->categoryId;
				$this->categoryName_ = $row->name;
				$this->internalNotifyList_ = $row->internalNotifyList;
				$this->externalNotifyList_ =$row->externalNotifyList;
				$this->customerId_ = $row->customerId;
				$this->creatorId_ = $row->creatorId;
				$this->ownerId_ =$row->ownerId;
				$this->assignedTo_ =$row->assignedTo;

				return true;
			}
			else
				return false;
	}

	//methods to retrieve/set data/member variables

	//retrieve id
	public function getId()
	{
		if($this->id_>0) return $this->id_;
		else return false;
	}
	//set id
	public function setId($id)
	{
		if (is_numeric($id))
		{
			if ($this->setField('id',$id)) return true;
			else return false;
		}
		else return false;
	}

	public function getCustomerId()
	{
		if($this->id_>0) return $this->customerId_;
		else return false;
	}
	public function setCustomerId($customerId)
	{
		if (is_numeric($customerId))
		{
			if ($this->setField('customerId',$customerId))
				return true;
			else return false;
		}
		else return false;
	}

    //used by methods to set member variables
	private function setField($field, $value)
	{
		$dbhWrite=getDbhWrite();
		$query="update Ticket set 
$field='".$dbhWrite->escape_string($value)."' where 
id=$this->id_";

		$result = $dbhWrite->query($query);
		if ($result==true)
		{
			if($dbhWrite->affected_rows==1)
			{
				$this->setTimestampUpdated();
				$this->refreshValues($this->id_);
				return true;
			}
			else return false;
		}
		else return false;
	}

    //used to create an entry into the Ticket table (database).
    //After insertion this row is used to form a Table object and returned to the caller
	static public function create($fields)
	{
		$dbhWrite=getDbhWrite();
		$timestamp = time();
		$parentTicketId='null';
		$customerId='null';
		$ownerId='null';
		$assignedTo='null';
		$categoryId='null';
		$type='null';

		foreach($fields as $field => $value)
		{
			switch($field)
			{
				case "type":
					if(self::isPermittedType($value)) $type=$value;
					else return false;
					break;
				case "status":
					if(self::isPermittedStatus($value)) $status=$value;
					else return false;
					break;
				case "subject":
					if (is_string($value))
						$subject=$dbhWrite->escape_string($value);
					else
						return false;
					break;
				case "parentTicketId":
					if(is_numeric($value))
						$parentTicketId=$value;
					break;
				case "categoryId":
					if(is_numeric($value))
						$categoryId=$value;
					break;
				case "customerId":
					if(is_numeric($value)) $customerId=$value;
					else return false;
					break;
				case "creatorId":
					if(is_numeric($value))
						$creatorId=$value;
					else return false;
					break;
				case "ownerId":
					if(is_numeric($value))
						$ownerId=$value;
					break;
				case "assignedTo":
					if(is_numeric($value))
						$assignedTo=$value;
					break;
			}
		}


		$insertStr = "insert into Ticket 
(type,subject, timestampCreated, timestampUpdated, status, 
parentTicketId, categoryId, customerId, creatorId, ownerId, 
assignedTo) values ($type, '$subject', $timestamp, 
$timestamp, $status, $parentTicketId, $categoryId, 
$customerId, $creatorId,$ownerId,$assignedTo)";

		$result = $dbhWrite->query($insertStr);

		if ($result == true)
		{
			if ($dbhWrite->affected_rows==1)
			{
				$ticket=new Ticket($dbhWrite->insert_id);
				return $ticket;
			}
			else return false;
		}
		else return false;

	}



	//searches the entire ticket table based on supplied field=>value pairs
	static public function searchFields($fields,$orderBy='id')
	{
		global $dbh;

		$query="select * from Ticket where ";
		foreach($fields as $field => $value)
		{
			if($value[0]=="!") //checking for not equal condition
			{
				$value=substr($value,1);
				$query.="`".$dbh->escape_string($field)."`!='".$dbh->escape_string($value)."' and ";
			}
			else
				$query.="`".$dbh->escape_string($field)."`='".$dbh->escape_string($value)."' and ";
		}
		$query=substr($query,0,-5);
		$query.=" order by ".$dbh->escape_string($orderBy);
		$result = $dbh->query($query);

		if ($result)
		{

			if ($dbh->affected_rows>0)
			{
				$tickets = array();
				$tickets = Ticket::processResult($result);
				return $tickets;
			}
			else return false;
		}
		else return false;
	}


    //used by searchFields method. Converts a set of retrieved data rows into array of objects.
	static private function processResult($result)
	{
		if($result->num_rows >= 1)
		{

			$tickets=array();
			while($row=$result->fetch_object())
			{
				$tickets[] = new Ticket($row->id);
			}

			return $tickets;
		}
		else return false;
	}





}




MVC for PHP



 

    http://www.phpmvc.net/
 


Creating Custom Tags in JSP



How to create custom tags in JSP?
You can create custom tags in JSP. 
Steps:
-----
1. You have to create a Java file that will define the operation of the custom tag
2. For the Java file, you have to extend javax.servlet.jsp.tagext.BodyTagSupport class
3. in your implementation, you have to override (re-write) doStartTag(), doEndTag(), 
and doAfterBody() methods
4. Create tag library description file(XML file with .tld extension).

Example
-------

Implement a tag that reverses a string


import java.io.IOException;
import javax.servlet.jsp.JspTagException;
import javax.servlet.jsp.JspWriter;
import javax.servlet.jsp.tagext.BodyContent;
import javax.servlet.jsp.tagext.BodyTagSupport;

public class ReverseTag extends BodyTagSupport 
{
    private static final long serialVersionUID = 1L;    
    
    //override doStartTag
    public int doStartTag() throws JspTagException{
        return EVAL_BODY_TAG;
    }    
    
    //override doEndTag
    public int doEndTag() throws JspTagException 
    {
      try {
           JspWriter out = pageContext.getOut();
      } catch (Exception ex) {
           throw new JspTagException("Exception" + ex);
      }
    return SKIP_BODY;
 }

//reverse the text    
public int doAfterBody() throws JspTagException 
{
    BodyContent body = getBodyContent();
    try {
            JspWriter out = body.getEnclosingWriter();
            //get text inside the tag
            String bodyContent = body.getString();
            //reverse the text
            if (bodyContent != null) {
                for (int i = bodyContent.length() - 1; i >= 0; i--) {
                    out.print(bodyContent.charAt(i));
                }
            }
            out.println();
            body.clearBody(); // Clear for next evaluation
          } catch (IOException ioe) {
               throw new JspTagException("Exception at doAfterBody " + ioe);
     }
        return (SKIP_BODY);
    }
}

5. Create the taglib descriptor

<?xml version="1.0" encoding="ISO-8859-1" ?>
<!DOCTYPE taglib PUBLIC "-//Sun Microsystems, Inc.
//DTD JSP Tag Library 1.1//EN" "http://java.sun.com/j2ee/dtds/web-jsptaglibrary_1_1.dtd">
<taglib>
    <tlibversion>1.0</tlibversion>
    <jspversion>1.1</jspversion>
    <shortname></shortname>
    <info></info>
    <tag>
        <name>stringreverse</name>
        <tagclass>net.justetc.taglibrary.ReverseTag</tagclass>
        <info>
            Reverse the text
        </info>
    </tag>
</taglib>


6. Example use of the custom tag


<%@ taglib uri="/WEB-INF/reverse.tld" prefix="reverse" %>

<html>
<head>
    <title>Custom Tag library</title>
</head>

<body bgcolor="#ffffff">

<hr />
<reverse:stringreverse>
        justetc
</reverse:stringreverse>
<hr />
</body>
</html>





































JSP Tags



JSP Tag Library (JSTL)

JSP Disadvantage
1. Java code embedded in scriptlets make the code difficult to understand
2. Reduced code reusability
3. Difficult to modify [Java Code]
4. Java code needs to use Class Casting

JSTL Advantages

1. Better code
2. Enhanced Code reusability
3. No type casting from the request and session objects
4. Expression Language in JSP makes it easier to call getter and setter methods.

Drawback

1. Adds overhead as JSTL adds more into the resultant Servlet
2. Can not do all that Java code can do


Four Libraries:

Core: Basic tags like loops, conditions, and input/output
fmt: Format like date format
xml: XML processing
sql: Database access


Core Tag Library

<%@ taglib prefix="core" uri="http://java.sun.com/jsp/jstl/core" %> 
<core:set var="var" scope="session" value="..."/> 

Conditional
<core:if test="${condition}"> </core:if> 
<core:choose>
     <core:when test="condition1" >
     ...
     </core:when>
    <core:when test="condition2" >
    ...
    </core:when>
    <core:otherwise>
    </core:otherwise>
</core:choose>

<core:otherwise> will be executed only and if only all other conditions fail.


Iterator

Iterator tages are used to traverse an array of objects and process each object (when required)
<c:forEach var="item" items="collection">
</c:forEach>

or

<c:forEach begin="0" end="10" varStatus="status" step="1" >
</c:forEach>


Function Tag Library

Basic String Functions
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %> 

Example
<%@ taglib uri="/WEB-INF/fn.tld" prefix="fn" %>
<%@ taglib prefix="core" uri="http://java.sun.com/jsp/jstl/core" %> 

<core:set var="test" value="Hello World"/>
Index of l is :- ${fn:indexOf(test, "l")}<br>




JSP Expression Language



Expression Language
-------------------
Introduced in JSP 2.0
You can write short expressions for different operations [other than using functions]

If you want to ignore EL in any page add

<%@ page isELIgnored ="true" %>


Some examples

EL=> what does it mean?

\${3+2-1}=> ${3+2-1} <%-- Addition/Subtraction --%>

\${"1"+2} => ${"1"+2} <%-- String conversion --%>

\${3%2} => ${3%2} <%-- Modulo --%>

\${(8 div 2) mod 3} => ${(8 div 2) mod 3} <%-- Compares with "equals" but returns false for null --%>

\${1<2} => ${1<2} <%-- Numerical comparison --%>




JSP: Random Information



  

      Three JSP constructs: scripting elements, actions, directives
     
 Scripting elements: expressions, scriptlets, declarations    
     
 Expression = translates to println in servlets in _jspService methods  <% = expression %> <% = new java.util.Date() %>   inserts values directly to the output
   
 scriptlet: block of java code : directly inserted into the related servlets in the _jspService method: <% java code %>
   
 declarations: <%! variable and method declarions%>  Directly placed in the body of the translated servlets
   
 >= JSP 1.2 : XML Syntax for expressions, scriptlets, declarations    


<jsp:expression>Java Expression</jsp:expression>
<jsp:scriptlet>Java Code</jsp:scriptlet>
<jsp:declaration>JSP Declaration</jsp:declaration>


 JSP implicit objects: request, response, out, session 
 Implicit objects: application = servletContext = persistent data, config = ServletConfig = initialization parameters, pageContext = context to store reference to objects, and page = this, exception = used by error pages :

<%@ directive attribute ="value " %>
<%@ directive attribute1 ="value1 " attribute2 ="value2 " ... attributeN ="valueN " %>
<jsp:directive.directivename attribute1 ="value1 " attribute2 ="value2 " ... 
attributeN ="valueN " />

 JSP Directives : page, include, taglib
 page directive attributes:  import, extends, session, isThreadSafe, buffer, autoFlush, contentType, pageEncoding, errorPage, isErrorPage, language, and info


<%@ page import="package.class " %>
<%@ page import="package.class1,...,package.classN " %>
<%@ page extends="somepackage.someclass " %>
<%@ page session="true" %>
<%@ page session="false" %>
<%@ page isThreadSafe="true" %>
<%@ page isThreadSafe="false" %> 
<%@ page buffer="none" %>
<%@ page buffer="sizekb" %>
<%@ page autoFlush="true" %>
<%@ page autoFlush="false" %>
<%@ page contentType="MIME-Type " %>
<%@ page contentType="MIME-Type ; charset=Character-Set " %>
<%@ page contentType="video/mpeg" %>
<%@ page pageEncoding="GB_2312-80" %>
<%@ page errorPage="Relative URL " %>
<%@ page info="Some Message " %>
<li> XML Syntax
<jsp:directive.page buffer="32kb" />
<jsp:directive.page isErrorPage="true" />

 Other directives:

<%@ include file="relevantURLspec " %>
<jsp:directive.include file="relativeURLspec " />
<jsp:include page="RelativeURL " flush="true" /> : output of another file


 JSP Custom Tag: 

 <%@ taglib uri="TagLibraryURI " prefix="someprefix " %>


 JSP Pages and Java Beans: JSP page can include logics in Java Code - not recommended - rather embed code into beans or custom tags
 JavaBean: basically a class, visible, also invisible: JSP supports both types:
 JavaBeans are accessed through Introspections : 
 Introspection: a class that extends BeanInfo Interface
 JSP action tags for Java Beans: jsp:useBean, jsp:getProperty, jsp:setProperty
jsp:useBean: attributes: id, class, scope=(page,request,session,application), beanName, type, 
jsp:getProperty: name = name of the bean = id of jsp:useBean, property = property to access
jsp:setProperty: attributes: name = name of the bean = id of jsp:useBean, property = property to access, value = value to set, use param attribute to extract from request object for setting values
 To use a bean from JSP page, place the bean class file in one of the accessible servlet directories
 Custom Tags = alternatives to java beans for JSP = reduces some limitations of JavaBeans (in JSP) like to few methods to access the bean, you may still require  to write much java code in JSP [that we want to avoid]
  


JSP: Custom Tags




 Custom Tags = alternatives to java beans for JSP = reduces some limitations of JavaBeans (in JSP) like to few methods to access the bean, you may still require to write much java code in JSP [that we want to avoid]
 Overview:
 Custom tag with no body should extend TagSupport class that implements Tag interface
 Tags extending TagSupport should extend only one method - doStartTag(), and in this case the doStartTag() method should return SKIP_BODY
 A simple tag code is shown below:


Next you need to define a tld file - a descriptor file for the tag - XML file.
 In the tld files, the most important tag is the tag
 The sub-elements of the tag element are: name, tag-class, description, body-content.
 Sample:


<tag>
  <name>SimpleTag</name>
  <tag-class>tags.SimpleTag</tag-class>
  <description>Sample TLD File</description>
  <body-content>empty</body-content>
</tag>


 To use the custom tag in a JSP file, You need to provide a directive like:


<%@ taglib uri='path to the tld file' prefix='st' %> 


Example use: <st:simple>
 How to add attributes to tags: In the class, declare private variable for each attribute you want, define a setXXX method for each of the attribute
 In the tld - file, add an attribute tag for each attribute you want. This attribute tag can have following sub-elements: name, required, rtexprvalue = can the value be an expression, type=class to which the value should typecast to, description 
 Defining tag body: doStartTag() should return EVAL_VALUE_INCLUDE
 A new method named doEndTag() should be defined that will define what will be the effect on the body text. 
 The doEndTag() method may return EVAL_PAGE = continue to process rest of the JSP page, SKIP_PAGE = stop processing rest of the page 
 To support body inside the custom tags - body-content tag in the tld file needs to be adjusted: example 


<tag>
  <name>SimpleTag</name>
  <tag-class>tags.SimpleTag</tag-class>
  <description>Sample TLD File</description>
  <body-content>JSP</body-content>
</tag>


Example use: <st:simple> Body Text  <st:simple>



MVC : Struts : Java : Industry Web Application



Industries use frameworks for application development quite often. 

For example: Java concepts like JSP, Servlet, Swing, Bean, JDBC can 

be used directly to create web-applications but when such 

applications become big, it becomes difficult to maintain and 

develop them further. Hence, frameworks like struts are used to develop 

large web-based Java applications. This makes maintenance and 

further development easier. If you need to write a very simple web 

application with a few pages, then you might consider using 

JSP/Servlet directly otherwise Struts like frameworks are better 

options.

Struts-1 uses the concept Model View Architecture (MVC) and provides 

the controller. In MVC model, Model represents business logic, View 
represents user interfaces, and Controller represents/controls control 
flow of the application. 


Struts' control layer is based on standard technologies like 

Java Servlets, JavaBeans, ResourceBundles, and XML, as well as 

various Apache Commons  packages, like BeanUtils and Chain of 

Responsibility. 

For the Model,  struts-1 framework can interact with standard 
data access technologies, like JDBC  and EJB,  as well as most 
any third-party packages, like Hibernate,  iBATIS,  or Object Relational 
Bridge.  For the View,  the framework works well with JavaServer Pages,  
including JSTL and JSF,  as well as  Velocity Templates,  XSLT,  and 
other presentation systems.


The framework's Controller acts as a bridge between the application's Model and the web View. 
When a request is received, the Controller invokes an Action  class. The Action class consults 
with the Model to examine or update the application's state. The framework provides an ActionForm  
class to help transfer data between Model and View.

Struts-1 Configuration
-----------------------
web.xml is one of the configuration files. It is an XML-formatted file that works as a 
deployment descriptor. 

struts-config.xml is another resource file to initialize the applications resources. 
These resources include  ActionForms  to collect input from users,  ActionMappings  
to direct input to server-side  Actions,  and ActionForwards to select output pages.

Sample struts-config.xml file.

<?xml version="1.0" encoding="ISO-8859-1" ?>
    <!DOCTYPE struts-config PUBLIC
          "-//Apache Software Foundation//DTD Struts Configuration 1.3//EN"
          "http://struts.apache.org/dtds/struts-config_1_3.dtd">
    <struts-config>
        <form-beans>
            <form-bean
                name="logonForm"
                type="app.LogonForm"/>
        </form-beans>
        <action-mappings>
            <action
                path="/Welcome"
                forward="/pages/Welcome.jsp"/>
            <action
                path="/Logon"
                forward="/pages/Logon.jsp"/>
            <action
                path="/LogonSubmit"
                type="app.LogonAction"
                name="logonForm"
                scope="request"
                validate="true"
                input="/pages/Logon.jsp">
                <forward
                    name="success"
                    path="/pages/Welcome.jsp"/>
                <forward
                    name="failure"
                    path="/pages/Logon.jsp"/>
            </action>
            <action
                path="/Logoff"
                type="app.LogoffAction">
                <forward
                    name="success"
                    path="/pages/Logoff.jsp"/>
            </action>
        </action-mappings>
        <message-resources parameter="resources.application"/>
    </struts-config>

Other resources like Validators can be initialized here.




How to upload data from an excel file to database using servlets?



Use third party API bundles like jexcelApi. 
http://jexcelapi.sourceforge.net/.


---

create an XML file to define the structure of your excel file like how many columns, column names, corresponding database table/class column/variable name, 



Create a Java class that can retrieve information from that xml file. You can use XPATH and DOM for the purpose. check http://www.ibm.com/developerworks/library/x-javaxpathapi.html for ideas



---


write an html form that use input type='file', the form action should point to the servlet that will collect data from the excel file and store it in the database. servlet will collect data from the input stream



-----------


in the servlet use the class that represents the xml file. to get information about the file that will help mapping the excel file data to the corresponding table column. You can also create a class representing the database table. From servlet extract data, create an object of table class type, and create a function that takes the object and inserts into the table.



---------------



How servlet will process the excel file:




jexcelAPI Workbook class represents the total workbook

use getsheet method of workbook class to get the sheet 
number 0,1,2....


use getCell method of worksheet to get a particular cell object in the excel file

use getContents on the cell object to get data in string type.



from the XML file having excel file structure, you can get number of columns in the file. So you can write a look that will read data row,column wise from the excel file



----

code to process excel file [in servlet]


 Workbook workbook = Workbook.getWorkbook(new File("myfile.xls"));


 Sheet sheet = workbook.getSheet(0);


 Cell a1 = sheet.getCell(0,0);


Cell b2 = sheet.getCell(1,1);


Cell c2 = sheet.getCell(2,1);



String stringa1 = a1.getContents();


String stringb2 = b2.getContents();


String stringc2 = c2.getContents();



----------------------


check the following webpage, it provides file upload and extract data from input stream in servlet applications





http://jexcelapi.sourceforge.net/resources/faq/






Servlet, JSP Specifications



   JSP Specification
  
 Servlet Specification
 Java Enterprise javabeans Specifications


JSP: Reference Manuals




   JSP Syntax : Quick Reference
  
 JSP Syntax : Quick Reference
'target='new'>JSP Syntax : Quick Reference - 2
 JSP tutorial by Sun: Reference




Servlet Random Information



  

      Web component information sharing: web context (ServletContext), request, session, and page
     
 ServletContext - Scope: entire web-application (Servlets & JSPs)
     
 page/PageContext : A single point of access for many of the attributes of a JSP page 
     
 For each access of the servlet a thread is created. In applications a thread can be accessed concurrently by many threads.
     
 If the servlet accesses and edits some external resources, then data inconsistency, and  data loss may occur
     
 Synchronized access should help:
     
 public class TestServlet extends HttpServlet implements SingleThreadModel   will provide synchronization and concurrency
      
 In JSP, you can include <%@ page isThreadSafe="false" %> - to provide thread safety
      
 For SingleThreadModel: you still have to synchronize access to class variables and to any shared resources that are stored outside the servlet. example:
 
 synchronized(sharedResource) {
 }


       
 For high-traffic servlets, you should use explicit synchronization blocks rather than implementing SingleThreadModel
 Synchronization is only an issue if the servlet accesses external data resources -- returns data from another source: not for a servlet that just performs a task with no data access
  


Complete path analysis to get a merchant account


Please Check:

http://www.sitepoint.com/article/merchant-account-review


Detail Discussion to have a Merchant account


If pricing is a concern check it.
http://www.sitepoint.com/article/money-where-mouse-is-gateways/4


For detail explanations, please check the following link:
http://www.sitepoint.com/article/money-where-mouse-is-gateways



Web Hosting Companies


Best Hosting Companies
I was searching for a suitable webhosting for me. Until now features and cost wise I liked HostGator, VodaHost, HostMonster, BlueHost. Yahoo is another one I like but pricy. I am into hostgator and vodahost mainly. Though, vodahost has some bad reputation and their customer support may not be great. Just few other hosting companies are below. They are also very popular with huge customers. They may be better than Hostgator or Vodahost or HostMonster for you but for me I am sticking with HostGator[Do not have much experience with their customer support though]. It's really difficult to come up with the best.

	GoDaddy.com

	
Dotster.com

        
Lunar Pages

        
 Easy CGI

	
1 & 1 Hosting

	
HostGator

        
An Hosting

        
HostRocket.Com


	
Netfirms

	
FatCow 

	
techhosting.com



Configuring IIS 6.0


Please check:



http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/0a199196-4ae9-41eb-b8c1-572251f9f550.mspx?mfr=true



It is a very good resource



Check the following link to know in 2003 server that runs IIS what services should be enabled/disabled



http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/eb255518-2cc7-4972-8dd9-40bbaa7ca331.mspx?mfr=true




Common Administrative Tasks:
http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/f504d2a6-be2a-49fa-b3e9-931ab1671bf1.mspx?mfr=true


WebLogic and WebSpheres



I was just checking about weblogic and websphere. Some resources that I went through:


   Compare Weblogic and Websphere
 Book: BEA Weblogic Server Bible
 Websphere Tutorial

Note: Sometimes knowing information like where to get the information is important especially when you know if you read, you will understand, and if you understand, you will be able to implement.


Installing Apache Tomcat 6.0: Windows



   Make sure you have installed J2SE 5 (both the jdk and the jre) or greater (for tomcat 6.0, j2se5 is required according to my experience)
  
Set the JAVA_HOME environment variable to the path of 
      the JDK
  
 Download Tomcat 6 from http://tomcat.apache.org/download-60.cgi
  
 Click on the downloaded setup.exe file
  
 Select Core+service+examples i.e. full install
  
 When the installer asks for the JRE, provide the path for jre 5 (jre 6 did not work for me)
  
 For user name and password I kept user name to be admin password to be blank [you can also set one]
  
 Next, Next, Finish - tomcat will be installed and start running [if the option was selected]
  
 Set the CATALINA_HOME environment variable to the installation path of the Tomcat 6.0 - it is important for deploying J2EE web applications under Tomcat



Normalization (1NF to 5th NF)


Please check this link.

http://www.keithjbrown.co.uk/vworks/mysql/mysql_p7.php

DBMS System Online Course


Please check the resources at:

http://www.cs.sfu.ca/CC/354/zaiane/material/notes/contents.html



Just like an undergraduate course in Database Management Systems. Along with other topic, ER-Diagram and representation of ER-Diagrams into tables are provided. Some of the complex forms of ER diagrams are discussed.


Practical Database Design Resources



http://www.dbpd.com/vault/9804date.htm
http://cs.senecac.on.ca/~dbs201/pages/Normalization_Practice.htm
http://cisnet.baruch.cuny.edu/holowczak/classes/3400/norm_ex.html
http://thecommunityengine.com/bit320/syllabus/
http://www.ibm.com/developerworks/web/library/wa-dbdsgn2.html
http://www.phpbuilder.com/columns/barry20000731.php3
http://www.devx.com/ibm/Article/20859
http://www-128.ibm.com/developerworks/web/library/wa-dbdsgn1.html
http://www.devx.com/ibm/Article/20702



Normalization in Relational DBMS Systems



First Normal Form:


All table columns should have distinct meaning

In another word: All attributes of an entity/table must be unique

Solution: 

Grow the table row wise[in the direction of rows] not the column wise
Group the columns with same meaning into one
To handle multiple values for the same attribute of an entity create multiple rows [not multiple columns]

To keep track of paycheques, you may think the table should be as follows:

Name, provider company, date 1, pay cheque 1, date2 , pay cheque 2, date 3, paycheque 3

See date1, date2, and date 3 have the same meaning
Pay cheque 1, Pay cheque 2, and Pay cheque 3 have the same meaning
These are the repeating attributes

Solution:
create table with:

number name, company, date,   paycheque
100    1,    xyz,      12/13,  1000.00
100    1,    abc,      12/13,  1000.00
100    1,    xyz,      12/31,  1000.00

or 

create a child entity with Date and amount. And a master entity with number, Name and Company
Entity one: number, name, company
Entity two: number, date, amount


2nd Normal form:
----------------

Rule: 

All attributes can be identified from the primary key. Primary key is directly related to all other attributes.
All attributes are fully dependent on the primary key.

Line Item Table/Entity:
--------------------
number [line item] [pk]
order number[pk]
vendor name
vendor town
product code
product amount

Here, vendor name and vendor town may not be fully dependent on the whole primary key. They are dependent on the order number.
So we can decompose it into another table.

Order entity
Number [pk]
Vendor Name
Vendor Town

Lineitem entity

number [pk]
order_number [pk]
product code
product amount


3rd normal form
-----------------
Rule: Non key attributes are fully dependent on the primary key but not on any other key or attribute.
In Order entity table, vendor town is not fully dependent on number[pk], but it does depend on the vendor name. So we can decompose order entity table into two

Vendor Entity
Name [pk]
Town

Order Entity
Number
vendor-name









Boyce/Codd and Fourth Normal Form



Boyce/Codd Normal Form

----------------------

It is just an extension to the third normal form. Third normal form ensures that non key attributes does not depend on any non key attribute but fully depend on the key[primary] attributes.



Boyce codd normal form ensures that non key attributes are fully dependent on the total set of the primary key. Non-key attributes should not 
depend [only] on a subset of the key[primary] attributes.



Fourth Normal Form:

-------------------

It tries to decompose an entity/table into multiple entities/tables when there are multiple independent[not dependent on each other] multivalued attributes in an entity/table.



For example: Consider a table with emp_number, skill name, objective of the employee. Employees may have many skills and also many objectives. Hence, skill_name, and objective are multivalued and also they do not depend on each other. This table will cause many redundant data.Better is to use two tables like:



Emp_skill

Emp_num, Skill Name



Emp_objective

Emp_num, Objective





Logical Data Modeling: Logical Database Design Steps: RDBMS



Logical Data Modeling 

   

       Identify major entities
      
 Determine relationships between entities
      
 Determine primary and alternate keys
      
 Determine foreign keys
      
 Determine key business rules
      
 Add remaining attributes
      
 Validate user views through normalization
      
 Determine domains
      
 Determine triggering operations
      
 Combine user views
      
 Integrate with existing data models
      
 Analyze for stability and growth    
   

Translate Logical Model into the Real Database System

    Identify tables
   
 Identify columns
   
 Adapt data structure to product environment
   
 Design for business rules about entities
   
 Design for business rules about relationships
   
 Design for additional business rules about 
        attributes 
   
 Tune for scan efficiency
   
 Define clustering sequences
   
 Define hash keys
   
 Add indexes
   
 Add duplicate data
   
 Redefine columns
   
 Redefine tables


Design for Special Design Challenges

   Provide for access through views
  
 Establish security
  
 Cope with very large databases
  
 Access and accommodate change
  
 Anticipate relational technology evolution

Reference: C. C. Fleming and B. V. Halle




Firebird as an Alternative to Oracle/MS SQL Server/Interbase/Access


Note:



Firebird can be a very good alternatives to Oracle/MSSql Server/Interbase. First of all, it is free. It can be good enough for upto medium level of enterprises. Supported database size is more than 11 terabytes. For a single table 20GB. It also implements ACID properties well enough. Implements two phase commit and in record level locking and hence can provide more concurrency and connection.

It also implements MGA to provide recovery that also Oracle/MSSQL server imitated from interbase/firebird.

A well designed database and a well designed network using Firebird will be strong enough to support a mid - size company. Also, if the database is designed well and used the clean normalization, and used clean indexing, the query will be faster enough.

Linux platform is more suitable than windows platform for Firebird.

In 32 bit environment, Firebird server version support 2 GB of ram and hence around 450 concurrent connection. This is due to memory addressing in 32 environment, required cache size and for similar reasons.

In Linux 64 bit versions Firebird can provide better service and more concurrent connections. Firebird is not stable and reliable enough to be used with windows platform in 64 bit environment. Though, Firebird project, hopes to work on this.

Firebird, has a dedicated community to support the product. They provide supports through a discussion forum. (May be .. with some other ways). Firebird team also hopes to continue the project to fullfill future demand.

Being opensource project the database system seems will be strong enough and well designed and well featured to meet customer demand.

For details please check:


A detail discussion can be found here.
http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_enterprise_firebird


Note:
Firebird can be a good option for a Midsize company in Bangladesh


Features of Firebird


What is firebird:

The software has two main components: the database server, which lives on the same host machine as the databases, and the 

application interface, commonly referred to as �the client library�. The client runs in the client pc or in the middleware 

for accessing through internet browsers.

The server's executable is less than 1.5 Mb and a full server installation, including all tools and documentation, takes 

up less than 10 Mb. 


Versions:

1.5 most stable and tested

firebird 2: Mid of year  2006

Vulcan: A parallel project used by SAS to move many of its business applications from Oracle to Firebird. 
Purpose:  redesigning the threading architecture of the database engine


Is Firebird �Enterprise Capable�?
stability, scalability, availability, capacity, interoperability and autonomy.

Stability


Stable enough, supports strongly ACID properties

Scalable??

Good enough but not extremely scalable

Firebird's upscaling is merely a question of adapting the environment. The same engine comfortably handles anything from 

being embedded in a stand-alone client application, through to a classical two-tier client/server LAN of around 750 

potential users, to incorporation in a multi-tier solution for thousands of potential clients. Database growth is 

effectively limited only by the disk storage available and can be split across multiple hard disks.

Through smart replication and good connection management in the access layers, the workload of a busy system can be 

distributed across multiple servers. For example, a well-resourced central server can handle the interactive demands of 

LAN, intranet or extranet (or all together) while a replicated server takes care of long-running jobs that need to isolate 

a snapshot of data for lengthy periods.

Availability

Among its users, Firebird has a reputation for being bomb-proof.


It uses optimistic locking at record level, drastically reducing the wait-time overheads in comparison to others where 

read-write transactions lock entire sets, even tables, pre-emptively. No tuning is ever required to facilitate handling 

varying workloads through the day or week. A database does not have to be shut down for backups. It can be replicated or 

shadowed for almost instantaneous cutover in the event of disk failure. It is robust and recovers immediately from power 

failure, without loss of database integrity.


Supports online backup.

Firebird is a popular choice for enterprises needing continuity of service around the clock. Command-line tools are 

distributed with the software for all administrative activities, allowing regular housekeeping to be automated as 

scheduled or on-demand jobs. A Services API is also available to wrap admin tasks into a program or service application.



Firebird Databases as the Back-end to Enterprise Software Systems 



Who Uses Firebird?
Because Firebird is free, there are no licences to count, no beans to count. It is known, from reputable enterprise surveys, that Firebird is chugging away on hundreds of thousands of production sites around the world. The following is a selection of companies and organisations that are publicly known to be using Firebird:

Broadview Software Ltd, Toronto, Canada, vendor of information and control systems and online services for broadcasters worldwide

Morfik P/L, Hobart, Tas., developers and vendors of WebOS development suite for construction and maintenance of interactive websites, stores web objects in a Firebird meta-layer (system database) as well as Firebird user data.

Communicare Systems Pty Ltd, Perth, WA, vendor of patient management and medical records software for hospitals, clinics, medical practices and mobile health units across Australia.

�The Examiner� newspaper, Launceston, Tas., high availability(24/7) business, information, production and news systems.

U.S. Navy, broad range of management and logistical systems

Frontrange Solutions USA Inc., Colorado Springs, U.S.A, as the back-end of the highly scalable, award-winning integrated CRM, service management and business systems �Goldmine� software suite.

British Rail, U.K., timetabling, bookings, accounting and information systems for national railway passenger network. 

Deutsche Presse-Agentur GmbH, HQ in Hamburg, Germany, largest press agency in Germany, provides a worldwide service to newspapers, magazines, TV and radio news networks.

KIMData, Munich, Germany, business intelligence systems and data warehousing for German hospitals.


Comparison: FireBird, MySQL, PostGreSQL


PosTGRESQL Provides better query responsetime than firebird and MySql. Firebird stands somewhere in the middle. A 

little longer response time than PostGreSql.


http://benchw.sourceforge.net/benchw_results_open3.html


Several PostGreSQL versions are compared at:


http://benchw.sourceforge.net/benchw_results_postgres_history.html

Several MySQL versions are compared at:


http://benchw.sourceforge.net/benchw_results_mysql_indexes.html

A good comparison among these databases will be found at:

http://www.geocities.com/mailsoftware42/db/



Mentionable comments

PostGreSQL Supports 400+GB DB.
One source: As for each connection it forks new thread, it is bit slower than MySql: Another source: postgresql is faster 

than mysql. Seems the truth is: for single connection postgresql can be faster but for multiple connections mysql is 

faster. just an information, not sure yet.

New PostGre Features
http://www.postgresql.org/docs/whatsnew

Some mentionable comments: Supports SMP and almost linearly improves performance witth multiprocessor system.

Supports roles, in/out parameters/two phase commit/table partitioning shared row locking(firebird record level 

locking), 64 bit shared memory hence supports two terabytes of ram


PostGreSQL capability

4.4) What is the maximum size for a row, a table, and a database?
These are the limits:


Maximum size for a database? unlimited (32 TB databases exist) 

Maximum size for a table? 32 TB 

Maximum size for a row? 400 GB 

Maximum size for a field? 1 GB 

Maximum number of rows in a table? unlimited 

Maximum number of columns in a table? 250-1600 depending on column types 

Maximum number of indexes on a table? unlimited 


One limitation is that indexes can not be created on columns longer than about 2,000 characters. Fortunately, such indexes 

are rarely needed. Uniqueness is best guaranteed by a function index of an MD5 hash of the long column, and full text 

indexing allows for searching of words within the column.


Source:

http://www.postgresql.org/docs/faqs.FAQ.html



4.20) What replication solutions are available? 
Though "replication" is a single term, there are several technologies for doing replication, with advantages and 

disadvantages for each.


Master/slave replication allows a single master to receive read/write queries, while slaves can only accept read/SELECT 

queries. The most popular freely available master-slave PostgreSQL replication solution is Slony-I.


Multi-master replication allows read/write queries to be sent to multiple replicated computers. This capability also has a 

severe impact on performance due to the need to synchronize changes between servers. PGCluster is the most popular such 

solution freely available for PostgreSQL.


There are also commercial and hardware-based replication solutions available supporting a variety of replication 

models.



Important Notes
Server Configuration 
A number of postgresql.conf settings affect performance. For more details, see Administration Guide/Server Run-time 

Environment/Run-time Configuration for a full listing, and for commentary see 

http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html and 

http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html. 



Hardware Selection 

The effect of hardware on performance is detailed in http://www.powerpostgresql.com/PerfList/ and 

http://momjian.us/main/writings/pgsql/hw_performance/index.html. 




Wikipedia link for postgressql-http://en.wikipedia.org/wiki/PostgreSQL

EnterpriseDB kind of commercial but cheap based on postgressql 



PostGreSQL Limitations and solutions


http://searchopensource.techtarget.com/originalContent/0,289142,sid39_gci1172668,00.html

PostGreSQL Windows Installer
http://pgfoundry.org/projects/pginstaller/


Important Links for Mysql, PostGreSQL, Firebird, EnterpriseDB, Ingres


Firebird and postgresql:
http://www.linuxjournal.com/node/7010 


Important comment: easy to migrate from oracle to firebird than to postgre or mysql. From interbase to firebird will be better as they are similar.



http://forums.devshed.com/firebird-sql-development-61/mysql--vs--firebird-sql-62269.html : Firebird is far better choice than mysql


As for Performance, MySQL will outperform Firebird on almost all tests on local machine......


On a Network Server a properly designed Firebird Server will outperform MySQL on any Test and the network-Traffic generated by Mysql will be many times higher than Firebird

So you decide the one you need according to your needs .....



This link also compares fb with potgre and favors fb as the author's background is borland

Why Postgress is  better than firebird

Firebird is not as good as PostgresQL (Score:0) 
By Anonymous Reader on 2004.12.03 2:21 (#102703) 
And here is why:

1. Firebird has no temp table support.
2. Firebird uses several SQL modes i.e DSQL which only works client side and PSQL which only works in procs. All sql works in Postgres functions.
Try creating a table or a user in a Firebird proc...ooops can't be done.
3. Firebird databases grow out of control and have to be backed up restored on a regular basis.
4. Firebird has a concept of a OAT (oldest active transaction counter) if this counter gets stuck all transactions get held up and your DB size goes through the roof and performance goes to the basement.
5. Firebird has virtually no built in functions and flakey UDFs must be used. Postgres on the other hand has every function you can think of.
6. Postgres has many rich procedural languages including perl,TCL,Java and C# in the works.

I could go on.... Version 8 of Postgres which will be released soon adds point in time recovery, Try except error handling in functions, table spaces and more.

I converted all my apps from Firebird 1.5 to Postgresql 7.4 and I would never use Firebird again on Unix or Windows 


Why PostGress better than FireBird: This comparison may not be accurate though.


Postgres Vs Interbase/Firebird (Score:0) 
By Anonymous Reader on 2004.12.03 18:23 (#102740) 
http://www.vitavoom.com/postgresql.html

Firebird has not true text type you must use the clumsy blobs and weird blob subtypes.

Postgres is much closer for enterprise use than Firebird.

almost all of this applies to Firebird as well as interbase.

PostgreSQL vs Interbase
PostgreSQL...

        * ... has no "dialects" or other weird features.
        * ... has no "backdoors" which can compromise your whole company's security and business (check here and here).
        * ... supports unlimited row sizes, unlimited datasebase sizes, tables up to 16TB (!!), unlimited number of rows, unlimited indexes for table and 1Gb per field.
        * ... has a boolean field.
        * ... can cancel a query asynchronously (Interbase 6.5 - the commercial version - seems to support it now).
        * ... has a flexible (not fixed) types system, and supports more types then Interbase. You can always add your own types at your wish, or change existent types behavior. PostgreSQL even has geometrical and IPv4 types support (!!).
        * ... supports inheritance.
        * .. supports flexible full text indexing through OpenFTS.
        * ... has a much more sofisticated locking mechanism (MVCC).
        * ... has arbitrary precision numeric fileds (numeric type).
        * ... is resistant to crashes and power failures (by using it's logging system, MVCC and chepoint)). Although it is technically possible that a database gets corrupt, we at Vita voom never saw it (except for corrupt media storage of course).
        * ... supports functions (whose can be used as stored procedures). These functions current can be written on SQL, pl/PgSQL (a language similar to Oracle's pl/SQL), TCL, Perl, and Python, C, C++ (or other compiled languages) but it's not limited to them.
        * ... can have functions to define default values for columns (providing ultimate default value flexibility).
        * ... supports much more arrays types then Interbase, they are more flexible and can support much more elements.
        * ... supports rules.
        * ... supports "fetch" SQL command to get only a limited number of rows at a time, making queries more responsive and resource economic.
        * ... has regular expressions support (for searches and operations).
        * ... has a 'EXPLAIN' command which will show will how it will perform a query, so that you optimize it.
        * ... has statistics about database usage which can be used to optimize queries and indices.
        * ... has sequences.
        * ... has more built-in functions then Interbase (or any other open source RDBMS).
        * ... supports indexes on functions.
        * ... has broader subselects support then Interbase.
        * ... has a more flexible BLOB fields support.
        * ... can limit the number of rows retrieved at at time (with the 'LIMIT' keyword).
        * ... is more standards-compliant then Interbase.
        * ... allo 


Speed: Firebird vs. PostGreSql


Please read.
According to this article postgresql faster than firebird. But I also got information/tests where firebird is faster than postgresql.

http://archives.postgresql.org/pgsql-advocacy/2003-06/msg00280.php

However,  Speed may not be the only factor to select a database product. We can improve raw speed in many alternate ways, hardware solutions, ram, faster storage, faster CPU. To compare speed issue, efficiency issue, we need to take a look how the query optimizations are implemented in databases. How, the joins, inner joins, outer joins are implemented. How multiple joins are implemented. Also, indexing is a factor that also affects performance issue. We can try creating same database and using a queries that are simple to complex and run on both databases and see, how well they perform.

Postgresql   supports SMP (multiple processor system) and provides better efficiency in multiprocessor systems. Firebird, is not still matured in SMP environment.

 Firebird supports 32 bit environment, still not stable/reliable enough for 64 bit env. It supports max ram of 2 gb and hence 150 -  450 concurrent users. Postgresql supports 2 TB of ram in a single server. For both of them, we can implement multiple servers with master/slave relation for better performances. 

Tools to create ER diagrams from a database dynamically



Microsoft Visio, and dbVisualizer provide features to create ER diagrams from existing database tables.

Why needed?
ER diagram is very useful to understand the total
database structure that helps a new programmer to work on the existing databases more efficiently.

Visio:
While creating Database diagrams, a menu item named database is displayed. Reverse Engineering option under Database menu helps to create ER diagram from 

existing database tables. Before that, target database drivers for visio need to be installed. If the database is in db2/mysql/postgresql, then  

db2/mysql/postgresql drivers for visio should be installed. From Database/options/drivers, the driver needs to be configured. Afterwards, Reverse Engineering 

option may be used to create the ER diagram. There is no suitable driver for postgresql databses for Visio. ODBC drivers may be used for the purpose (the ER 

diagram may not be accurate from ODBC driver for postgresql). 

dbVisualizer:
In dbVisualizer, when the TABLEs group/option is selected in the left pane in the right/middle pane there will be a references option. When the references 

tab is selected, an ER diagram is displayed using all tables. The ER diagram can be displayed as Hierarchical, circular, orthogonal, or organic. There are 

options for the purpose.

When a single table name is selected, the table and the associated tables are displyed in an ER diagram.

In the rightest column, selected tables option allows to create ER diagrams only for the selected tables. When 'selected tables' is clicked a list is 

displayed in a dialogbox. Tables can be selected from this list (use control key). Build graph  option creates and displays the ER diagram.



MySql Administration SQL Commands


Using MySQL, Administration
Workshop Requirements
You should have access to the MySQL command line client software.

Various different PRIVILEGES on the MySQL Server

Introduction
In the other MySQL Virtual Workshops we have used commands that are pretty much applicable to anyone. This part of the MySQL series is aimed at giving a rudimentary understanding of managing a MySQL database server. As such the task covered here are not really about manipulating data or database structures, but the actual databases themselves.

Creating a Database
In order to create a database you need to have the PRIVILEGES- this may be because you are the root user or you (or you systems administrator) has created an admin user that has ALL PRIVILEGES over all databases. In these examples a user called 'admin' has been created precisely for this purpose. Creating a database is fairly straightforward.

Logging In
A reminder of how to start the MySQL Client Software, and as we are not concerned with manipulating just one database we don't have to specify a database as part of our startup command.

$ mysql -u  -p
Enter password:Create database command
Next we are ready to enter the very simple command to create a database which is:

mysql> CREATE DATABASE ; Let's imagine that we are going to create a 'vworks' database (those wishing to create a database for use with the VWs should use this). We would enter the command:

mysql> CREATE DATABASE vworks; We can now check for the presence of this database by typing:

mysql> SHOW DATABASES;
+-----------+
| Database  |
+-----------+
| mysql     |
| vworks    |
+-----------+
2 rows in set (0.06 sec)The other database listed ('mysql') is the internal database which MySQL uses to manage users, permissions etc. 

NOTE: Deleting or DROPing a database is similar to the DROP TABLE command issued in Part 4. e.g.

DROP DATABASE Granting Privileges on the new database
Now that we have created a database, we need to decide who gets to use it. This is done by granting permissions for a user to use the database. This has a simplified syntax of:

GRANT  
ON  
TO  
[IDENTIFIED BY ] 
[WITH GRANT OPTION]Where the items in square brackets are optional. The most common use is to give ALL PRIVILEGES on a database to a local user who has to use a password to access the database (in this case vworks).

mysql> GRANT ALL PRIVILEGES 
    -> ON vworks.* 
    -> TO newuser@localhost 
    -> IDENTIFIED BY 'newpassword';  If you are creating a database for use with the rest of the Virtual Workshops you should use this statement, substituting your username and password of choice. There are some other options we will look at. To restrict the user to manipulating data (rather than table or database structures) the statement would be altered to:

mysql> GRANT SELECT,INSERT,UPDATE,DELETE 
    -> ON vworks.* 
    -> TO newuser@localhost 
    -> IDENTIFIED BY 'newpassword';  So that the user can only change the data using SELECT,INSERT,UPDATE or DELETE statements. If you wished to give a non-local user permissions on the database (for use with remote clients) then you could designate an IP or host address from which the user can connect:

mysql> GRANT ALL PRIVILEGES 
    -> ON vworks.* 
    -> TO newuser@192.168.0.2 
    -> IDENTIFIED BY 'newpassword';  Now a user on the machine '192.168.0.2' can connect to the database. To allow a user to connect from anywhere you would use a wildcard '%'

mysql> GRANT ALL PRIVILEGES 
    -> ON vworks.* 
    -> TO newuser@'%' 
    -> IDENTIFIED BY 'newpassword';You could even decide that a user doesn't need a password if connecting from a certain machine. 

mysql> GRANT ALL PRIVILEGES 
    -> ON vworks.* 
    -> TO newuser@192.168.0.2But I think it is sometimes good to provide a password anyway. Finally we'll look at the WITH GRANT OPTION condition. This allows the user to give others privileges to that database:

mysql> GRANT ALL PRIVILEGES 
    -> ON vworks.* 
    -> TO newuser@localhost 
    -> IDENTIFIED BY 'newpassword' 
    -> WITH GRANT OPTION;  This would allow the user 'newuser' to log into the database and give their friend privileges to SELECT,INSERT,UPDATE or DELETE from the database.

mysql> GRANT SELECT,INSERT,UPDATE,DELETE 
    -> ON vworks.* 
    -> TO friend@localhost 
    -> IDENTIFIED BY 'friendpass';  The WITH GRANT OPTION usually signifies ownership although it is worth noting that no user can GRANT more privileges that they themselves possess.

Revoking privileges
Revoking privileges is almost identical to granting them as you simply substitute RE VOKE.... FROM for GRANT....TO and omit any passwords or other options.

For example to REVOKE the privileges assigned to a user called 'badvworks':

mysql> REVOKE ALL PRIVILEGES 
    -> ON vworks.* 
    -> FROM badvworks@localhost;  Or just to remove UPDATE, INSERT and DELETE privileges to that data cannot be changed.

mysql> REVOKE INSERT,UPDATE,DELETE 
    -> ON vworks.* 
    -> FROM badvworks@localhost;  Backing Up Data
There are several methods we can use to backup data. We are going to look at a couple of utilities that come with MySQL: mysqlhotcopy and mysqldump. 

mysqlhotcopy
mysqlhotcopy is a command line utility written in Perl that backs up (to a location you specify) the files which make up a database. You could do this manually, but mysqlhotcopy has the advantage of combining several different commands that lock the tables etc to prevent data corruption. The syntax (as ever) first.

$ mysqlhotcopy -u  -p  /backup/location/Which SHOULD copy all the tables (*.frm, *.MYI, *.MYD) into the new directory - the script does require the DBI perl module though. To restore these backup files simply copy them back into your MySQL data directory. 

mysqldump
This is my preferred method of backing up. This outputs the table structure and data in series of SQL commands stored in a text file. The simplified syntax is

$ mysqldump -u  -p  [] > file.sqlSo for example to back up a 'vworks' database which may have been created by completing the workshops:

$ mysqldump -u admin -p vworks > vworks.sql
After entering the password a 'vworks.sql' file should be created. When you look at this file you can actually see that the data and structures are stored as a series of SQL statements. e.g.:

-- MySQL dump 8.22
--
-- Host: localhost    Database: vworks
---------------------------------------------------------
-- Server version       3.23.52

--
-- Table structure for table 'artist'
--

CREATE TABLE artist (
  artistID int(3) NOT NULL auto _increment,
  name varchar(20) default NULL,
  PRIMARY KEY  (artistID)
) TYPE=MyISAM;

--
-- Dumping data for table 'artist'
--


INSERT INTO artist VALUES (1,'Jamiroquai');
INSERT INTO artist VALUES (2,'Various');
INSERT INTO artist VALUES (3,'westlife');
INSERT INTO artist VALUES (4,'Various');
INSERT INTO artist VALUES (5,'Abba');
And so on for the other tables. 

We could also have chosen to output just one table from the database, for example the artist table:

$ mysqldump -u admin -p vworks artist > artist.sqlWe could even dump all the databases out (providing we have the permissions).

$ mysqldump -u admin -p --all-databases > alldb.sqlRestoring a Dump
Restoring a dump depends on what you have actually dumped. For example to restore a database to a blank database (perhaps having transferred the sql file to another machine) it is fairly simple. 

$ mysql -u admin -p vworks < vworks.sql...or to add a non-existent table to a database...

$ mysql -u admin -p vworks < artist.sqlHowever, what happens if we want to restore data to an existing database (perhaps a nightly backup) ? Well we would have to add other options:

The equivalent of overwriting the existing tables would be telling the dump to automatically drop any tables that exist before restoring the stored tables. This is done with the ' --add-drop-table ' option added to our statement.

$ mysqldump -u admin -p --add-drop-table vworks > vworks.sqlThen restore like normal:

$ mysql -u admin -p vworks < vworks.sqlThe reverse might also be true. We may wish to create the database if it doesn't already exist. To do this we use the '--databases' option to specify the database we wish to back up (you can specify more than one).

$ mysqldump -u admin -p --databases vworks > vworksDB.sqlThis will create additional SQL statements at the start of each database that CREATEs the dumped database (checking first to see if it does indeed exist) then USEing that database to import the table data into.

-- Current Database: vworks
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ vworks;

USE vworks;
Again we can resort like normal, but of course this time we can omit the database name.

$ mysql -u admin -p < vworksDB.sqlOptimising a dump
There are a couple of options that are sometimes worth including when backing up and restoring large databases. 

The first option is '--opt', this is used override the mysql server's normal method of reading the whole result set into memory giving a faster dump. Example:

$ mysqldump -u admin -p --opt vworks > vworks.sqlThe second option is '-a' or '-all' (either will do). Which also optimises the dump by creating mysql specific CREATE statements that speeds up the restore:

$ mysqldump -u admin -p --all vworks > vworks.sqlUsing mysqldump to copy databases.
It is possible to combine a dump and a restore on one line by using a pipe '|' to pass the output of the dump directly to mysql basically bypassing the file. This may initially seem a bit redundant, but we can use this method to copy a database to another server or even create a duplicate copy.

For example to copy the 'vworks' database to a mysql server called 'remote.server.com':

$ mysqldump -u admin -p --databases vworks | \
> mysql -u backup -p MyPassword -h remote.server.com Note: the"\" at the end of the first line means you wish to contine the command on another line before executing it. 

You may, in certain circumstances, wish to make a copy of live data so that you can test new scripts and 'real world' data. To do this you would need to duplicate a local database. First create the duplicate database:

mysql> CREATE DATABASE vworks2; Then once appropriate privileges have been assigned we can copy the tables from the first table into the second.

$ mysqldump -u admin -p vworks | mysql -u backup -p MyPassword vworks2Notice in both these examples the second half of the line (after the pipe) passes the password as part of the connection statement. This is because asking for two separate passwords at the same time breaks most shells. That is why I have used a 'backup' user who can be granted permissions and have them revoked as necessary. 

Miscellaneous Leftovers
This final bit includes a few brief tricks that weren't really appropriate to include elsewhere, but are still worth noting.

Remote Client Connection
If you have set the privileges to allow remote connections to a database, you can connect from a remote command line client by using the -h flag:

$ mysql -u  -p -h For example to connect to a fictional vworks.keithjbrown.co.uk server:

$ mysql -u admin -p -h vworks.keithjbrown.co.ukNon-Interactive Commands
Sometimes you may wish to just do a quick look up on a table without the hassle of logging into the client, running the query then logging back out again. You can instead just type one line using the ' -e ' flag. For example:

$ mysql -u admin -p vworks -e 'SELECT cds.artist, cds.title FROM cds'

Enter password: 
+------------+------------------------------+
| artist     | title                        |
+------------+------------------------------+
| Jamiroquai | A Funk Odyssey               |
| Various    | Now 49                       |
| westlife   | westlife                     |
| Various    | Eurovision Song contest 2001 |
| Abba       | Abbas Greatest Hits          |
+------------+------------------------------+


Securing MySQL Database


After installing mysql, we will need to remove test database and associated users and their permissions. We will need to use mysql database to remove associated users and permission.







--
DROP DATABASE test; 




--
SELECT db.Host, db.Db, db.User, db.Select_priv 
    -> FROM db WHERE (db.DB = "vworksDB");




---
SELECT db.User, db.Host, db.Db 
    -> FROM db 
    -> WHERE (db.Db LIKE 'test%');




---
DELETE FROM db 


    -> WHERE (db.Db LIKE 'test%');


---


mysql> DELETE FROM db 


    -> WHERE (db.Host = "%");




mysql> DELETE FROM db 


    -> WHERE (db.User = "");


---
SELECT user.Host, user.User 


    -> FROM user 


    -> WHERE ((user.Host = "%") OR (user.User = ""));


---


DELETE FROM user 
    -> WHERE ((user.Host = "%") OR (user.User = ""));
---


FLUSH PRIVILEGES;
---


details: http://www.keithjbrown.co.uk/vworks/mysql/mysql_pA.php#secure

Joins in MySQL


Cross-Join: All row by all row, ex: SELECT  
FROM , 



The Equi-Join or Inner Join:
Syntax: 


SELECT  

FROM ,  

WHERE (Table1.column = Table2.column)





SELECT cds.artist, cds.title, genres.genre 

    -> FROM cds, genres 

    -> WHERE (cds.genreID = genres.genreID);





The Left Join:


Syntax:




SELECT  

FROM  

LEFT JOIN  

ON Table1.column = Table2.column



Without adding a WHERE clause the Left Join produces the same results as the equi-join example above.


mysql> SELECT cds.artist, cds.title, genres.genre 

    -> FROM cds 

    -> LEFT JOIN genres 

    -> ON cds.genreID = genres.genreID; 



The USING Clause: You can use this if the columns you are carrying out the join on have the same name. 


Syntax:



SELECT  

FROM  

LEFT JOIN  

USING ()





UPDATE JOIN:


mysql> UPDATE cds, artists 

    -> SET 

    -> cds.title = 'The Funkship Odyssey', 

    -> artists.Artist = 'George Clinton'

    -> WHERE (artists.artistID = cds.artistID) 

    -> AND (cds.cdID = '2');







UPDATE cds->LEFT JOIN artists 

    -> ON cds.artistID = artists.artistID

    -> SET 

    -> cds.title = 'A Funk Odyssey', 

    -> artists.name = 'Jamiroquai'

    -> WHERE (cds.cdID = '2');





DELETE Joins:


mysql> DELETE cds 

    -> FROM cds, artists 

    -> WHERE (cds.artistID = artists.artistID) 

    -> AND (cds.artistID = '3');







MySQL Resources


Mysql Resources:
First you need the latest/stable version of MySQL. Right now 6.0 is the latest version. Mysql has two different releases like:

community server[free] 

enterprise server[commercial]



Note: MAXDB is the current full package with many features that may not be fully tested. 



Download MySQL:
You can download MySQL from



http://dev.mysql.com/downloads/mysql/5.0.html 



Check the lower portion of the webpage.



Working with Mysql:
After installation you can interact with MySQL server through command line[Best to become guru]. But you may also want to use GUI IDE to interact with. GUI tools can be downloaded from:



http://dev.mysql.com/downloads/gui-tools/5.0.html 





GUI are of two types: to administer MySQL server. Used for backup, restore, security 



Development: For creating Database and interact with database using tables, Queries, Stored procedures, triggers






Application Development:

You can build appications that use MySQL databases at the backend. You can use languages like PHP, Perl, Java, .net to interact with MySQL databases. Usually, the package for these languages contain a driver to provide the functionalities. Otherwise, you can download drivers from:



http://dev.mysql.com/downloads/connector/ 



for many different programming languages like Java, PHP, Perl and similar.




MySQL Resources: MySQL Start


Mysql Resources:
First you need the latest/stable version of MySQL. Right now 6.0 is the latest version. Mysql has two different releases like:

community server[free] 

enterprise server[commercial]



Note: MAXDB is the current full package with many features that may not be fully tested. 



Download MySQL:
You can download MySQL from



http://dev.mysql.com/downloads/mysql/5.0.html 



Check the lower portion of the webpage.



Working with Mysql:
After installation you can interact with MySQL server through command line[Best to become guru]. But you may also want to use GUI IDE to interact with. GUI tools can be downloaded from:



http://dev.mysql.com/downloads/gui-tools/5.0.html 





GUI are of two types: to administer MySQL server. Used for backup, restore, security 



Development: For creating Database and interact with database using tables, Queries, Stored procedures, triggers






Application Development:

You can build appications that use MySQL databases at the backend. You can use languages like PHP, Perl, Java, .net to interact with MySQL databases. Usually, the package for these languages contain a driver to provide the functionalities. Otherwise, you can download drivers from:



http://dev.mysql.com/downloads/connector/ 



for many different programming languages like Java, PHP, Perl and similar.




Stored Procedure in MySql


Starting from MySQL 5, you get Stored Procedure in Mysql

What is a stored procedure: A stored procedure is simply a procedure that is stored on the database server like MySQL. In programming languages, you write procedures to execute a function/logic. You can write similar procedure in SQL and store it in the database. From the front end application you can just call the procedure to get the functionality. Usually, you send series of sqls to the databases to execute a logic. A stored procedure is better as it needs one single call. But not every logic can/should be implemented as stored procedures.

 Sample stored procedure

CREATE PROCEDURE sp_hello() 

SELECT 'Hello World';




 It just creates a procedure. To run the procedure, you have to type 


call sp_hello; 


You will see 'Hello World' as output.

Stored procedures can also take parameters and return values. Both needs to be mentioned as parameter. In the parameter list we can declare a variable as IN, OUT, or INOUT parameter. You can use SQLs[insert,select] inside procedure to perform database operations. Also, you can use to set/unset session variables.

Session variable example

SET @X=100;

CREATE PROCEDURE sp_in(p VARCHAR(10))

SET @x = P;


call sp_in(1000);
 You see 1000 in screen

A more practical stored procedure with multiple statements

mysql> DELIMITER |
mysql> CREATE PROCEDURE sp_declare (P INT)
    -> BEGIN
    ->  DECLARE x INT;
    ->  DECLARE y INT DEFAULT 10;
    ->  SET x = P*y;
    ->  INSERT INTO sp1(id,txt) VALUES(x,HEX('DEF'));
    -> END|

Query OK, 0 rows affected (0.00 sec)
 
mysql> DELIMITER ;
mysql> CALL sp_declare(4);

 please, note the use of | and DELIMETER. They provide support so that you can use ; in your procedures.

Some stored procedure related SQL commands
SHOW PROCEDURE STATUS
SHOW CREATE PROCEDURE Hello
SELECT * FROM INFORMATION_SCHEMA.ROUTINES [Ansi standard]

Thanks
Sayed

MySQL Reference Manual



    MySQL Reference Manual


MySQL: Special Interests



 

     Optimization
    
 MySQL Load Balancher
    
 MySQL Replication
    
 MySQL Cluster
    
 Stored Procedures and Functions
    
 MySQL High Availability and Scalability

Complete Web Development Guide by JustEtc Publications Ltd

Distributed by justEtc

Layouts with CSS: Layout without tables

Two column webpage layout with CSS

Your two column webpage will look like

Three column webpage with CSS

Table Less Design: Layouts with CSS

More CSS Layouts:

Web page design standards

What you really need to learn in Javascript?

How to implement multiple tab webpages in Ajax and Javascript

Handling Checkbox arrays with Javascript::GetElementsByName:XHTML supported

Javascript by Example

Javascript - Form validation

JavaScript - Miscellaneous Validations

Javascript Codes

Introduction to JQuery

JavaScript DOM: Must knowledge to understand Ajax

Ajax: An Overview

PHP Security: Coding that Maintains Security

Php Security

Common security risks in PHP[Abdul Basit, php.net]

PHP Security when installed as a CGI Binary[php.net]

PHP and IBM DB2

A Sample PHP Class

MVC for PHP

Creating Custom Tags in JSP

JSP Tags

JSP Expression Language

JSP: Random Information

JSP: Custom Tags

MVC : Struts : Java : Industry Web Application

How to upload data from an excel file to database using servlets?

Servlet, JSP Specifications

JSP: Reference Manuals

Servlet Random Information

Complete path analysis to get a merchant account

Web Hosting Companies

Best Hosting Companies

Configuring IIS 6.0

WebLogic and WebSpheres

Installing Apache Tomcat 6.0: Windows

Normalization (1NF to 5th NF)

DBMS System Online Course

Practical Database Design Resources

Normalization in Relational DBMS Systems

Boyce/Codd and Fourth Normal Form

Logical Data Modeling: Logical Database Design Steps: RDBMS

Firebird as an Alternative to Oracle/MS SQL Server/Interbase/Access

Features of Firebird

What is firebird:

Versions:

Is Firebird �Enterprise Capable�?

Firebird Databases as the Back-end to Enterprise Software Systems

Comparison: FireBird, MySQL, PostGreSQL

New PostGre Features

Important Notes

Wikipedia link for postgressql-http://en.wikipedia.org/wiki/PostgreSQL

PostGreSQL Limitations and solutions

Important Links for Mysql, PostGreSQL, Firebird, EnterpriseDB, Ingres

Why Postgress is better than firebird

Why PostGress better than FireBird: This comparison may not be accurate though.

Speed: Firebird vs. PostGreSql

Tools to create ER diagrams from a database dynamically

MySql Administration SQL Commands

Securing MySQL Database

Joins in MySQL

MySQL Resources

Mysql Resources:

Download MySQL:

Working with Mysql:

Application Development:

MySQL Resources: MySQL Start

Mysql Resources:

Download MySQL:

Working with Mysql:

Application Development:

Stored Procedure in MySql

Some stored procedure related SQL commands

MySQL Reference Manual

Complete Web Development Guide

by JustEtc Publications Ltd