Creating mySQLi Prepared Statements for a PHP User Class

Question

I have a User class that I created which looks something like this:

class User {
    private $user;
    private $con;

    public function __construct($con, $user){
        $this->con = $con;
        $user_details_query = mysqli_query($con, "SELECT * FROM users WHERE username='$user'");
        $this->user = mysqli_fetch_array($user_details_query);
    }

    public function getUsername() {
        return $this->user['username'];
    }

    public function getNumberOfFriendRequests() {
        $username = $this->user['username'];
        $query = mysqli_query($this->con, "SELECT * FROM friend_requests WHERE user_to='$username'");
        return mysqli_num_rows($query);
    }

    public function getNumPosts() {
        $username = $this->user['username'];
        $query = mysqli_query($this->con, "SELECT num_posts FROM users WHERE username='$username'");
        $row = mysqli_fetch_array($query);
        return $row['num_posts'];
    }

There is much more to this, however what I'm trying to understand is how to re-create it using prepared statements without messing up the rest of the pages. I have calls over several pages that rely on the given User class contstruct. I'm new to prepared staements but have managed to do pretty well, however am having trouble when it comes to the classes. I trying to recreate for example public function getNumberOfFriendRequests() so that it has the same output as before, so that existing pages are not throwing errors. Here is what I have so far:

public function getNumberOfFriendRequests() {
$username $this->user['username'];
$query = mysqli_prepare($this->$con, "SELECT COUNT(*) FROM friend_requests WHERE user_to=?");
$query->bind_param('s', $username);
$query->execute();
$query_>bind_result(NOT SURE);
$query->fetch();
}

I'm also confused abt the public function __construct($con, $user) because this is what is called whenever I want to access the User data. Does anyone know how I would begin to rewrite the given User class so that the returns are the same & not interfering with php calls on dependent pages?

Answer 1

This should be your same class rewritten to use prepared statements. Your construct makes sense as you're saying "This User" and also passing in your DB object. You won't use bind_result, as you're using SELECT *, and bind_result is for targeting specific fields.

class User {
private $user;
private $con;

public function __construct($con, $user){
    $this->con = $con;

    /* prepare the query */
    $stmt =  $this->con->stmt_init();
    $stmt->prepare('SELECT * FROM users WHERE username = ?');

    /* Bind to string $user */
    $stmt->bind_param('s', $user);
    $stmt->execute();

    /* Get a result obj */
    $result = $stmt->get_result();
    $this->user = $result->fetch_assoc();

    /* free results */
    $stmt->free_result();

    /* close statement */
    $stmt->close();
}

public function getUsername() {
    return $this->user['username'];
}

public function getNumberOfFriendRequests() {
    $username = $this->user['username'];
    $stmt =  $this->con->stmt_init();
    $stmt->prepare('SELECT * FROM friend_requests WHERE user_to = ?');
    $stmt->bind_param('s', $user);
    $stmt->execute();

    /* Get a result obj */
    $result = $stmt->get_result();
    $qty = $result->num_rows;

    /* free results */
    $stmt->free_result();

    /* close statement */
    $stmt->close();

    return $qty;
}

public function getNumPosts() {
    $username = $this->user['username'];
    $stmt =  $this->con->stmt_init();
    $stmt->prepare('SELECT num_posts FROM users WHERE username=?');

    /* Bind to string $user */
    $stmt->bind_param('s', $user);
    $stmt->execute();

    /* Get a result obj */
    $result = $stmt->get_result();
    $data = $result->fetch_assoc();

    /* free results */
    $stmt->free_result();

    /* close statement */
    $stmt->close();

    return $data['num_posts'];
}

P.S. Don't use SELECT *; see: Select * Is Evil