PHP选择语句-错误

Question

我是学生，我在写一本笔记。我在我的中为登录或注册的用户创建了两个方法。

    <?php


    class DBHandler
    {

var $hostname;
var $user;
var $pw;
var $db;

var $connection;

function connectToDB($hostname,$user,$pw,$db){
    $this->hostname = $hostname;
    $this->user = $user;
    $this->pw = $pw;
    $this->db = $db;

    $this->connection = new mysqli($this->hostname,$this->user,$this->pw,$this->db);

    if ($this->connection->connect_error) {
        die('Failed to connect' . $this->connection->connect_error);
    }

    $this->ensureNotesTable();
    $this->ensureUsersTable();
}

function ensureUsersTable(){
    assert($this->connection);

    $queryCreate = "CREATE TABLE IF NOT EXISTS users(id INT(5) PRIMARY KEY AUTO_INCREMENT, username VARCHAR(100) NOT NULL, password VARCHAR(100) NOT NULL)";
    $this->connection->query($queryCreate);
}

function ensureNotesTable(){
    assert($this->connection);

    $queryCreate = "CREATE TABLE IF NOT EXISTS notesnew(id INT(5) PRIMARY KEY AUTO_INCREMENT, title VARCHAR(100) NOT NULL, content VARCHAR(100) NOT NULL, userid INT(5) NOT NULL)";
    $this->connection->query($queryCreate);
}

function ensureUsername($username,$password){
    assert($this->connection);

    echo $username;

    $query = "SELECT * FROM users WHERE username = (?)";
    $statement = $this->connection->prepare($query);
    $statement->bind_param('s',$username);
    $results = $statement->execute();
    echo $this->connection->errno.'-'.$this->connection->error;

    if(mysqli_num_rows($results) >= 1){
        echo $this->connection->errno.'-'.$this->connection->error;
        echo "Username already exists";
    } echo "Username is free!";
        $this->addUser($username,$password);
}


function addUser($username,$password){
    assert($this->connection);

    $queryCreate = "INSERT INTO users(username, password) VALUES (?,?)";
    $statement = $this->connection->prepare($queryCreate);
    $statement->bind_param('ss', $username, $password);

    return $statement->execute();

    echo "You have been registered!";
}

function getUserId($username){
    assert($this->connection);

    $queryCreate = "SELECT id FROM users WHERE username = $username";
    $row = $this->connection->query($queryCreate);

    $userid = mysqli_fetch_row($row);
    return $userid[0];
}

function addNote($title, $text, $userID){
    assert($this->connection);

    $queryCreate = "INSERT INTO notesnew(title, content, userid) VALUES (?,?,?)";
    $statement = $this->connection->prepare($queryCreate);
    $statement->bind_param('ssi', $title,$text,$userID);

    return $statement->execute();
}

}

在ensureUsername中，我想检查用于注册的用户名是否已经被其他用户选中了。

在addUser中，如果用户名是空闲的，我希望执行Insert语句，将用户添加到数据库中。

我今天试了大约3个小时，但总是会出错。我最讨厌这个了!也许我只是太蠢了。

现在它说：

警告: mysqli::query()期望参数1是字符串，在第57行的C:\Users\ReallySorry\PhpstormProjects\NoteAppMongo\DBHandler.php中给出对象-警告: mysqli_num_rows()希望参数1为mysqli_result，在第60行的C:\Users\ReallySorry\PhpstormProjects\NoteAppMongo\DBHandler.php中为null。

有人知道我做错了什么吗？

谢谢..。

抑郁的学生

Answer 1

你的问题是：

$statement = $this->connection->prepare($query);
$statement->bind_param('s',$username);
$results = $this->connection->query($statement)

当您使用预先准备好的语句时(做得很好，- so，这里的许多人不使用！)您需要对语句使用execute()方法，而不是在连接上调用query()。因此，这应该是可行的：

$results = $statement->execute()

Answer 2

对原作的一些小改动-

function ensureUsername( $username=false, $password=false ){
    $rv=false;/* Return Value */
    if( !assert( $this->connection ) or !$username or !$password ) return $rv;
    $db=$this->connection;/* shorthand for laziness */


    $sql = "select `username` from `users` where `username`=?;";
    $stmt = $db->prepare( $sql );
    $stmt->bind_param('s', $username );
    $res = $stmt->execute();
    $stmt->store_result();

    if( $res ){
        $rv=( $stmt->num_rows > 0 ) ? 'Sorry, that Username already exists!' : $this->addUser( $username, $password );
    }
    $stmt->free_result();
    $stmt->close();
    echo $rv;
}


function addUser( $username, $password ){
    $db=$this->connection;/* No need for assert now, if the script gets here the db conn must exist */

    $sql = "insert into `users` (`username`, `password`) values (?,?);";
    $stmt = $db->prepare( $sql );

    $stmt->bind_param('ss', $username, $password );

    return $stmt->execute() ? 'You have been registered!' : 'Sorry, there was a problem';
}