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

Web Technology Experts Notes

Wednesday, 3 June 2015