PHP Pdo and MySQLi

PDO and mysqli simultaneously - Risk of collision?

You can use mysqli and PDO in the same script. Just like you can create multiple connections to the same database even if you only use one of these connectors.

You always have the risk of concurrent connections having lock contention. But this is no more risky by using multiple connectors.

Is it possible to use both MySQLi and PDO?

Yes, it's possible. But keep in mind that you'd have two completely separated connections to the MySQL server in any case. The mysqli_* and PDO_MySQL extensions cannot (currently) share a single connection even though they use the same transport driver (MySQLnd)

PHP MySQLi to PDO?

The most simple solution would be to change $pdo->fetch(PDO::FETCH_ASSOC) to $pdo->fetchAll(PDO::FETCH_ASSOC)

fetchAll returns ALL rows in the requested query, while fetch only gets 1 row (the first)

Example:

<?php

try {

    $PDO_result = $db_PDO->prepare("SELECT * FROM nnm_anime INNER JOIN nnm_anime_info ON nnm_anime.a_id = nnm_anime_info.a_id WHERE a_name LIKE ?");

    //Execute by inserting an array:
    if (!$PDO_result->execute([$pismenka[$i] . "%" ])) { //Added ."%" 
        die('Error!');
    }

    //Fetch rows:
    $rows = $PDO_result->fetchAll(PDO::FETCH_ASSOC);

    //Go trough each row:
    foreach ($rows as $row) {
        //Do something
    }

    //Catch exceptions thrown by PDO
} catch (PDOException $ex) {
    print_r($ex);
}

What is the difference between MySQLi and PDO?

PDO is an interface for accessing databases:

The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP. Each database driver that implements the PDO interface can expose database-specific features as regular extension functions. Note that you cannot perform any database functions using the PDO extension by itself; you must use a database-specific PDO driver to access a database server. (source)

MySQLi is an extension for accessing MySQL databases:

The mysqli extension allows you to access the functionality provided by MySQL 4.1 and above. (source)

Which one you should use is primarily opinion-based and not particularly well suited for the Stack Overflow format.

PHP PDO and MySQLi

At the basic level the mysql, mysqli and PDO extensions all answer the question how do I talk to the database? They all provide functions and functionality to connect to a database and send and retrieve data from it. You can use them all at the same time establishing several connections to the database at once, but that's typically nonsense.

mysql* is a very simple extension that basically allows you to connect to the database, send it SQL queries and not much else.

mysqli improves this (as the name suggests) by adding parameterized queries and a few other things into the mix.

PDO is an extension that abstracts several database drivers into one package, i.e. it allows you to use the same code to connect to MySQL, Oracle, MS SQL Server and a number of other databases without needing to use database specific extensions or rewrite your code when you switch databases (in theory at least). It also supports parameterized queries.

If you know you're going to be using MySQL exclusively, mysqli is a good choice. Especially since you can use it in a procedural way, what you're already used to from the mysql extension. If you're not familiar with OOP, that's helpful. Otherwise, PDO is a nice object oriented, flexible database connector.

---

* Note that the mysql extension is now deprecated and will be removed sometime in the future. That's because it is ancient, full of bad practices and lacks some modern features. Don't use it to write new code.

What is the difference between PDO and MySQLi prepared statements?

The difference is below:-

1. Mysqli is only for the MySQL database. PDO supports other database using the same functions.

2. Mysqli can be used in either an object-oriented style or a procedural style. PDO is always object-oriented.

3. Mysqli supports prepared statements with ? placeholders for parameters. PDO supports both ? placeholders and also named placeholders, like :columnName.

4. Mysqli requires that you use a function to bind each parameter value to the prepared statement. PDO also allows you to simply pass an array of parameter values as you execute the prepared statement.

How can i do that mysqli query with PDO?

As you have no insecure data in your query, there's no need to use prepare, use simple query function:

$dbc = $db->query("SELECT * FROM categories ORDER BY title");

Next, fetchAll fetches all results immediately. You need to fetch row by row. In case of query, this can be done as

foreach ($dbc as $row) {
    print_r($row);
}

Or with fetch method:

while ($row = $dbc->fetch()) {
    print_r($row);
}

What's this PDO Code in mysqli?

