FAQ
hi

i'm realy a newbie to DBI and mostly all works, what i have done and found
out with the manuals and examples around the net... but their are two
problems around "select" - i cannot solve myself. i'm using DBD-ODBC and
MsSQL2K.

1. how can i put the output of a select query to a array?
2. i need a count of the selected/found rows (for e.g. i select users and
like to display how many users i have collected with this statement)


Alex

Search Discussions

  • Paul Boutros at Jan 28, 2003 at 11:11 pm
    Both questions are in the manual, which you can reread many times w/o
    fully grasping all the details.

    <snip>
    1. how can i put the output of a select query to a array?
    my $dbh = DBI->connect('<your connect string>', $user, $pass);
    my $sql = 'Select field1, field2 from temp';
    my $sth = $dbh->prepare($sql);

    $sth->execute();

    while (my @row = $sth->fetchrow_array()) {
    print join("\t", @row), "\n";
    }

    $dbh->disconnect();
    2. i need a count of the selected/found rows (for e.g. i select users and
    like to display how many users i have collected with this statement)
    In the above code use:

    my $sql = 'SELECT COUNT(*) FROM temp';

    or:

    my $i = 0;
    while (my @row = $sth->fetchrow_array()) {
    $i++;
    }

    HTH,
    Paul
  • Alex at Jan 28, 2003 at 11:25 pm

    while (my @row = $sth->fetchrow_array()) {
    print join("\t", @row), "\n";
    }
    i've tryed this... but it hasn't worked... i will re-try our...
    my $sql = 'SELECT COUNT(*) FROM temp';
    yes - but this will result in a additional select and therefor more database
    activity :-(
    my $i = 0;
    while (my @row = $sth->fetchrow_array()) {
    $i++;
    }
    very difficult in my code... i will have a look if i can change the code.
    isn't it possible without a "while" ?


    Alex
  • Paul Boutros at Jan 28, 2003 at 11:33 pm

    my $sql = 'SELECT COUNT(*) FROM temp';
    yes - but this will result in a additional select and therefor more database
    activity :-(
    Not a huge cost if it's against an index....
    my $i = 0;
    while (my @row = $sth->fetchrow_array()) {
    $i++;
    }
    very difficult in my code... i will have a look if i can change the code.
    isn't it possible without a "while" ?
    How did you plan on *retrieving* the data if not through a loop? If you
    plan on actually getting all the data, you'll have to loop through all the
    rows I think. Maybe you could post a code fragment?
  • Alex at Jan 28, 2003 at 11:52 pm
    very difficult in my code... i will have a look if i can change the
    code.
    isn't it possible without a "while" ?
    How did you plan on *retrieving* the data if not through a loop? If you
    plan on actually getting all the data, you'll have to loop through all the
    rows I think. Maybe you could post a code fragment?
    here is the old code... based ob a file

    my @data=split(/\r?\n/,$part[2]);

    if ($data[2] ne '') {
    my @datas=split(';',$data[2]);
    print "Counted Items ";print $#datas+1;print" Items<BR>\n";
    print "Inside: ";foreach (sort @datas) {print "Item: $_ ";} print "<BR>\n";
    }



    Alex
  • Paul Boutros at Jan 29, 2003 at 12:03 am

    How did you plan on *retrieving* the data if not through a loop? If you
    plan on actually getting all the data, you'll have to loop through all the
    rows I think. Maybe you could post a code fragment?
    here is the old code... based ob a file

    my @data=split(/\r?\n/,$part[2]);

    if ($data[2] ne '') {
    my @datas=split(';',$data[2]);
    print "Counted Items ";print $#datas+1;print" Items<BR>\n";
    print "Inside: ";foreach (sort @datas) {print "Item: $_ ";} print "<BR>\n";
    }
    Not sure I see your problem. Rather than splitting from a file, you're
    selecting from an array. Instead of doing a foreach{} loop you just do a
    while loop. Doesn't change the logic at all.

    my $sql = 'SELECT field FROM DATA ORDER BY field';
    my $sth = $dbh->prepare($sql);
    $sth->execute();

    while (my $row = $sth->fetchrow_array()) {
    $i++;
    $html .= "Item: $row ";
    }

    print "Counted Items $i Items<BR>",
    "Inside $html<BR>";
  • Alex at Jan 29, 2003 at 12:22 am
    $html .= "Item: $row ";
    oh *g* - thats the idea :-) - a point for appending the $html string... i
    will change this... sometimes if you look too long on a code you get
    blockhead... :-)


    thx
    Alex
  • Stephane Perennes at Jan 29, 2003 at 7:21 am
    If you only need to get the table size,
    musql_affected_row is the function to call,
    i'm not sure of the function name under the perl
    APi,

    But you may need more, I got exactly that problem when trying to select a
    few items
    from a database while displaying only a few of them
    (take as example google and the pages system)


    It happens basically you select items using a MAX option
    (saying that you want only MAX items, and a starting item options
    (something like LIMIT 10 30 output up to 30 rows starting from row 10),
    note that in such cases the order
    is relevant.


    In this case some databases allow you to access the total number
    of selected items before the conditionnal
    on the maxnumber (and starting item)

    As example with Mysql 4 some function (select found_rows()) return this
    number (which is different from the number of rows in the table that you
    get).

    mysql_affected_rows returns the size of the table that you get,
    or the number of rows affected by an update, delete or anything.


    Note that if you use a count(*) you will get troubles
    if you also use a "group by" modifier,

    Select count(*) from Student, Class where Student.ID=Class.StudentID
    and Class.Name like "Maths%" group by Student.ID
    will return a table where for each sutdent we find the number of
    Maths courses she/he attempts (Eg if we have Maths1 and Maths2 etc ..)
    and not the total number of row in the resulting table.
  • David N Murray at Jan 29, 2003 at 12:31 am
    Of course, you can do it all with array references if your a glutton for
    punishment. I can never remember how and always have to look it up with
    working code.

    my $rs = $dbh->selectall_arrayref("select * from table");
    # count
    print "count: $#$rs\n";
    # each row
    my $rr;
    foreach $rr (@$rs) {
    print "col1: $rr->[0] col2 $rr->[1]\n";
    }

    The book is a useful reference, too.

    Dave

    On Jan 29, alex scribed:
    while (my @row = $sth->fetchrow_array()) {
    print join("\t", @row), "\n";
    }
    i've tryed this... but it hasn't worked... i will re-try our...
    my $sql = 'SELECT COUNT(*) FROM temp';
    yes - but this will result in a additional select and therefor more database
    activity :-(
    my $i = 0;
    while (my @row = $sth->fetchrow_array()) {
    $i++;
    }
    very difficult in my code... i will have a look if i can change the code.
    isn't it possible without a "while" ?


    Alex
  • Alex at Jan 31, 2003 at 10:57 pm

    my $rs = $dbh->selectall_arrayref("select * from table");
    # count
    print "count: $#$rs\n";
    one user told me - and i read about, too. this row counter will not give
    correct results on some database servers... i'm not sure if mssql will work
    correctly, but this may create problems if you like to program platform
    independend.


    Alex
  • Kåre Olai Lindbach at Jan 29, 2003 at 12:32 am

    On Wed, 29 Jan 2003 00:28:07 +0100, you ("alex" wrote:
    while (my @row = $sth->fetchrow_array()) {
    print join("\t", @row), "\n";
    }
    i've tryed this... but it hasn't worked... i will re-try our...
    my $sql = 'SELECT COUNT(*) FROM temp';
    yes - but this will result in a additional select and therefor more database
    activity :-(
    my $i = 0;
    while (my @row = $sth->fetchrow_array()) {
    $i++;
    }
    very difficult in my code... i will have a look if i can change the code.
    isn't it possible without a "while" ?
    Shouldn't you look into "selectall_arrayref", then you would get all
    rows as an arrayref, and each element of this is an arrayref to all
    the columns returned.

    my $aryref = $dbh->selectall_arrayref("SELECT * FROM temp");
    if(!defined $aryref and !@$aryref) {
    print "Nothing to show!\n";
    die; # or preferbly something else
    }
    print scalar @$aryref, " rows found\n";

    #The lines are:

    foreach my $cols (@$aryref) {
    print "Col:",join(";",@$cols),"\n";
    }

    # You might use map instead, but it is a sort of loop anyhow.

    # Untested!

    ... and I usually do a map iterating over the col values to set undef
    cols to ex. "" like:

    my @cols = map {!defined($_) && $_ = '';$_} @$cols;

    ... still after memory, and untested!

    --
    mvh/Regards
    Kåre Olai Lindbach

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedJan 28, '03 at 11:02p
activeJan 31, '03 at 10:57p
posts11
users5
websitedbi.perl.org

People

Translate

site design / logo © 2022 Grokbase