PHP query() and mysqli_query()

This article is created to cover the two functions of PHP, that are:

• query()
• mysqli_query()

Both functions are used to perform SQL query against the MySQL database using PHP MySQLi script. The only difference is, the query() uses with PHP MySQLi object-oriented script, whereas the mysqli_query() uses with PHP MySQLi procedural script.

PHP query()

The PHP query() function is used to perform an SQL query against MySQL database, in PHP MySQLi object-oriented style. For example:

<?php
   $server = "localhost";
   $user = "root";
   $pass = "";
   $db = "codescracker";
   
   $conn = new mysqli($server, $user, $pass, $db);
   
   if($conn->connect_errno)
   {
      echo "Database connection failed!<BR>";
      echo "Reason: ", $conn->connect_error;
      exit();
   }
   
   $sql = "INSERT INTO `customer`(`name`, `age`, `email`) 
      VALUES ('Michael', '25', 'michael@xyz.com')";
   
   $qry = $conn->query($sql);
   if($qry)
   {
      echo "Data inserted successfully.";
      
      // block of code, to process further
   }
   else
   {
      echo "Something went wrong!<BR>";
      echo "Error Description: ", $conn->error;
   }
   $conn->close();
?>

The output produced by above PHP example on query() function, is shown in the snapshot given below:

Note - The mysqli() is used to open a connection to the MySQL database server, in object-oriented style.

Note - The new keyword is used to create a new object.

Note - The connect_errno is used to get/return the error code (if any) from last connect call, in object-oriented style.

Note - The connect_error is used to get the error description (if any) from last connection, in object-oriented style.

Note - The exit() is used to terminate the execution of the current PHP script.

Note - The error is used to return the description of error (if any), by the most recent function call, in object-oriented style.

Note - The close() is used to close an opened connection, in object-oriented style.

The above example can also be written in this way:

<?php
   $conn = new mysqli("localhost", "root", "", "codescracker");
   
   if(!$conn->connect_errno)
   {
      $sql = "INSERT INTO `customer`(`name`, `age`, `email`) 
         VALUES ('Michael', '25', 'michael@xyz.com')";
         
      if($conn->query($sql))
      {
         echo "Data inserted successfully.";
         // block of code, to process further
      }
   }
   $conn->close();
?>

PHP query() Syntax

The syntax of query() function in PHP, is:

connectionVariable -> query(SQLcode, mode)

The mode parameter is optional, and is used to indicate how the result will be returned. The following three values that can be used to define this parameter:

• MYSQLI_STORE_RESULT - This is the default value. Used to return result object with buffered result set
• MYSQLI_USE_RESULT - Used to return result object with un-buffered result set
• MYSQLI_ASYNC - Used not to return result set immediately. The mysqli_poll() function, is then, to get results

PHP mysqli_query()

The PHP mysqli_query() function is used when we need to perform some query against the MySQL database in PHP MySQLi procedural style. For example:

<?php
   $conn = mysqli_connect("localhost", "root", "", "codescracker");
   
   if(!mysqli_connect_errno())
   {
      $sql = "INSERT INTO `customer`(`name`, `age`, `email`) 
         VALUES ('Olivia', '28', 'codescracker.com@gmail.com')";
      
      if(mysqli_query($conn, $sql))
      {
         echo "Data inserted successfully.";
         // block of code, to process further
      }
   }
   mysqli_close($conn);
?>

Note - The mysqli_connect() is used to open a connection to the MySQL database server, in procedural style.

Note - The mysqli_connect_errno() is used to get/return the error code (if any) from last connect call, in procedural style.

Note - The mysqli_close() is used to close an opened connection to the MySQL database, in procedural style.

PHP mysqli_query() Syntax

The syntax of mysqli_query() function in PHP, is:

mysqli_query(connectionVariable, SQLcode, mode)

Security Concern While Using query() Or mysqli_query()

While using either query() or mysqli_query(), to execute some query on the database, there are a lot of security concern comes into picture. Therefore we need to make sure, user can not use some malicious code to get into the database. This concern sometime called as SQL injection.

To avoid SQL injection against your database, use parameterized prepared statements, along with filtered parameters. For example:

<?php
   $conn = new mysqli("localhost", "root", "", "codescracker");
   
   if(!$conn->connect_errno)
   {
      $sql = "INSERT INTO `customer`(`name`, `age`, `email`) VALUES (?, ?, ?)";
      
      $qry = $conn->prepare($sql);
      $qry->bind_param("sis", $name, $age, $email);
         
      $name = $conn->real_escape_string("Ethan");
      $age = 31;
      $email = $conn->real_escape_string("ethan@xyz.com");
      
      $qry->execute();
   }
   $conn->close();
?>

In above example, the "sis" refers to, string integer string, the types of three parameters given to bind_param(), that are $name, $age, and $email.

Note - The prepare() is used to prepare an SQL statement before its execution on the MySQL database, in object-oriented style, to avoid SQL injection.

Note - The bind_param() is used to bind variables to a prepared statement, as parameters, in object-oriented style.

Note - The real_escape_string() is used to escape special characters from a string.

Note - The execute() is used to execute a prepared statement on the MySQL database, in object-oriented style.

PHP Online Test