CODEWITHSUNDEEP
phpmysqlview
Dalen
Dalen

Reputation: 8986

Call parametric MySQL view from PHP

I'm trying to build a parametric view in mysql so that I can call it from PHP specifying the parameter.

There are two views actually: the first one which implements the parameter and the second one which just selects from the first:

CREATE VIEW first_view AS
SELECT * FROM my_table t WHERE t.parameter < get_parameter();

CREATE VIEW second_view AS
SELECT * FROM first_view;

The get_parameter function is implemented as follows:

CREATE FUNCTION get_parameter() RETURNS timestamp
    NO SQL
    DETERMINISTIC
BEGIN
    IF ISNULL(@parameter) THEN
        RETURN DATE(CURDATE()- INTERVAL DAYOFYEAR(CURDATE()) DAY);
    ELSE
        RETURN @parameter;
    END IF;
END

Then, my query, looks like this:

SELECT v.* FROM (select @parameter := '2014-12-31' p) par, first_view v

This kind of query allows me to specify the parameter and then select everything from first_view.

When I run such query on Mysql Workbench everything works as expected while when i run it via PHP it returns an empty set.

I'm using the same mysql user both on workbench and PHP so it shouldn't be a premission problem.

How can I get the result set with PHP too?

UPDATE

I've been trying this on the Mysql CLI client and it returns an empty set as PHP does.

It looks like the query:

SELECT v.* FROM (select @parameter := '2014-12-31' p) par, first_view v

doesn't actually sets the @parameter variable, or at least not the first time.

Infact, if I run the query twice then it works and the view returns what is expected. I solved the problem by sending two statements from php to mysql:

First I set the parameter:

SET @parameter = '2014-12-31';

the I run the actual query:

SELECT * FROM second_view;

Now I wonder: why the first query isn't instantiating the parameter correctly?

Upvotes: 1

Views: 839

Answers (1)

Gerd K
Gerd K

Reputation: 1147

From mysql reference:

A routine is considered “deterministic” if it always produces the same result for the same input parameters

Your function has no input parameters, but considers a variable declared outside of the function.

From mysql reference:

Declaring a nondeterministic routine as DETERMINISTIC might lead to unexpected results by causing the optimizer to make incorrect execution plan choices.

This is just speculation: But the reason why it works in MySQL workbench and not in CLI/PHP could be that query caching in the workbench session is probably disabled per default.

You could try to verify this disabling the cache during CLI query:

SELECT SQL_NO_CACHE v.* FROM (select @parameter := '2014-12-31' p) par, first_view v

Upvotes: 2

Related Questions