As said, I recommend using the PDO extension. But if you choose to use mysqli instead, then use the object-oriented mysqli instead of the procedural one. On php.net each mysqli function is presented in both ways.

The password should be strongly encrypted. My recommendation: the password_hash function - either with the PASSWORD_BCRYPT option (a constant defining the Blowfish hashing algorithm), or with the PASSWORD_ARGON2I option (constant defining the Argon2 hashing algorithm and introduced as of PHP 7.2.0). So, you should first save new user credentials in the form of a password hash (a string of minimum 60 aleatory characters) - in the users table. With a code similar to this one:

signup.php:

$username = $_POST['username'];
$password = $_POST['password'];

// Create a hash from a posted password.
$passwordHash = password_hash($password, PASSWORD_BCRYPT);

$sql = 'INSERT INTO users (username, password) VALUES (?, ?)';

$statement = $connection->prepare($sql);
$statement->bind_param('ss', $username, $passwordHash);
$statement->execute();

//...

For a clear view regarding your question here's an extended example of a login page - using my own naming and coding conventions. How you decide to adapt it, e.g. to process the results, is up to your logical scheme and system - I'm not familiar with Android. The code contains a server-side credentials validation part too. For proper error reporting see this article. Don't forget to change the db credentials.

connection.php:

<?php

/*
 * This page contains the code for creating a mysqli connection instance.
 */

// Db configs.
define('HOST', 'localhost');
define('PORT', 3306);
define('DATABASE', 'tests');
define('USERNAME', 'root');
define('PASSWORD', 'root');

/*
 * Enable internal report functions. This enables the exception handling,
 * e.g. mysqli will not throw PHP warnings anymore, but mysqli exceptions
 * (mysqli_sql_exception).
 *
 * MYSQLI_REPORT_ERROR: Report errors from mysqli function calls.
 * MYSQLI_REPORT_STRICT: Throw a mysqli_sql_exception for errors instead of warnings.
 *
 * @link http://php.net/manual/en/class.mysqli-driver.php
 * @link http://php.net/manual/en/mysqli-driver.report-mode.php
 * @link http://php.net/manual/en/mysqli.constants.php
 */
