Self Join in mySql

I have a scenario where a table is required to be joined with itself. This process is called self-join.

Say, We have a table called 'category' with the following column structure.

cat_id	cat_name	parent_id
1	parent1	0
2	parent2	0
3	child1	1
4	child2	2
5	sub-child1	3
6	sub-child2	4

The table above has a parent-child relationship among rows. For example, category "parent1" has a child "child1" and again "child1" has a child named "sub-child1".

The table might have more such entries i.e "sub-child1" can have multiple children under it and even those children can have n number of children added under it. 

Problem is, how to get the tree for top-most category "parent1" ?

We need to use self-join techniques to solve this issue. Below is the SQL which will fetch all the children of "parent1".

SELECT t.cat_name as cat1, m.cat_name as cat2, l.cat_name as cat3 FROM category t

left join (select * from category ) as m on t.cat_id = m.parent_id

left join (select * from category ) as l on m.cat_id = l.parent_id

where t.cat_name = 'parent1';

Here, we have left joined the category table with itself twice as we wanted to find out 3rd level children. Using only "join" would not do the proper searching, because that will miss out those second level category names which don't have any children under it. Plain "join" returns data only if there is a match where as "Left Join" would return parent category name even if there is no match for any children.

If we were to search for only 1 level of children, we would have used the following query :

SELECT t.cat_name as cat1, m.cat_name as cat2 FROM category t

left join (select * from category ) as m on t.cat_id = m.parent_id where t.cat_name = 'parent1';

To find all the category names which have immediate children and each of those children has atleast one sub-children under it we can use the following SQL :


SELECT t.cat_name as cat1, m.cat_name as cat2, l.cat_name as cat3 FROM family as t, family as m, family as l 
where t.cat_id = m.parent_id and  m.cat_id = l.parent_id and t.cat_name = 'parent1'

Backticks in INSERT Query in PhpMyAdmin

I just want to share an experience while trying INSERT SQL query in MySql. I was working on MySQL server version: 5.5.16.

I have one employee details table called "emp" which has the following fields..
emp_id, emp_name, emp_sal, emp_address, emp_tel

The field names are quite self-explanatory. I faced problems when I wanted to inserted a row with only emp_id and emp_name values. 

INSERT into emp ("emp_id","emp_name") values("php-002","Chandan");

The above statement fell flat on its face. MySQL gave me an error saying syntax error.  However, I had corrected the statement by using backtick operator as a wrapper for field names. This is shown below.

INSERT into emp (`emp_id`,`emp_name`) values("php-002","Chandan");

Even if I leave the field names without applying any wrapper, it still works.

INSERT into emp (emp_id,emp_name) values("php-002","Chandan");

Hope this post was useful.

Get first and last day of current month in PHP

To achieve this we need to create a DateTime object and then use the modify() method to change the timestamp as shown in the code snippet below.

<?php
// Create Object
$d = new DateTime( date("Y-m-d",strtotime('now') ) );

// Change the timestamp in 'd' object
$d->modify('first day of this month');

// Format the date according to FORMAT passed
echo $d->format('Y-m-d 00:00:00');

// Again change the timestamp in 'd' object
$d->modify('last day of this month');

// Format the last day of the month
echo $d->format('Y-m-d 23:59:59');
?>

The above code is quite self-explanatory. 

1. Php date() function takes timestamp as 2nd parameter and strtotime() reads the first parameter as a date and converts it to a timestamp.

2. The DateTime object's modify() method is used to alter the timestamp. It can accept various English-like strings like "+1 day", "-1 month", "first day of this month"

Javascript Date Validation check

Suppose you have a contact form on your website, which job hunters will fill up during Job application. In that form, they must fill up the DOB text input.

We need to check ::

1. Date given is in correct format :: dd-mm-yyyy is maintained
2. Date is actually a correct data, date such as '29-02-1978' will be discared

We take the following actions for the points above respectively::

1. We use a regular expression to check the formatting of the date
2. We use Javascript Date() function feature for achieving the correct date

Check the code below, we have written a function called date_validation() which takes a date and returns true if it is a valid date, returns false otherwise.

