Generate drop and add constraint query for a table

Below query will generate drop and add constraint query for a table

SELECT `DROP`,`CREATE` FROM (
SELECT
CONCAT("ALTER TABLE `", `K`.`TABLE_NAME`, "` DROP FOREIGN KEY `", `K`.`CONSTRAINT_NAME`, "`;") "DROP",
CONCAT("ALTER TABLE `",
`K`.`TABLE_NAME`,
"` ADD CONSTRAINT ",
"`fk_",
`K`.`TABLE_NAME`,
"_",
`K`.`REFERENCED_TABLE_NAME`,
"1",
"` FOREIGN KEY (`",
`K`.`COLUMN_NAME`,
"`) REFERENCES ",
"`",
`K`.`REFERENCED_TABLE_SCHEMA`,
"`.`",
`K`.`REFERENCED_TABLE_NAME`,
"` (`",
`K`.`REFERENCED_COLUMN_NAME`,
"`) ON DELETE ",
`C`.`DELETE_RULE`,
" ON UPDATE ",
`C`.`UPDATE_RULE`,
";") "CREATE"
FROM `information_schema`.`KEY_COLUMN_USAGE` `K`
LEFT JOIN `information_schema`.`REFERENTIAL_CONSTRAINTS` `C` USING (`CONSTRAINT_NAME`)
WHERE `K`.`REFERENCED_TABLE_SCHEMA` = "dbname"
AND `K`.`REFERENCED_TABLE_NAME` = "table") AS DropCreateConstraints;

change all fields having DATA_TYPE ‘datetime’ to ‘int’ across all table of a DB without data loss

Process I follow to change all fields having DATA_TYPE ‘datetime’ to ‘int’ across all table of a DB without data loss

1) Generate all alter statement to convert DATA_TYPE to ‘datetime’ to ‘varchar’

select concat(
"ALTER TABLE `",
`TABLE_SCHEMA`,
"`.`",
`TABLE_NAME`,
"` CHANGE COLUMN `",
`COLUMN_NAME`,
"` `",
`COLUMN_NAME`,
"` VARCHAR(50) NULL DEFAULT NULL",
";"
) AS alterDatetime
from `information_schema`.`COLUMNS`
where TABLE_SCHEMA = ‘dbname’
and DATA_TYPE = ‘datetime’;

2) Generate all UPDATE statement to set unix_timestamp value

select concat(
"UPDATE `",
`TABLE_SCHEMA`,
"`.`",
`TABLE_NAME`,
"` SET `",
`COLUMN_NAME`,
"` = ",
"unix_timestamp(`",
`COLUMN_NAME`,
"`);"
) AS alterDatetime
from `information_schema`.`COLUMNS`
where TABLE_SCHEMA = 'dbname'
and DATA_TYPE = 'datetime';

3) Generate all ALTER statement to convert DATA_TYPE ‘varchar’ to ‘int’

select concat(
"ALTER TABLE `",
`TABLE_SCHEMA`,
"`.`",
`TABLE_NAME`,
"` CHANGE COLUMN `",
`COLUMN_NAME`,
"` `",
`COLUMN_NAME`,
"` INT(11) UNSIGNED NULL DEFAULT NULL",
";"
) AS alterDatetime
from `information_schema`.`COLUMNS`
where TABLE_SCHEMA = 'dbname'
and DATA_TYPE = 'datetime';

Example :
1) ALTER TABLE `dbname`.`tblename` CHANGE COLUMN `createdOn` `createdOn` VARCHAR(50) NULL DEFAULT NULL;

2) UPDATE `dbname`.`tblename` SET `createdOn` = unix_timestamp(`createdOn`);

3) ALTER TABLE `dbname`.`tblename` CHANGE COLUMN `createdOn` `createdOn` INT(11) UNSIGNED NULL DEFAULT NULL;

What is the difference between PHP-4 and PHP-5?

PHP4

1. Passed by value only
2. Duplicate an object by assigning it to another variable
3. A constructor was simply a method that had the same name as the class itself
4. Not support abstract class
5. Not support interface
6. Not support magic methods
7. Not support final

PHP5

1. Passed by value and passed by reference
2. Duplicate an object by clone keyword
   Function __clone(){
   $this->obj = clone $this->obj;
   }
3. It introduces a new unified constructor/destructor
   __constructor
   __destructor
4. It allows to declare a class as abstract
5. Supports interfaces
6. Supports Maic methods
   __call, __get, __set and __toString
7. Use final keyword
8. Supports exceptions
9. Inclusions of visibility

Difference between require() and require_once().Please explain in details with examples.

Include() vs require()
The include() statement includes and evaluates the specified file and same for require() also.
The two constructs are identical in every way except how they handle failure

