CODEWITHSUNDEEP
phpmysqlmysqlipdo
user4933310
user4933310

Reputation:

fetch_array to PDO

I'm still new to PDO. Need to know the replacement of this mysqli code to PDO.

public function validusername() {
    $conn = new mysqli('127.0.0.1', 'root', '', 'project');
    $sql = "SELECT COUNT(*) FROM `users` WHERE `username` = '$this->username'";
    $result = $conn->query($sql);

    return ($result->fetch_array()[0] == 1) ? true : false;
}

Note: $this->username is a value which is from $_POST['username'] from public function ___contruct in a single class. This mysqli will check if the username is exist in MySQL and will return true if any.

I tried to replace the codes with these:

public function validusername() {
    $db = new PDO('mysql:host=127.0.0.1;dbname=project', 'root', '');

    $result = $db->prepare('
        SELECT COUNT(*)
        FROM users
        WHERE username = :username
    ');
    $result->execute(['username'=>$this->username]);
    $result = $result->fetch(PDO::FETCH_ASSOC);

    return ($result[0] == 1) ? true : false;
}

Upvotes: 1

Views: 606

Answers (2)

RiggsFolly
RiggsFolly

Reputation: 94672

I think all you need to do is correctly reference the parameter in your parameter list

$result->execute(['username'=>$this->username]);

it should be

$result->execute([':username'=>$this->username]);

Notice the : in front of the parameter array key field was missing

You are also not using any error checking. I prefer to set PDO to throw exceptions but as you dont you can also test the result of each call and output the database error so you know more about whats wrong like this

public function validusername() {
    $db = new PDO('mysql:host=127.0.0.1;dbname=project', 'root', '');

    $result = $db->prepare('
        SELECT COUNT(*)
        FROM users
        WHERE username = :username');
    if ( ! $result ) {
        print_r($db->errorInfo());
        exit;
    }

    if ( ! $result->execute(['username'=>$this->username]) ) {
        print_r($db->errorInfo());
        exit;
    }

    $result = $result->fetch(PDO::FETCH_ASSOC);

    return ($result[0] == 1) ? true : false;
}

Additional tip:

It is normal to connect to the database only once per execution. You appear to be doing it for each method. It would be better to pass $db as a parameter or better still to hold the $db handle as a class property and set it as part of the constructor. Connection is quite a time consuming action.

Upvotes: 2

Clay
Clay

Reputation: 4760

The main changes are:

  1. Use a DSN when using PDO - the DSN will include the database, hostname or port (optional)

  2. You define the fetch mode on the instance of the connection object.

  3. You can easily bind your values to the query to prevent SQL injection

  4. You create a statement with the prepare method then call execute on the variable returned from that.

Here is your code updated to reflect what is mentioned above:

public function validusername() {
    $conn = new PDO('mysql:dbname=project;host=127.0.0.1', 'root', '');
    // return all result sets as associative array
    $conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

    $sql = "SELECT COUNT(`uid`) AS total FROM `users` WHERE `username` = :username";
    $stmt = $conn->prepare($sql);
    $stmt->bindParam(':username', $this->username);
    $stmt->execute();
    $row = $stmt->fetch();
    return $row['total']==1;
}

And a quick way to bind a variable is to do this:

$stmt->execute([':username'=>$this->username]);

instead of this:

$stmt->bindParam(':username', $this->username);
$stmt->execute();

With bindParam you can set the param type for the third parameter (such as int or boolean).

Here is the documentation for PDO's statement object which provides a number of other examples for executing queries: http://php.net/manual/en/pdo.prepared-statements.php

Upvotes: 2

Related Questions