计算PDO准备语句中的行数

Question

I have two questions regarding codes below.

1. I know the second code is correct but not sure if first is also correct both do same thing first one is just easy to write.
2. I want to count the number of rows in database for the selected element if ($query->num_rows == 1) { doesn't work so how to rowcount for the code below.

First code:

 $query = $db->prepare("SELECT * from users WHERE username = :username");
 $query->execute(array(':username'=>$un));

Second:

$result = "SELECT * from users WHERE username = :username";
$query = $db->prepare( $result ); 
$stmt->bindValue(':username'=>$un);
$query->execute($stmt);

Answer 1

First, if you want to ensure that only one username is selected, you can use LIMIT in your MySQL statement

SELECT * from users WHERE username = :username ORDER BY id DESC LIMIT 1

Or:

SELECT DISTINCT(username) from users WHERE username = :username```

Even better, when creating the table, you can require that the username is unique:

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL,
    ...
    PRIMARY KEY (id),
);

Second, to verify that a row was actually retrieved from the dabase, you can use fetch:

$query = $db->prepare("SELECT * from users WHERE username = :username");
$query->execute(array(':username'=>$un));
$rows = $query->fetch(PDO::FETCH_NUM);
if($rows[0]) { 
    // Row exists
}