Re: Trying to execute several queries involving temp tables in a PHP script

Hi Alexander,

I think you can have all in one query, without temp tables:

SELECT r.rid, r.cards, to_char(r.stamp, 'DD.MM.YYYY
HH24:MI') as day,
c.bid, c.trix, c.pos, c.money, c.last_ip, c.quit,
u.id, u.first_name, u.avatar, u.female, u.city,
u.vip > CURRENT_DATE as vip
FROM pref_rounds r, pref_cards c, pref_users u
WHERE u.id = c.id and
r.rid = c.rid and
r.rid in (

select rid
from pref_cards
where stamp > now() - interval '1 day' and
id in (
select id
from pref_money
where yw = to_char(current_timestamp - interval '1
week', 'IYYY-IW')
order by money
desc limit 10

) and
bid = 'Misere' and
trix > 0

)
order by rid, pos;

2012/6/13 Alexander Farber <alexander(dot)farber(at)gmail(dot)com>

> Hello fello PostgreSQL users,
>
> with PHP 5.3.3 and PostgreSQL 8.4.11
> (and a pgbouncer, but I've tried without it too)
> I'm trying to execute several SQL queries
> with 2 temp tables (listed below) and then use
> the result of a final join to construct a JSON array.
>
> Unfortunately my script using prepare/execute
> (and I've tried query() too) fails with PHP error:
> "cannot insert multiple commands into a prepared statement".
>
> And when I split my statements into multiple
> prepare()/execute() or query() calls,
> then the temp. tables aren't found anymore.
>
> Any ideas please on how to handle this situation
> in PHP scripts, do I really have to encapsulate
> my calls into a pl/PgSQL function?
>
> More details on my query and setup:
>
> http://stackoverflow.com/questions/11010784/error-cannot-insert-multiple-commands-into-a-prepared-statement
>
> And below is my PHP code:
>
> try {
> $options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);
> $db = new PDO(sprintf('pgsql:host=%s port=%u; dbname=%s',
> DBHOST, DBPORT, DBNAME), DBUSER, DBPASS, $options);
>
> $sth = $db->prepare("
> start transaction;
> create temporary table temp_ids (id varchar not null) on
> commit drop;
> insert into temp_ids (id)
> select id
> from pref_money
> where yw = to_char(current_timestamp - interval '1
> week', 'IYYY-IW')
> order by money
> desc limit 10;
>
> create temporary table temp_rids (rid integer not null) on
> commit drop;
> insert into temp_rids (rid)
> select rid
> from pref_cards
> where stamp > now() - interval '1 day' and
> id in (select id from temp_ids) and
> bid = 'Misere' and
> trix > 0;
>
> SELECT r.rid, r.cards, to_char(r.stamp, 'DD.MM.YYYY
> HH24:MI') as day,
> c.bid, c.trix, c.pos, c.money, c.last_ip, c.quit,
> u.id, u.first_name, u.avatar, u.female, u.city,
> u.vip > CURRENT_DATE as vip
> FROM pref_rounds r, pref_cards c, pref_users u
> WHERE u.id = c.id and
> r.rid = c.rid and
> r.rid in (select rid from temp_rids)
> order by rid, pos;
> commit;
> ");
> $sth->execute();
> while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
> # construct a JSON array of objects
> }
> } catch (Exception $e) {
> exit('Database problem: ' . $e->getMessage());
> }
>
> Thank you
> Alex
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>