DBI foreach loop Question

[nwyork]

I was sent this code, but the @$row at the bottom does not work for me? I understand what it seems it is suppose to do, but it doesn't work. I don't find anything in the PERL manual pages about this.

$SQL = "select $variable, $variable from table where db.field = input";
@result = database_call('tracking', 'SELECT', $SQL);

if(!scalar(@result))
{
print "<tr><td colspan=5 align='center'>There are no orders pending shipment.</td></tr>\n";
return;
}

$OnRow = 0;

foreach $row(@result)
{
++$OnRow;

($variable,$variable) = @$row;

Has anybody used this or is there alternate way?

Thanks ahead of time
Nick

 

[audiopro]

Hi Nick
I'm curious - what is the code meant to do?


Keith

http://www.studiosoft.co.uk

 

[MikeLacey]

Nick,

That code doesn't call The DBI. Now it might be that the function database_call() uses The DBI but it's difficult for us to help with that kind of thing.

A DBI way of doing that would be:

### Prepare the SQL statement ( assuming $dbh exists )
$sth = $dbh->prepare( "
            select $variable, $variable
            from table
            where db.field = $input
" );

### Execute the SQL statement and generate a result set
$sth->execute();

### Fetch each row of result data from the database as a list
while ( ( $name, $type ) = $sth->fetchrow_array ) {
    print "$name, $type\n";
}

Mike

To err is human,
but to really foul things up -
you require a man Mike.

Want to get great answers to your Tek-Tips questions? Have a look at faq219-2884

 

[nwyork]

Hey, audiopro this code is to create a report. What I'm trying to do is grab a row from the table and work with it in each foreach loop. I think the @$ is for dereferencing with the DBI statement handle method $tbl_ary_ref = $sth->fetchall_arrayref() or the DBI statement handle method @row_ary = $dbh->selectrow_array($statement). I'm new to DBI so I'm still trying figure out which one to use.

 

[audiopro]

Never touched DBI but always willing to advance my knowledge.
I am more used to Foxpro which uses human type words rather than symbols found in Egyptian Pyramid paintings lol.
As the required data from your query is already an array of arrays, I would have thought it easier to access the array elements directly ie. $result[$x][$y].
I may however just be way off the mark.


Keith

http://www.studiosoft.co.uk

 

[MikeLacey]

nwyork - you don't need to dereference the array returned by $sth-fetchrow_array

Mike

To err is human,
but to really foul things up -
you require a man Mike.

Want to get great answers to your Tek-Tips questions? Have a look at faq219-2884

 

[nwyork]

I know I put the wrong statement method handle in. This is the code that I'm working on but not getting any where!

$sth1 = $dbh->prepare($SQL1);
$sth1->execute();
$results = $sth1->fetchall_arrayref();
@result = @$results;
foreach $row(@result) {
(variable, variable, etc..) = @$row;
print "<TD>$variable</TD>";
}

Any suggestions of what I possibly or am doing wrong. I've seen working code almost identical minus variable names and sql statement like this working. I know my sql statement is working.

Thanks ahead of time.

 

[MikeLacey]

Like this I think :-( I'm rubbish with references...

$results = $sth1->fetchall_arrayref();
foreach $row(@$results) {
($v1, $v2....) = @$row;
.
.
.

Mike

To err is human,
but to really foul things up -
you require a man Mike.

Want to get great answers to your Tek-Tips questions? Have a look at faq219-2884

 

[nwyork]

I'm not sure why that code in my previous code did not work for me last night, but it does now.

Thanks everybody that gave me some input on this!