Re: Ordinal number within a table

Kapoor, Nishikant wrote:

 > [Sorry for cross-posting.]
 >
 > This is in continuation with the above mentioned subject - I am trying to
 > find the 'display data order' for the returned resultset. The following
 > thread very well answers my question:
 >
 >  http://lists.mysql.com/mysql/185626
 >
 > a) SET @row=0;
 > b) SELECT (@row:=@row+1) AS row, ename, empno FROM emp ORDER BY empno;
 >
 > +-----+--------+-------+
 > | row | ename  | empno |
 > +-----+--------+-------+
 > |   1 | SMITH  |  7369 |
 > |   2 | ALLEN  |  7499 |
 > |   3 | WARD   |  7521 |
 > |   4 | JONES  |  7566 |
 > |   5 | MARTIN |  7654 |
 > +-----+--------+-------+
 >
 > However, I am trying to use it in a perl script instead of from command
 > line, and I am not sure how exactly to do it. I need to execute both
 > statements a & b together ...

You cannot execute them together.  You must execute them one at a time, in the 
same connection.

 > ... or else I get
 >
 > +-----+--------+-------+
 > | row | ename  | empno |
 > +-----+--------+-------+
 > |NULL | SMITH  |  7369 |
 > |NULL | ALLEN  |  7499 |
 > |NULL | WARD   |  7521 |
 > |NULL | JONES  |  7566 |
 > |NULL | MARTIN |  7654 |
 > +-----+--------+-------+

If you are getting this, you've made a mistake in your perl code.  It's hard 
to say what, though, as you haven't shown us your code.

Hmmm.  User variables are connection specific.  Are you making the mistake of 
opening and closing a connection for each query?  That's unneccessary, and it 
adds a lot of overhead.

 > How can I execute both (a) and (b) in my perl script?

The same way you would execute any two statements, one at a time.  Something like:

   $conn->do('SET @row=0');
   my $sql = 'SELECT @row:=@row+1 as row, <fields> FROM <tables> WHERE ...';
   my $sth = $conn->prepare($sql);
   $sth->execute();
   return $sth->fetchall_arrayref( {} );

 > Thanks for any help.
 > Nishi

Mathias wrote:

 > Hi,
 > You don"t need to use @row in perl,
 > just use :
 >
 > $n=0;
 > while (fetch..) {
 > $n++;
 > print "$n"."$ename ...\n";
 >
 > }

That would work.

Kapoor, Nishikant wrote:

 > I could, but I am assigning the entire resultset in one shot to another
 > construct as follows:
 >
 > my $str  = "SELECT \@row:=\@row+1 as row, <fields> FROM <tables> WHERE ...";
 > my $sth = $conn->prepare($st);
 > $sth->execute();
 > return $sth->fetchall_arrayref( {} );
 >
 > Thanks,
 > -Nishi

You are returning an arrayref!  One row in your results equals one row in your 
array -- in the same order!  Arrays are indexed, so display data order is 
already built into your array.  Display position = array position + 1.  Why do 
you need a redundant field in each row?

Harald Fuchs wrote:

 > Just change the last line to
 >
 >   my $n = 0;
 >   return [ map { [ ++$n, @$_ ] } @{$sth->fetchall_arrayref} ];
 >
 > What's the problem?

Are you sure?  I get "Can't coerce array into hash at...".

Mathias wrote:

 > Then alter your table to add an auto_increment column, update it and play
 > your query without @row.
 >
 > Mathias

No, no, no!  This is what we call using a shotgun to kill a gnat.  It also 
doesn't yield the order of the query results.

Michael

-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/[email protected]