PHP SQLite3Result::fetchArray re-executes query

Question

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?

The LIMIT isn't influential. It merely makes the query stop earlier rather than going through the whole table. I've amended my post with what is echoed. — Taco de Wolff
– Taco de Wolff, Commented Oct 19, 2012 at 20:53

CL. · Accepted Answer · 2012-10-19 21:22:28Z

2

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.

answered Oct 19, 2012 at 21:22

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

code19 · Accepted Answer · 2012-10-21 23:23:32Z

0

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];
}

answered Oct 21, 2012 at 23:23

code19

855 bronze badges

1 Comment

Taco de Wolff Over a year ago

This is completely irrelevant. The problem is not attaching the regex functionality to SQL, but the fact that queries are executed twice. Which I discovered through using a custom regex function..

Collectives™ on Stack Overflow

PHP SQLite3Result::fetchArray re-executes query

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related