Wednesday 3 June 2015

PHP Mysql Interview Questions and Answers for fresher and experienced

PHP Mysql Interview Questions and Answers for fresher and experienced


Question: How many ways we can retrieve the data in the result set of MySQL using PHP?
Following are different ways
$sqlQuery = mysql_query("SELECT id, name FROM users");
while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
    //$row have results
}

$sqlQuery = mysql_query("SELECT id, name FROM users");
while ($row = mysql_fetch_assoc($sqlQuery)) {
    //$row have results
}

$sqlQuery = mysql_query("SELECT id, name FROM users");
while ($row = mysql_fetch_object($sqlQuery)) {
   //$row have results
}



Question: How can we create a database using PHP and MySQL?
mysql_create_db('db_name');



Question: How can we repair a MySQL table?
Use any of below as per requirement:
REPAIR tablename; 
REPAIR tablename quick;
REPAIR tablename extended;



Question: How can we find the number of rows in a result set using PHP?
$sqlQuery = mysql_query("SELECT id, name FROM users");
$num = mysql_num_rows($sqlQuery);



Question: How to update auto increment value for 100000?
ALTER TABLE table_name AUTO_INCREMENT = 100000;



Question: How to get the current version number of MYSQL?
SELECT VERSION();



Question: What is difference between between NOW() and CURRENT_DATE()?
Now gives the current date, hour and minutes
select NOW()
//2015-06-03 19:17:23  



CURRENT_DATE gives the current date only.
select CURRENT_DATE()
//2015-06-03

Question: List all databases?
SHOW DATABASES



Question: What is mysql query to show the first 100 records?
SELECT * FROM user LIMIT 0,100


Question: How many TRIGGERS allows per table in mysql?
Following are 6 triggers in mysql for table.
1. BEFORE INSERT,
2. AFTER INSERT,
3. BEFORE UPDATE,
4. AFTER UPDATE,
5. BEFORE DELETE
6. AFTER DELETE



Question: What is difference between COMMIT and ROLLBACK?
COMMIT: Mostly it is used in transaction and commit means all process are completed successfully. Once commit done you can not revert.
ROLLBACK: Mostly it is used in transaction and ROLLBACK means all process are NOT completed successfully. So revert the db changes automatically.


Question: What is SAVEPOINT?
The SAVEPOINT statement is used to set a savepoint with a name in transaction. used for roll back the transaction to the named savepoint specified instead of all the changes.


Question: How to find the number of days between two dates?
$now = time(); // or your date as well
$newDate = strtotime("2010-01-01");
$datediff = $now - $$newDate;
echo floor($datediff/(60*60*24));


Question: How to find the number of hours between two dates?
$now = time(); // or your date as well
$newDate = strtotime("2010-01-01");
$datediff = $now - $$newDate;
echo floor($datediff/(60*60));


Question: How to find the number of minutes between two dates?

$now = time(); // or your date as well
$newDate = strtotime("2010-01-01");
$datediff = $now - $$newDate;
echo floor($datediff/(60));


Question: How do I get random item from an array?

$array = array('','s','d');
echo $array[array_rand($array)];


Question: How to update the max_allowed_packet mysql variable?
You can check the value of max_allowed_packet with following query.
SHOW VARIABLES LIKE 'max_allowed_packet'; 

For Update, You have to change in configuration mysql.
File Location in My Wamp Server: D:\wamp\bin\mysql\mysql5.6.17\my.ini
Now search with max_allowed_packet and update, as per requirement.


Question: List all the tables whose engine is InnoDB?
SELECT table_name FROM INFORMATION_SCHEMA.TABLES  WHERE ENGINE = 'InnoDB'




Question: How to update the column where NULL value set?
update `users` set phone='000000000' where  phone is NULL