$mysqliDriver = new mysqli_driver();
$mysqliDriver->report_mode = (MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

/*
 * Create a new db connection.
 *
 * @see http://php.net/manual/en/mysqli.construct.php
 */
$connection = new mysqli(HOST, USERNAME, PASSWORD, DATABASE, PORT);

login.php:

<?php

require 'connection.php';

/*
 * ================================
 * Operations upon form submission.
 * ================================
 */
if (isset($_POST['submit'])) {
    /*
     * =======================
     * Read the posted values.
     * =======================
     */
    $username = isset($_POST['username']) ? $_POST['username'] : '';
    $password = isset($_POST['password']) ? $_POST['password'] : '';

    /*
     * ===========================
     * Validate the posted values.
     * ===========================
     */
    // Validate the username.
    if (empty($username)) {
        $errors[] = 'Please provide a username.';
    } /* Other validations here using elseif statements */

    // Validate the password.
    if (empty($password)) {
        $errors[] = 'Please provide a password.';
    } /* Other validations here using elseif statements */

    /*
     * ======================
     * Check the credentials.
     * ======================
     */
    if (!isset($errors)) { // No errors yet.
        /*
         * The SQL statement to be prepared. Notice the so-called markers,
         * e.g. the "?" signs. They will be replaced later with the
         * corresponding values when using mysqli_stmt::bind_param.
         *
         * @link http://php.net/manual/en/mysqli.prepare.php
         */
        $sql = 'SELECT username, password
                FROM users
                WHERE username = ?
                LIMIT 1';

        /*
         * Prepare the SQL statement for execution.
         *
         * @link http://php.net/manual/en/mysqli.prepare.php
         */
        $statement = $connection->prepare($sql);

        /*
         * Bind variables for the parameter markers (?) in the
         * SQL statement that was passed to prepare(). The first
         * argument of bind_param() is a string that contains one
         * or more characters which specify the types for the
         * corresponding bind variables.
         *
         * @link http://php.net/manual/en/mysqli-stmt.bind-param.php
         */
        $statement->bind_param('s', $username);

        /*
         * Execute the prepared SQL statement.
         * When executed any parameter markers which exist will
         * automatically be replaced with the appropriate data.
         *
         * @link http://php.net/manual/en/mysqli-stmt.execute.php
         */
        $statement->execute();

        /*
         * Get the result set from the prepared statement.
         *
         * NOTA BENE:
         * Available only with mysqlnd ("MySQL Native Driver")! If this
         * is not installed, then uncomment "extension=php_mysqli_mysqlnd.dll" in
         * PHP config file (php.ini) and restart web server (I assume Apache) and
         * mysql service. Or use the following functions instead:
         * mysqli_stmt::store_result + mysqli_stmt::bind_result + mysqli_stmt::fetch.
         *
         * @link http://php.net/manual/en/mysqli-stmt.get-result.php
         * @link https://stackoverflow.com/questions/8321096/call-to-undefined-method-mysqli-stmtget-result
         */
        $result = $statement->get_result();

        /*
         * Fetch the credentials into an associative array.
         * If no record is found, the operation returns NULL.
         */
        $credentials = $result->fetch_array(MYSQLI_ASSOC);

        if (isset($credentials) && $credentials) { // Record found.
            $fetchedUsername = $credentials['username'];
            $fetchedPasswordHash = $credentials['password'];

            /*
             * Compare the posted username with the one saved in db and the posted
             * password with the password hash saved in db using password_hash.
             *
             * @link https://secure.php.net/manual/en/function.password-verify.php
             * @link https://secure.php.net/manual/en/function.password-hash.php
             */
            if (
                    $username === $fetchedUsername &&
                    password_verify($password, $fetchedPasswordHash)
            ) {
                header('Location: welcome.html');
                exit();
            } else {
                $errors[] = 'Invalid credentials. Please try again.';
            }
        } else {
            $errors[] = 'No credentials found for the given user.';
        }
    }
}
?>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
        <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=yes" />
        <meta charset="UTF-8" />
        <!-- The above 3 meta tags must come first in the head -->

        <title>Demo - Login</title>

        <script src="https://code.jquery.com/jquery-3.2.1.min.js" type="text/javascript"></script>

        <script type="text/javascript">
            $(document).ready(function () {
                $('#username').focus();
            });

            function validateForm() {
                return true;
            }
        </script>

        <style type="text/css">
            body {
                padding: 30px;
            }

            label {
                display: block;
                font-weight: 400;
            }

            input[type="text"],
            input[type="password"] {
                display: block;
                margin-bottom: 20px;
            }

            button {
                display: block;
                padding: 7px 10px;
                background-color: #8daf15;
                color: #fff;
                border: none;
            }

            .messages {
                margin-bottom: 20px;
            }

            .messages .error {
                color: #c00;
            }
        </style>
    </head>
    <body>

        <div class="messages">
            <?php
            if (isset($errors)) {
                foreach ($errors as $error) {
                    ?>
                    <div class="error">
                        <?php echo $error; ?>
                    </div>
                    <?php
                }
            }
            ?>
        </div>

        <div class="form-login">
            <form name="credentials" action="" method="post" onsubmit="return validateForm();">
                <label for="username">Username:</label>
                <input type="text" id="username" name="username" value="<?php echo isset($username) ? $username : ''; ?>">

                <label for="password">Password:</label>
                <input type="password" id="password" name="password" value="<?php echo isset($password) ? $password : ''; ?>">

                <button type="submit" name="submit" value="submit">
                    Submit
                </button>
            </form>
        </div>

    </body>
</html>

The table structure used for testing:

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(100) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The data used for testing:

This data would be saved by running the signup.php code. Each password hash here (e.g. each value of the password column) is the encrypted representation of the corresponding value of the username column. For example, the first hash represents the string (e.g. the password) "demo1".

INSERT INTO `users` (`id`, `username`, `password`)
VALUES
    (1, 'demo1', '$2y$10$ZzULeTfsMwBj6DwpsfzxPu0irOrkL.l7rkimPkpcojL4RAMLwEZkW'),
    (2, 'demo2', '$2y$10$bpLOz4ur4wdVs4RN9ZatGekmynMhgOAdkwBchRLAf2t8hwc9Kkh7K');

---

Related Topics