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

Best Related Posts are Following:

1. PHP MySQL interview questions and answers for experienced
2. MySQL Interview Questions and Answers for Experienced
3. Difference between inner join and outer join in MySQL
4. SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 2269548 for key PRIMARY
5. Mysql Privileges - Types of privileages in MySql - How do I grant privileges in MySQL

Differences between Stored Procedures and Functions in MYSQL

1. Stored Procedure can return zero or n values whereas function can return one value which is mandatory.
2. Functions can be called from procedure whereas procedures cannot be called from function.
3. Procedures can have input/output parameters for it whereas functions can have only input parameters.
4. Procedure allows select as well as DML statement in it whereas function allows only select statement in it.
5. Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
6. We can go for transaction management in procedure whereas we can't go in function.
7. Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.

Best Related Posts are Following:

1. Comparison between MySQL and MongoDB.
2. What are differences between $(document).ready and $(window).load?.
3. Difference between MVC and MVP design pattern.
4. What is difference between event.preventDefault() and return false in Javascript?.
5. What is the difference between call and apply in javascript?.
6. Difference between inner join and outer join in MySQL.
7. Difference between theme and template in website.
8. Difference between in_array and array_key_exists in php with Example.
9. Difference between SSL and HTTPS.
10. Difference between Notice and warning and fatal error.
11. Difference between overloading and overriding in php with Example.
12. What is difference between natcasesort and sort?.
13. Difference Between Hadoop and Big-Data.
14. Difference Between Primary Key And Unique Key And Foreign Key And Composite Key.
15. PHP Sessions and Cookie and Security.
16. Difference between WebService and API.
17. Difference Between MyISAM and innoDB - MySQL.
18. Difference Between Zend framework 2 VS Zend framework 1.
19. Difference between JSON and JSONP.
20. Difference Between Cakephp and Zend Framework.
21. Difference between Abstract class and Interface in PHP.