1. Include() produces a warning while require() results in a fatal error
2. Use require() if you want a missing file to hault processing of the page. Include() does not behave this way, the script will continue regardless.

Explain CURL and its use .

CURL
Curl is the client URL function library. PHP supports it through ‘libcurl’. To enable support for libcurl when installing PHP add –with-curl = [location of curl libraries] to the cofigure statement before compiling.
Curl package must be installed prior to installing PHP.
Most major functions desired when connecting to remote webservers are included in curl, including POST, GET for posting, SSL support, HTTP authentication, session and cookie handling.

Ex: <?php
$ch = curl-init();
Curl_setopt($ch, CURLOPT_URL,’http://www.ex.com’);
Curl_setopt($ch, CURLOPT_HEADER,1);
Curl_setopt($ch, CURLOPT_RETURNTRANSFER,1);
$data = curl_exec();
Curl_close($ch);
?>
Options:-
($ch,CURLOPT_POST,1) –tell curl to send the form respons via the POST method
($ch, CURLOPT_POSTFIELDS, $curlpost) – Store the post data

“A command line tool for getting or sending files using URL syntax”

Diff. between MYISAM Vs INNODB

Much more conservative approach to disk space management, each MyISAM table is stored in a separate file, which could be compressed then with myisamcheck if needed. With innodb the tables are stored in tablespace., and not much further optimization is possible. All data except for TEXT and BLOB cab occupy 8000 bytes at most. No fulltext indexing is available for innodb. The COUNT(*) execute slower than in MyISAM due to tablespace complexity.

INNODB- full support of ACID transactions, foreign keys with cascading UPDATE & DELETE and row level locking (Commit, Rollback)

How to check case sensitive in query ?

Check case sensitive:BINARY:

SELECT * FROM tblname WHERE BINARY email = mail2pjena@gmail.com

Difference between LEFT JOIN and Query with comma(,)

SELECT x.aid, b.bid, b.bdesc FROM x LEFT JOIN b ON (b.bid=x.bid) WHERE x.aid=”whatever”
VS
SELECT x.aid, b.bid, b.bdesc FROM x,b WHERE (x.aid=”whatever”) AND (b.bid=x.bid)
The difference between these two SQL will only become apparent if an occurrence of table ‘b’ is missing for an occurrence of table ‘x’. With the 1st query all columns selected from table ‘b’ will be returned as empty. With the 2nd query the effect will be that the occurrence of ‘x’ will be dropped from the selection.

Explain about use of ob_start() and why we use buffer.

This function will turn output buffering on. While output buffering is active no output is sent from the script (other than headers), instead the output is stored in an internal buffer.
The contents of this internal buffer may be copied into a string variable using ob_get_contents(). To output what is stored in the internal buffer, use ob_end_flush(). Alternatively, ob_end_clean() will silently discard the buffer contents.
http://php.net/manual/en/function.ob-start.php

The Output Buffer
Output Buffer is ideal for caching technique. In this way one have to start caching bye using the function ob_start() at the start of the program and then we get the buffer output content by using ob_get_contents() and then stop buffering by using ob_end_flash(). Then we can store the buffer output content and show it whenever we need it. We can also use cache expire time to make obsolete the cache and cache the fresh content.

Sample Code
<?php
//set cache expire timer
$cache_expire_time = 60*60; //this time is in second
// check if there is a cached file existence
if ( file_exists( ‘cachefile.ctmp’ ) && (time()-$cache_expire_time < filemtime(‘cachefile.ctmp’))) {
// if file is already cached and not expired then read the file
readfile ( ‘cachefile.ctmp’ );
exit();
}else if( file_exists( ‘cachefile.ctmp’ )){
//file exist and expired so delete it
@unlink(‘cachefile.ctmp’);
}
// if no cached version start output buffering
ob_start();
// display some HTML ( this will be stored in the buffer )
?>
<html>
<head>
<title>Simple PHP caching</title>
</head>
<body>
<h2>This will be cached by the program.</h2>
</body>
</html>
<?php
$bufferContent = ob_get_contents();
// get buffer content
ob_end_flush();
// clean and display buffer content in the browser
$fp = fopen ( ‘cachefile.ctmp’ , ‘w’ ) or die ( ‘Error opening cache file’ );
// write buffer content to cache file
fwrite ( $fp , $bufferContent );
fclose( $fp );
?>

Explain self join with an example.

Given a table product_tbl with a field product_status. We could see how many products there are and how many currently are not for sale.

SELECT COUNT(p1.id) AS total_products, COUNT(p2.id) AS not_for_sale FROM product_tbl p1 LEFT JOIN product_tbl p2 ON p1.id = p2.id AND p2.product_status = 0;