PHP query() and mysqli_query()

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

• query()
• mysqli_query()

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

PHP query()

The PHP query() function is used to perform an SQL query against the 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 the above PHP example using the query() function is shown in the snapshot given below:

Note: The mysqli() function 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 or return the error code (if any) from the last connect call in object-oriented style.

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

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

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

Note: The close() function 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 the 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 can be used to define this parameter:

• MYSQLI_STORE_RESULT: This is the default value. Used to return the result object with the buffered result set.
• MYSQLI_USE_RESULT: Used to return a result object with an unbuffered result set.
• MYSQLI_ASYNC: Used to not return the result set immediately.

PHP mysqli_query()

In PHP mysqli procedural style, we use the mysqli_query() function to do a query against the MySQL database. 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() function is used to open a connection to the MySQL database server in procedural style.

Note: The mysqli_connect_errno() function is used to get or return the error code (if any) from the last connect call in procedural style.

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

PHP mysqli_query() Syntax

The syntax of the 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 queries on the database, there are a lot of security concerns that come into play. Therefore, we need to make sure the user cannot use some malicious code to get into the database. This concern is sometimes called 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 the above example, the "sis" refers to string integer string, the types of the three parameters given to bind_param(), which are $name, $age, and $email.

Note: The prepare() function 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() function is used to bind variables to a prepared statement as parameters in object-oriented style.

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

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

PHP Online Test