CODEWITHSUNDEEP
phpsqlite
Taco de Wolff
Taco de Wolff

Reputation: 1758

PHP SQLite3Result::fetchArray re-executes query

I attached a function to the REGEXP keyword in PHP and I discovered that SQLite3Result::fetchArray re-executes the query! What inefficiency!

function _sqliteRegexp($pattern, $string)
{
    echo $pattern . ' - ' . $string . '<br/>';
    return preg_match('/' . $pattern . '/', $string);
}

$handle = new SQLite3($filename);
$handle->createFunction('regexp', '_sqliteRegexp', 2);

// (1)
$result = $handle->query("SELECT * FROM pages WHERE '" . $request_uri . "' REGEXP request LIMIT 1;");

// (2)
$result->fetchArray();

Giving, for example:

(1)
\/ - /admin/settings/1
\/sqlite - /admin/settings/1
\/admin - /admin/settings/1
\/admin\/logout - /admin/settings/1
\/admin\/settings(\/[0-9]+)? - /admin/settings/1

(2)
\/ - /admin/settings/1
\/sqlite - /admin/settings/1
\/admin - /admin/settings/1
\/admin\/logout - /admin/settings/1
\/admin\/settings(\/[0-9]+)? - /admin/settings/1

Both (1) and (2) echo the pattern and string the very same. I highly suspect the query is re-executed, which would be quite redundant. Is this a bug or just me being hastily concluding?

Upvotes: 1

Views: 1161

Answers (2)

code19
code19

Reputation: 75

Check the methode "sqlite_regExp" on Havalite: http://www.havalite.com/?p=98

Works also with LIMIT:

if($rows = sqlite_regExp("SELECT * FROM myTable WHERE preg_match('/sql(lite)*/ui', myRow) LIMIT 1")){
    foreach($rows as $row) echo $row[0];
}

Upvotes: 0

CL.
CL.

Reputation: 180060

A look into the source code shows that query fetches the first row just to determine whether the result is empty, or not. The first call to fetchArray will execute the query again. (So much for efficiency. Or side effects.)

If you are interested in no more than one row, use querySingle instead.

To answer your question: this works as designed. And that design is a bug.

Upvotes: 2

Related Questions