how create auto increment column on the fly in mysql with php pdo?

Question

Possible Duplicate:
Is there an equivalent for MySQL's “multi_query()” in PDO?

I have two table, I do a union from two table:

 select * 
 from table1
 union 
 select *
 from table2

I would want create an auto increment column on the fly for creating "more results" pagination passing this auto increment id as offset.

I can create auto increment column on the fly in this way:

SELECT @i:=0;

SELECT all_res.*, 
       @i:=@i+1 AS i 
FROM (
 select * 
 from table1
 union 
 select *
 from table2
) as all_res

but if I run queries in php pdo I obtain this error:

SQLSTATE[42000]: Syntax error or access violation: 1064 SELECT all_res.*, @i:=@i+1 AS i' at line 1

How can I do for obtain an unique column id to be able to "view more results"?

Just define the column that you want as `AUTO_INCREMENT` - http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html — Nir Alfasi, Sep 28 '12 at 08:50
see this post: [Get row number on select](http://stackoverflow.com/questions/2520357/mysql-get-row-number-on-select) — Farnabaz, Sep 28 '12 at 08:51
but I want auto_increment on union query result, in table1 and table2 I have already an auto_increment column — paganotti, Sep 28 '12 at 08:52
Are you trying to execute those two `SELECT`s in a single `PDO::query` call? — lanzz, Sep 28 '12 at 08:55
Comments turn out that user is just pushing stuff to the database without first checking if things are applicatable or not. then pushing errors into stackoverflow. Voting for duplicate now. — hakre, Sep 28 '12 at 08:58

score 2 · Accepted Answer · edited May 23 '17 at 11:48

2

PDO: one query at a time. Not two queries. Use mysqli for that it has multi query support.

See as well:

edited May 23 '17 at 11:48

Community

answered Sep 28 '12 at 08:55

hakre

In PDO isn't possible to do it? – paganotti Sep 28 '12 at 08:57
You can perform the two queries separately. Custom variables persist between queries, as long as you're in the same connection. – lanzz Sep 28 '12 at 09:00

1 Answers1