mysql fetch assoc array empty error

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 ();
    }
}

Why are you preparing a query that has a concatenated variable in it and no parameters — RiggsFolly, Feb 16 '17 at 12:37
Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, Feb 16 '17 at 12:37

Your Common Sense · Accepted Answer · 2017-02-16T12:31:38.200

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

Thank you for your prompt response. Indeed I will correct that. However, it is still running the same error when the searched item is not found. How do I handle that error? — Michael Amiro, Feb 16 '17 at 12:28

mysql fetch assoc array empty error

1 Answers1