<script type="text/javascript">
function date_validation(user_date)
{
// Use a RegEX pattern to check formatting
var patt = /^[0-9]{1,2}-[0-9]{1,2}-[0-9]{4,4}$/;

// If the input string's format is okay
if( patt.test(user_date) )
{

// Extract Day Month Year from user input
var p = user_date.split(/-/);
var a_year  = p[2];
var a_month = p[1];
var a_day   = p[0];


// Create a new Date Object with each part
// Date() object does not hold original data
// if the Date is wrong
var t_d = new Date(a_year, a_month - 1, a_day);

// Now check
if( t_d.getDate() == a_day && t_d.getMonth() == (a_month - 1) && t_d.getFullYear() == a_year )
{
   // Date is Okay
   return true;
}
else
{
   return false;
}
}
}

console.log( date_validation("29-2-1978") ); // False

console.log( date_validation("29-2-1976") ); // True
</script>

The code above is quite self-explanatory. Still, let's discuss some parts of it.

1.  We used a RegEX pattern to validate the user input. So the patt.test() returns true if the input is as per our expectation.

2. Next we extract date, month and year info from the user input by calling a split function. Split accepts Regular Expression as its arguments. So the pattern /-/ means we wanted to split it by the character hyphen (-). After the split, each part is stored in an array.

3. Next we create a Date object with these date, month and year information. In Javascript, month starts from 0, hence to mean February (month : 2), we need to use 1 (2-1).  

For example, if we want to create a date object with date : 29-2-1978, we need to provide

// January is 0, Feb is 1 etc
var test_date = new Date(1978, 1, 29); 

Javascript Date() function has a feature, if we want to create a Date object with an invalid date, it changes to a different valid date. For example, the above line will generate an object with date "1-3-1978" as "29-2-1978" does not exist on calendar. But if we used a valid date, the object gets created with that valid date only.

So, with the following lines ::

if( t_d.getDate() == a_day && t_d.getMonth() == (a_month - 1) && t_d.getFullYear() == a_year )

... we just checked whether the entered date is changing or not, if changes, it means that entered date is invalid. So, if the date is valid, this function returns true and false in other cases.

Check out some other validation techniques using Javascript in articles Email Validation in Javascript,  Numeric Data validation using Javascript. 

To know basic Javascript Regular Expression Patterns, see Article Basic Regular Expression Patterns I 

QR Code generation with PHP

Generating QR code with PHP is very easy. I had downloaded this library couple of days back..

Please download the class file BarcodeQR.php from http://www.shayanderson.com/


// Include the Library
include("BarcodeQR.php");

// Instantiate the BarcodeQR Object
$qr = new BarcodeQR();

// Info which will be put into the QR
$qr_text = "Account_no: 12345\nUsername: rocker\nEmail_address: test@gmail.com\nContact_no: 9988776655";

// Set object property
$qr->text($qr_text);

// Draw 
$qr->draw('206','254', "../path/to/your/QR_images/folder/test_qr.png");
?>

The above code is pretty self-explanatory. We need to include the class file, then create a object $qr with that class. Next we set the text which will be embedded in the QR image and finally we create the image using draw() method. The draw method takes Width, Height and Image path respectively.

Try it!! It's smooth and perfect.

Multiple record Saving using a loop in CakePHP

Situation :: Suppose, in a sample project, a vendor can have various contacts saved as his reference. We have an array of 10 rows and each of those rows need to be inserted in a database table using a loop. We have a corresponding model "Vendorcontact" where the table name and primary keys have been defined.

Problem :: 
If we use ModelName::save() method inside the loop, it creates a record for the first time in the zeroth iteration of the loop. Next time onwards, it just updates the same record.

Assumption :: 
The array names $vendor_contacts; 
The related model name is "Vendorcontact"; 
The vendor has an ID 1;
The "Vendorcontact" table has following fields :: vendor_id (INT), vendor_contact (Varchar)

Solution :: 


// Vendor id
$vendor_id = 1;

// Vendor contacts are kept in an Array
$vendor_contacts[0] = "x@x.com";
$vendor_contacts[1] = "xy@xy.com";
...
...
$vendor_contacts[9] = "z@z.com";

// Load corresponding model
$this->loadModel('Vendorcontact');

$a = array(); // 

// Build the loop
for( $i=0; $i < count($vendor_contacts); $i++ )
{
  $a = "";
  $a['vendor_id'] = $vendor_id;
  $a['vendor_contact'] = $vendor_contacts[$i];

  //The important line below tells
  //Cake to INSERT instead of UPDATE 
  $this->Vendorcontact->create(false);  
  $this->Vendorcontact->save( $a );

}
?>

Welcome

Welcome to CP's blog...



All the discussions/conclusions/remarks etc. are solely made by Chandan Patra. Please do comment/like if any article comes in your help.