PHP MySQL Interview Questions and Answers Set – 3



In this tutorial we are providing some set of frequently asked PHP MySQL Interview Questions which will help you to get success in interview. Read PHP interview questions and answers Set – 2.

  1. How can we connect to a MySQL database from a PHP script?

    To be able to connect to a MySQL database, we must use mysql_connect() function as follows:

    $database = mysql_connect("HOST", "USER_NAME", "PASSWORD"); 
    mysql_select_db("DATABASE_NAME", $database); 
    
  2. What is the function mysql_pconnect() usefull for?

    mysql_pconnect() ensure a persistent connection to the database, it means that the connection do not close when the PHP script ends.

  3. How the result set of Mysql be handled in PHP?

    The result set can be handled using mysql_fetch_array, mysql_fetch_assoc, mysql_fetch_object or mysql_fetch_row.

  4. How is it possible to know the number of rows returned in result set?

    The function mysql_num_rows() returns the number of rows in a result set.

  5. Which function gives us the number of affected entries by a query?

    mysql_affected_rows() return the number of entries affected by an SQL query.

  6. What is the difference between mysql_fetch_object() and mysql_fetch_array()?

    The mysql_fetch_object() function collects the first single matching record where mysql_fetch_array() collects all matching records from the table in an array.

  7. MySQL vs MySQLi vs PDO

    MySQLi (the “i” stands for improved) and PDO (PHP Data Objects) are the MySQL extensions used to connect to the MySQL server in PHP5 or verions, MySQL extension was deprecated in 2012.MySQLi only works with MySQL databases whereas PDO will works with 12 other Database systems.

    I recommend PDO because, if you want to choose another database instead of MySQL, then you only have to change the connection string and a few queries. But if you are using MySQLi you will need to rewrite the entire code.

  8. How to connect to mysql database using PDO connection?
    $servername = "localhost";
    $username = "username";
    $password = "password";
    
    try {
        $conn = new PDO("mysql:host=$servername;dbname=myDB", $username, $password);
        // set the PDO error mode to exception
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        echo "Connected successfully";
        }
    catch(PDOException $e)
        {
        echo "Connection failed: " . $e->getMessage();
        }
    
  9. How to connect to mysql database using MySQLi connection?

    MySQLi Object-Oriented

    $servername = "localhost";
    $username = "username";
    $password = "password";
    $dbname = "myDB"; // Optional
    
    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    echo "Connected successfully";
    

    MySQLi Procedural

    $servername = "localhost";
    $username = "username";
    $password = "password";
    $dbname = "myDB"; // Optional
    
    
    // Create connection
    $conn = mysqli_connect($servername, $username, $password, $dbname);
    
    // Check connection
    if (!$conn) {
        die("Connection failed: " . mysqli_connect_error());
    }
    echo "Connected successfully";
    
  10. How to execute an sql query? How to fetch its result ?

    MySQLi Object-oriented

    $sql = "SELECT id, firstname, lastname FROM users";
    $result = $conn->query($sql); // execute sql query
    
    if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) { // fetch data from the result set
            echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "
    ";
        }
    } else {
        echo "0 results";
    }
    

    MySQLi Procedural

    $sql = "SELECT id, firstname, lastname FROM users";
    $result = mysqli_query($conn, $sql); // execute sql query
    
    if (mysqli_num_rows($result) > 0) {
        // output data of each row
        while($row = mysqli_fetch_assoc($result)) { // fetch data from the result set
            echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "
    ";
        }
    } else {
        echo "0 results";
    }
    

    PDO

    Method 1:USE PDO query method

    $stmt = $db->query('SELECT id FROM Employee');  
    $row_count = $stmt->rowCount();  
    echo $row_count.' rows selected';
    

    Method 2: Statements With Parameters

    $stmt = $db->prepare("SELECT id FROM Employee WHERE name=?");  
    $stmt->execute(array($name));  
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
  11. How we can retrieve the data in the result set of MySQL using PHP?

    MySQLi methods

        1. mysqli_fetch_row
        2. mysqli_fetch_array
        3. mysqli_fetch_object
        4. mysqli_fetch_assoc
    

    PDO methods

        1. PDOStatement::fetch(PDO::FETCH_ASSOC)
        2. PDOStatement::fetch(PDO::FETCH_OBJ)
        3. PDOStatement::fetch()
        4. PDOStatement::fetch(PDO::FETCH_NUM)
    
  12. What is the use of mysql_real_escape_string() function?

    It is used to escapes special characters in a string for use in an SQL statement

  13. What is the difference between mysql_fetch_array() and mysql_fetch_assoc() ?

    mysql_fetch_assoc function Fetch a result row as an associative array, While mysql_fetch_array() fetches an associative array, a numeric array, or both.

  14. What is mean by an associative array?

    Associative arrays are arrays that use string keys is called associative arrays.

  15. Difference between mysql_connect and mysql_pconnect?

    mysql_pconnect() makes a persistent connection to the database which means a SQL link that do not close when the execution of your script ends. mysql_connect() provides only for the database new connection while using mysql_pconnect, the function would first try to find a (persistent) link that’s already open with the same host, username and password. If one is found, an identifier for it will be returned instead of opening a new connection, the connection to the SQL server will not be closed when the execution of the script ends. Instead, the link will remain open for future use.

  16. What are the differences between mysql_fetch_array(), mysql_fetch_object(), mysql_fetch_row()?

    mysql_fetch_array() Fetch a result row as an associative array, a numeric array, or both.
    mysql_fetch_object ( resource result ) Returns an object with properties that correspond to the fetched row and moves the internal data pointer ahead. Returns an object with properties that correspond to the fetched row, or FALSE if there are no more rows.
    mysql_fetch_row() fetches one row of data from the result associated with the specified result identifier. The row is returned as an array. Each result column is stored in an array offset, starting at offset 0.

  17. What is sql injection ?

    SQL injection is a malicious code injection technique. It exploiting SQL vulnerabilities in Web applications.

  18. What are the other commands to know the structure of a table using MySQL commands except EXPLAIN command?
    DESCRIBE table_name;
    
  19. How can we find the number of rows in a table using MySQL?

    Use this for MySQL

    SELECT COUNT(*) FROM table_name;
    
  20. How many ways we can we find the current date using MySQL?
    SELECT CURDATE();
    SELECT CURRENT_DATE();
    SELECT CURTIME();
    SELECT CURRENT_TIME();
    
  21. What is the difference between CHAR and VARCHAR data types?

    CHAR is a fixed length data type. CHAR(n) will take n characters of storage even if you enter less than n characters to that column. For example, “Hello!” will be stored as “Hello! ” in CHAR(10) column.

    VARCHAR is a variable length data type. VARCHAR(n) will take only the required storage for the actual number of characters entered to that column. For example, “Hello!” will be stored as “Hello!” in VARCHAR(10) column.

  22. How can we know the number of days between two given dates using MySQL?

    Use DATEDIFF()

    SELECT DATEDIFF(NOW(), '2018-01-14');
    
  23. How can we change the name of a column of a table?

    This will change the name of column:

    ALTER TABLE table_name CHANGE old_colm_name new_colm_name
    

Read Next Tutorial PHP Interview Questions and Answers Set – 4