mysql获取assoc数组空错误

Question

I want to output the fetched array onto the frontend. It works fine until the array returns as empty. It throws a PHP error that 'undefined variable $data on php line X'. I've looked for solutions though they have not fully suited what I have in mind. Please assist.

public function search($search) {
    try {
        $query = $this->connection->prepare ( "SELECT * FROM files WHERE number=$search ORDER BY id" );
        $query->execute ();
        while ( $row = $query->fetch ( PDO::FETCH_ASSOC ) ) {
            $data [] = $row;
        }
        return $data;
    } catch ( PDOException $e ) {
        $e->getMessage ();
    }
}

Answer 1

You are running your query wrong way.

The only proper way to add a variable into PDO query is to add it through a parameter. It is very important to understand that simply adding quotes around a variable is not enough and will eventually lead to innumerable problems, from syntax errors to SQL injections. On the other hand, due to the very nature of prepared statements, it's a bullet-proof solution that makes it impossible to introduce any problem through a data variable.

    $this->connection->prepare ( "SELECT * FROM files WHERE number=? ORDER BY id" );
    $query->execute ([$search]);

while to eliminate the error you should use the appropriate fetch mode. So the full code would be

public function search($search) {
    $this->connection->prepare ( "SELECT * FROM files WHERE number=? ORDER BY id" );
    $query->execute ([$search]);
    return $query->fetchAll(PDO::FETCH_ASSOC);
}

note that you should never catch an error to report it