How do I convert this PDO code to MySQLi?

Question

I'm quite new to PHP and MySQL and I try to learn how to change a code from PDO to MySQLi. Its about a remember me function with a securitytoken and identifier for a login system that I found in the web. I would like to learn and understand how I can change the code from PDO to MySQLi. I know in MySQLi there is a statement create and prepare, also I have to bind parameters and execute. But in this case, I don't know how to start anyway.

$pdo = new PDO('mysql:host=localhost;dbname=dbname', 'root', '');
if (!isset($_SESSION['id']) && isset($_COOKIE['identifier']) &&
isset($_COOKIE['securitytoken'])) {
    $identifier = $_COOKIE['identifier'];
    $securitytoken = $_COOKIE['securitytoken'];

    $statement = $pdo->prepare("SELECT * FROM securitytokens WHERE identifier = ?");
    $result = $statement->execute(array($identifier));
    $securitytoken_row = $statement->fetch();

    if (sha1($securitytoken) !== $securitytoken_row['securitytoken']) {
        die('Maybe a stolen securitytoken.');
    } else {
        //Token was correct
        //Set an new token
        $neuer_securitytoken = random_string();
        $insert = $pdo->prepare("UPDATE securitytokens SET securitytoken = :securitytoken WHERE identifier = :identifier");
        $insert->execute(array('securitytoken' => sha1($neuer_securitytoken), 'identifier' => $identifier));
        setcookie("identifier", $identifier, time() + (3600 * 24 * 365)); //1 Year valid
        setcookie("securitytoken", $neuer_securitytoken, time() + (3600 * 24 * 365)); //1 Year valid

        //Loggin the user
        $_SESSION['id'] = $securitytoken_row['id'];
    }
}

Answer 1

Don't do it! PDO is simpler and offers more functionality.

If you want to do it just to learn MySQLi then you should keep these things in mind:

1. This is how you open the connection properly:

   mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
   $mysqli = new mysqli($host, $user, $pass, $db);
   $mysqli->set_charset($charset);
   

2. There is no bind-in-execute in MySQLi. In fact there is no bind by value at all! You can only bind by reference and you have to specify the type of the value.

   $statement = $mysqli->prepare("SELECT * FROM securitytokens WHERE identifier = ?");
   $statement->bind_param('i', $identifier); // i for integer; s for string. 
   $statement->execute();
   

3. MySQLi has no named placeholders, so you need to use positional ones only.

   $insert = $mysqli->prepare("UPDATE securitytokens SET securitytoken = ? WHERE identifier = ?");
   $sha1ResultDueToPassByRef = sha1($neuer_securitytoken);
   $insert->bind_param('si', $sha1ResultDueToPassByRef, $identifier);
   $insert->execute();
   

4. The fetch method in MySQLi works totally differently and it returns boolean. To get the same outcome as PDO's fetch() you would need to get_result() and then use fetch_array() or fetch_assoc()

   $securitytoken_row = $statement->get_result()->fetch_array();