PHP MySQL interview questions and answers for experienced

Question: What are 3 different ways to connect with MySQL?
Following are 3 API with which we can connect to MySQL
1. MySQL
2. MySQLI
3. PDO

We should use MySQLI because MySQLI is imporoved version of MySQL and it have lot of functions which are absent in MySQL.

Question: What is the best collation to use for MySQL?
utf8_general_ci: It is used for fast sorting but it is less accurate.
utf8_unicode_ci: It is used for better accuracy but little slow as compare to utf8_general_ci.

You can also use for specific languages like utf8_swedish_ci.

Question: Give an example of Transaction with commit and rollback?

try {
    // First of all, let's begin a transaction
    $db->beginTransaction();

    // A set of queries; if one fails, an exception should be thrown which we are handling at bottom
    $db->query('1 query exe');
    $db->query('2 query exe');
    $db->query('3 query exe');
    
    $db->commit();
} catch (Exception $e) {
    // An exception has been thrown by try
    //This function have all the things which you want to rollback.

    $db->rollback();
}

Question: How to use "MySQL in clause" for "string search" and "number search"?
When we are searching with number: We need not to add single quote

$sql=mysql_query('SELECT * FROM `users` WHERE id IN ( 1, 2, 34 )');
while($data = mysql_fetch_array($sql)){
    //write here your code
}

When we are searching with string:Its compulsory to add single OR double quote.

$sql=mysql_query("SELECT * FROM `users` WHERE email in('myemail@gmail.com','anotheremail@gmail.com','testemail@gmail.com')");
while($data = mysql_fetch_array($sql)){
    //write here your code
}

Question: How to get the closed 10 locations between with lat long?
You can try below MySQL as its working for me:

$sql='SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) 
* cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin(radians(lat)) ) ) AS distance 
FROM city HAVING distance < 25 ORDER BY distance asc limit 0,10';

while($data = mysql_fetch_array($sql)){
    //write here your code
}

Question: How to use update query based on SELECT Query?
You have to use the join for this, See below format:

update tab1
left join table2 tab2 on
    tab1.name = tab2.name
set
    update_field = if(start_dates > end_dats, 'valid', 'Not-valie')

Queston: How to optimize table?

OPTIMIZE TABLE `users`

Question: How to repair table?
you can use any of below:

REPAIR TABLE users
REPAIR TABLE users QUICK
REPAIR TABLE users EXTENDED

Web Technology Experts Notes

Wednesday, 8 April 2015