Connecting PHP to MySQL (MySQLi & PDO)

This is the most asked question I have seen in the past few months so I thought I would collect some samples here:

MySQLi Connect Example (non Object)

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // Make MySQLi throw exceptions.

    try {
        // Try Connect to the DB with mysqli_connect function - Params {hostname, userid, password, dbname}
        $link = mysqli_connect("localhost", "root", "", "mysqli_examples");
    } catch (mysqli_sql_exception $e) { // Failed to connect? Lets see the exception details..
        echo "MySQLi Error Code: " . $e->getCode() . "<br />";
        echo "Exception Msg: " . $e->getMessage();
        exit; // exit and close connection.
    }

    //No Exceptions were thrown, we connected successfully, yay!
    echo "Success, we connected without failure! <br />";
    echo "Connection Info: " . mysqli_get_host_info($link) . PHP_EOL;

    /*
     * Do some cool mysqli stuff...
     */
    
    mysqli_close($link); // finally, close the connection
    ?>

MySQLi Connect Example (Object)

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // Make MySQLi throw exceptions.

    try {
        // Try Connect to the DB with new MySqli object - Params {hostname, userid, password, dbname}
        $link = new mysqli("localhost", "root", "", "mysqli_examples");
    } catch (mysqli_sql_exception $e) { // Failed to connect? Lets see the exception details..
        echo "MySQLi Error Code: " . $e->getCode() . "<br />";
        echo "Exception Msg: " . $e->getMessage();
        exit; // exit and close connection.
    }

    //No Exceptions were thrown, we connected successfully, yay!
    echo "Success, we connected without failure! <br />";
    echo "Connection Info: " . mysqli_get_host_info($link) . PHP_EOL;

    /*
     * Do some cool mysqli stuff...
     */
    
    mysqli_close($link); // finally, close the connection
    ?>

PHP MySQLi Prepare

$statement = mysqli_prepare($link, "select user_name, first_name, last_name from users_table where id = 1");
OR
$statement = mysqli_prepare($link, "select user_name, first_name, last_name from users_table where id = ?");
mysqli_stmt_bind_param($statement, "?", 1);

PHP MySQLi Select DB

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // Make MySQLi throw exceptions.

        try {
            // Try Connect to the DB with new MySqli object - Params {hostname, userid, password, dbname}
            $link = mysqli_connect("localhost", "root", "", "mysqli_examples");
            // Select all records from the db. Keep inside try block incase there an issue with sql query
            $result = mysqli_query($link, "select username, first_name, last_name, gender from users");

        } catch (mysqli_sql_exception $e) { // Failed to connect? Lets see the exception details..
            echo "MySQLi Error Code: " . $e->getCode() . "<br />";
            echo "Exception Msg: " . $e->getMessage();
            exit; // exit and close connection.
        }
        
        
        /*
         * Do some cool mysqli stuff with our data
         */
        while($row = mysqli_fetch_assoc($result))
            {
                $users[] = $row;
            }
        var_dump($users);
        
        mysqli_close($link); // finally, close the connection

Object-Oriented Style Select

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // Make MySQLi throw exceptions.

        try {
            // Try Connect to the DB with new MySqli object - Params {hostname, userid, password, dbname}
            $mysqli = new mysqli("localhost", "root", "", "mysqli_examples");
            // Select all records from the db. Keep inside try block incase there an issue with sql query
            $query = "select username, first_name, last_name, gender from users";

            if ($result = $mysqli->query($query)) {

                /* fetch associative array */
                while ($row = $result->fetch_assoc()) {
                    var_dump($row);
                }

                /* free the memory associated with this result set */
                $result->free();
            }
        } catch (mysqli_sql_exception $e) { // Failed to connect? Lets see the exception details..
            echo "MySQLi Error Code: " . $e->getCode() . "<br />";
            echo "Exception Msg: " . $e->getMessage();
            exit; // exit and close connection.
        }

        $mysqli->close(); // finally, close the connection

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // Make MySQLi throw exceptions.

        try {
            // Try Connect to the DB with new MySqli object - Params {hostname, userid, password, dbname}
            $link = mysqli_connect("localhost", "root", "", "mysqli_examples");
            // Select all records from the db. Keep inside try block incase there an issue with sql query
            $result = mysqli_query($link, "select username, first_name, last_name, gender from users");
        } catch (mysqli_sql_exception $e) { // Failed to connect? Lets see the exception details..
            echo "MySQLi Error Code: " . $e->getCode() . "<br />";
            echo "Exception Msg: " . $e->getMessage();
            exit; // exit and close connection.
        }

        /*
         * use mysqli_fetch_array and loop through data
         */
        while ($row = mysqli_fetch_array($result, MYSQLI_BOTH))
        {
            var_dump($row);
        }
        
        mysqli_close($link); // finally, close the connectionCopy

Procedural Style

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // Make MySQLi throw exceptions.

        try {
            // Try Connect to the DB with new MySqli object - Params {hostname, userid, password, dbname}
            $link = mysqli_connect("localhost", "root", "", "mysqli_examples");
            // Select all records from the db. Keep inside try block incase there an issue with sql query
            $result = mysqli_query($link, "select username, first_name, last_name, gender from users");
        } catch (mysqli_sql_exception $e) { // Failed to connect? Lets see the exception details..
            echo "MySQLi Error Code: " . $e->getCode() . "<br />";
            echo "Exception Msg: " . $e->getMessage();
            exit; // exit and close connection.
        }

        /*
         * use mysqli_fetch_array and loop through data
         */
        while ($row = mysqli_fetch_array($result, MYSQLI_BOTH))
        {
            var_dump($row);
        }
        
        mysqli_close($link); // finally, close the connectionCopy

Object Oriented style

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // Make MySQLi throw exceptions.

        try {
            // Try Connect to the DB with new MySqli object - Params {hostname, userid, password, dbname}
            $mysqli = new mysqli("localhost", "root", "", "mysqli_examples");
            // Select all records from the db. Keep inside try block incase there an issue with sql query
            $query = "select username, first_name, last_name, gender from users";

            if ($result = $mysqli->query($query)) {

                //Use mysqli fetch_array and use the MYSQLI_BOTH Parameter for both indexed and associative data
                while ($row = $result->fetch_array(MYSQLI_BOTH)) {
                    var_dump($row);
                }

                /* free the memory associated with this result set */
                $result->free();
            }
        } catch (mysqli_sql_exception $e) { // Failed to connect? Lets see the exception details..
            echo "MySQLi Error Code: " . $e->getCode() . "<br />";
            echo "Exception Msg: " . $e->getMessage();
            exit; // exit and close connection.
        }

        $mysqli->close(); // finally, close the connection