SQL Reports NOT allow CREATE TEMPORARY TABLES

[joseh55]

I need to create TWO temporary tables with a sql statement in SQL REPORTS...

CREATE TEMPORARY TABLE t1 (select * from enc1 where .....) CREATE TEMPORARY TABLE t2 (select * from enc2 where .....)

select t1.row1, t2.row2 FROM cdr inner join t1 on cdr.uniqueid=t2.id

BUT NO WORKS!!

SQL REPORTS ALLOW CREATE TEMPORARY TABLES???

[edu-bob]

Can you be more explicit about what error you get?

Also, make sure that the database user (in the 'Environment' section of config.php) has the proper database permissions to create temporary tables. But it can be more complicated than that, because you probably do not want your default database user in php-reports to be able to write to the database.

I have done this many times. What I do is to define two database configurations in config.php. The default one uses a database user who has read-only permission. Then I define a second one that has read-write permission. You do this in the pdo or mysql section of config.php where you define the host, user, and pass values for your SQL database. Add three more key/value pairs called host_rw, user_rw, and pass_rw and their values are the host, user, and password for read-write access to your database.

For example, your read-only settings (host,user,pass) could refer to a user on a slave database, whereas your read-write settings (host_rw, user_rw, pass_rw) would probably refer to your master server.

Then, in your report file (foo.sql) you need to have a header -- OPTIONS: access=rw This will cause php-reports to use the read-write parameter for creating the database connection.