Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • MySQLi functions to run a query and checking for errors occcured on last function call

    • 1
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 1.21k
    Comment on it

    After connecting database successfully, the next important task in an application is performing the query on the connected database. This article provides the details about how we can run a query to a MySQL database using mysqli extension. The article also provides the details about different useful functions which can be used to track the errors occurred with the query. The list and details of these functions are given below :

     

    1. mysqli_query()

    mysqli_query() function is used to perform a query on the database.

     

    Result :  With non-DML statements such as SELECT, DESCRIBE, EXPLAIN etc., mysqli_query() will return a mysqli_result object on success of these queries. While with other queries such as INSERT, UPDATE, DELETE it will return TRUE on success and FALSE on failure.

     

    Syntax :

     

    1.1 Procedural Style : mysqli_query($con, $query, $result_mode) 

     

    Description :

    $con : MySQLi link identifier returned by mysqli_connect() or mysqli_init(). (Required)

    $query : Represents the query statement (SQL statement).(Required)

    $result_mode : Represent the result mode. (Optional)

     

    $result_mode can be one of the following :

     

    a. MYSQLI_STORE_RESULT : This mode is used as default.

    b. MYSQLI_USE_RESULT : This mode is used for retrieving large amount of data.

     

    Example :

    <?php
    
    $con = @mysqli_connect($host_name, $username, $password, $database);
    
    /* check connection errors */
    if (!$con) {
      die('Connect Error: ' . mysqli_connect_errno());
    }
    
    $query = "CREATE DATABASE demo";
    
    //execute query
    if (mysqli_query($con, $query)) {
        echo "Database created successfully";
    } else {
        echo "Error in creating database";
    }
    
    mysqli_close($con);
    
    ?>
    
    Output : Database created successfully

     

    1.2 Object Oriented Style : $con->query($query, $result_mode)

    $query and $result_mode parameters are same as defined above.

     

    Example :

    <?php
    
    // Create connection
    $con = @new mysqli($host_name, $username, $password, $database);
    
    if ($con->connect_errno) {
        die('Connect Error: ' . $con->connect_error);
    }
    
    $query = "CREATE DATABASE demo_db";
    
    //ececute query
    if ($con->query($query)) {
        echo "Database created successfully";
    }
    
    // close connection
    $con->close();
    
    ?>
    Output : Database created successfully

     

    2. mysqli_errorno()

    This function returns the error code for the last function call with respect to mysqli link whether it succeed or fail. If no error occurred, it returns 0.

     

    Syntax :

     

    2.1 Procedural Style : mysqli_errno($con)

    $con : Specifies the MySQLi link identifier. (Required)

     

    Example :

    <?php
    
    //create connection
    $con = @mysqli_connect($host_name, $username, $password, $database);
    
    // check connection errors 
    if (!$con) {
      die('Connect Error: ' . mysqli_connect_errno());
    }
    
    $query = "CREATE DATABASE demo_db";
    
    //execute query and check for error
    if (!mysqli_query($con, $query)) {
        echo "Error Code :". mysqli_errno($con);
    }
    
    //close connection
    mysqli_close($con);
    
    ?>
    Output : Error Code :1007

     

    2.2 Object Oriented Style : $con->errno

    Example :

    <?php
    
    //create connection
    $con = @new mysqli($host_name, $username, $password, $database);
    
    //check connection error
    if ($con->connect_errno) {
        die('Connect Error: ' . $con->connect_error);
    }
    
    $query = "CREATE DATABASE demo_db";
    
    //ececute query and check error
    if (!$con->query($query)) {
        echo "Error Code : ". $con->errno;
    }
    
    //close connection
    $con->close();
    
    ?>
    
    Output : Error Code :1007

     

    3. mysqli_error()

    The function mysqli_error() is similar to mysqli_errno(). The only difference is, it returns the error message instead of error code for the most recent function call.

     

    Result : Returns a string if any error occurred on recent function call and if no error occurred, it returns an empty string ("").

     

    Syntax :

     

    3.1 Procedural Style : mysqli_error($con)

    $con : Specifies the MySQLi link identifier. (Required)

     

    Example :

    <?php
    //create connection
    $con = @mysqli_connect($host_name, $username, $password, $database);
    
    // check connection errors 
    if (!$con) {
      die('Connect Error: ' . mysqli_connect_errno());
    }
    
    $query = "CREATE DATABASE demo_db";
    
    //execute query and check errors
    if (!mysqli_query($con, $query)) {
        echo "Error : ". mysqli_error($con);
    }
    
    // close connection
    mysqli_close($con);
    ?>
    
    Output : Error : Can't create database 'demo_db'; database exists

     

    3.2 Object Oriented Style : $con->mysqli->error

     

    Example :

    <?php
    // Create connection
    $con = @new mysqli($host_name, $username, $password, $database);
    
    //check connection errors
    if ($con->connect_errno) {
        die('Connect Error: ' . $con->connect_error);
    }
    
    $query = "CREATE DATABASE demo_db";
    
    //execute query and check error
    if (!$con->query($query)) {
        echo "Error : ". $con->error;
    }
    
    // close connection
    $con->close();
    ?>
    Output : Error : Can't create database 'demo_db'; database exists

     

    4. mysqli_error_list()

    Like above two functions, this function also returns a list of errors in the last function call.

     

    Result : Returns a list of errors as an associative array containing errno (error code), error (error statement) and sqlstate.

     

    Syntax :

     

    4.1 Procedural Style : mysqli_error_list ($con)

    $con : Specifies the MySQLi link identifier.(Required)

     

    Example :

    <?php
    //create connection
    $con = @mysqli_connect($host_name, $username, $password, $database);
    
    // check connection errors 
    if (!$con) {
      die('Connect Error: ' . mysqli_connect_errno());
    }
    
    $query = "CREATE DATABASE demo_db";
    
    //execute query and check error
    if (!mysqli_query($con, $query)) {
        echo "<pre>";
        print_r(mysqli_error_list($con));
        echo "</pre>";
    }
    
    // close connection
    mysqli_close($con);
    ?>

     

    Output :

    Array
    (
        [0] => Array
            (
                [errno] => 1007
                [sqlstate] => HY000
                [error] => Can't create database 'demo_db'; database exists
            )
    )

     

    4.2 Object Oriented Style : $con->error_list

     

    Example :

    <?php
    // Create connection
    $con = @new mysqli($host_name, $username, $password, $database);
    
    if ($con->connect_errno) {
        die('Connect Error: ' . $con->connect_error);
    }
    
    $query = "CREATE DATABASE demo_db";
    
    //execute query and check error
    if (!$con->query($query)) {
        echo "<pre>";
        print_r($con->error_list);
        echo "</pre>";
    }
    
    // close connection
    $con->close();
    ?>

     

    Output :

    Array
    (
        [0] => Array
            (
                [errno] => 1007
                [sqlstate] => HY000
                [error] => Can't create database 'demo_db'; database exists
            )
    )

     

     

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: