One to One relationship example in database(oracle, MYSQL, SQl, Ms access)

In this Article, I will explain how to create one to one relationship between two tables.In a relational database such as Mysql,SQl,Ms access,  you can create relationships between pairs of tables like..
1.one-to-one
2.one-to-many 
3.many-to-many.

One-to-one relationships in a relational database:

one-to-one relationships are bidirectional relationships.each row in one database table is linked to one and only one other row in another table.
An example of a one-to-one relationship is a Person can have only one Passport and that a Passport can belong to only one Person.
we are designing Database with a One To One Relationship between Person and Passport Table.



You may not use one-to-one relationships very usually, but you should know how to create them.

There are other types of database relationships, one to many and many to many relationships.

MySql queries interview questions and answers - MySql Database Tutorial

MySql queries Interview Questions & Answers
In this section, I have selected advanced MYSQL queries interview questions and answers for all levels of candidates(entry level, advanced level, expert level).Last few months I have been working to select best MYSQL queries for MYSQL interview questions.

This article introduces MYsql Database queries:

Suppose we have to tables 
1.Employee
2.Employee Salary

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `photo` varchar(100) DEFAULT NULL,
  `first_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `gender` varchar(11) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `mobile` varchar(100) DEFAULT NULL,
  `password` varchar(100) DEFAULT NULL,
  `active` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

employees

id   name 
1    Admin
2    Employee1
3    Employee2
4    Employee3
5    Employee4

CREATE TABLE `employee_salary` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `employee_id` int(10) DEFAULT NULL,
  `salary` varchar(50) DEFAULT NULL,
  `created_on` datetime DEFAULT NULL,
  `created_by` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=32 DEFAULT CHARSET=latin1

id employee_id  salary
1    1          10000
2    2          20000
3    3          10000
4    4          40000
5    5          30000 

1. Find Second highest paid employees withou using LIMIT function.

   Query:

   SELECT MAX(salary) FROM  employee_salary WHERE salary <(SELECT MAX(salary) FROM  employee_salary)

2.Skip repeating values

  Query:
  SELECT salary FROM  employee_salary GROUP BY salary

3.Reset Auto-increment next value

  Query:
  ALTER TABLE tablel SET AUTO_INCREMENT=val;

4.Find the Size of database

Query:
SELECT 
s.schema_name AS 'database',
SUM(t.data_length) AS DATA,
SUM( t.index_length ) AS INDEXES,
SUM(t.data_length) + SUM(t.index_length) AS 'Mb Used',
IF(SUM(t.data_free)=0,'',SUM(t.data_free)) AS 'Mb Free',
COUNT(table_name) AS TABLES
FROM information_schema.schemata s
LEFT JOIN information_schema.tables t ON s.schema_name = t.table_schema
GROUP BY s.schema_name  

5.First and Last Date of Month

Last day of next month:

SELECT LAST_DAY (DATE_ADD(CURDATE(), INTERVAL 30 DAY))

Last day of Previous month:

SELECT LAST_DAY (DATE_SUB(CURDATE(), INTERVAL 30 DAY))

First day of next month

SELECT  CONCAT(LEFT(CURDATE() + INTERVAL 1 MONTH, 8), '01'); 


6.Display every Nth row

Query:
SELECT id
FROM employees
GROUP BY id 
HAVING MOD(id, N) = 0; 

7.Age in years

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW()) - TO_DAYS('1991-07-01')), '%Y') + 0;

8.Difference between two dates

SELECT DATEDIFF('2002-07-15','2001-07-15')

9. Update Table using Join

UPDATE employee e LEFT JOIN employee_salary s ON e.id=s.employee_id 
SET s.salary='50000'
where e.first_name='Admin'

10. DELETE Table using Join 

Delete employee_salary FROM employee_salary  LEFT JOIN employee ON employee.id=employee_salary.employee_id 
WHERE  employee.first_name='Admin'

XSS Prevention in PHP Cheat sheet pdf - Cross Site Scripting examples

XSS (Cross Site Scripting) Prevention in PHP


In this Article,I will show how to prevent XSS in PHP. Cross Site Scripting occurs, when an attacker uses a Web Application to send a malicious script(Javascript,VBScript,HTMl Script), Most often Malicious Data comes from the HTTP request.This script can access cookies,sessions and other information stored by your browser.These scripts even change the content of HTML Page.

There are Two types of XSS Attackes:

1.Stored XSS Attackes:  Malicious code permanantly stored on server such as  in Database ,Comment..etc
2.Reflected XSS Attacks: Injected Code reflected off the Web server such as error message,Search results..etc

XSS (Cross Site Scripting) Prevention in PHP Cheat sheet:

- Untrusted User Input Data:
Most often Malicious Data comes from the HTTP request.Never trust data coming from the user.you should not insert or send it anywhere without taking steps to make sure that any attacks are detected .you have to validate all user input data using PHP functions before Insert into database or send it to anywhere.

- Validation:
   Validate all user input data ($_GET, $_POST, $_REQUEST, $_COOKIE) using Regular expressions,Javascript and PHP functions to prevent XSS attacks.

- Some PHP functions that helps you to prevent XSS attacks

1.htmlspecialchars: htmlspecialchars Converts all special characters to HTML entities.(<, >, &, ‘, “.).

2.strip_tags: Used to strip HTML and PHP tags from a string.

3.mysql_real_escape_string: Escapes special characters in a string for use in an MySQL statement

4.Encode URL Query String Parameters.

- You can use htmlentities and stripslashes php functions for Retrieving and Displaying the Data From the Database.

- Use Escape methods (HTML Escape,Javascript Escape,CSS Escape) for XSS (Cross Site Scripting) Prevention.

- Always Use XHTML.


I hope This article will help you to prevent XSS Attacks.



For more Inforamation about XSS Cheat sheet click here

Mobile phone number validation regex in php - Regex for Indian Phone Numbers

In this article,I will explain how to validate Mobile phone number using regex in php.I have written simple php script using regular expression to validate all mobile phone numbers.
e.g.+910000000000,0000000000,910000000000,+91 0000000000,+91-0000000000...etc

PHP regular expressions help your application to validate Mobile Numbers,Emails,phone numbers,..etc.
The first sign (^) represents that it is the beginning of the string and  ($) represents the end of the string.

I have a function which checks to see if a Indian telephone/Mobile number is valid or not.


Example: Regular Expression Mobile Number Validation in PHP



<?php


$mobile="910000000001";


function phoneNumbervalidation($mobile)
{
if(preg_match('/^((\+){0,1}91(\s){0,1}(\-){0,1}(\s){0,1})?([0-9]{10})$/', $mobile,$matches)){
print_r($matches);
return true;
}
else
return false;
}

phoneNumbervalidation($mobile);

?>


I hope above example will help you to validate Indian Mobile numbers.You can refer above Regular expressions to validate international phone numbers.