hashing mutliple rows from sql results?

Discussion in 'Perl Misc' started by erik, May 15, 2005.

  1. erik

    erik Guest

    I am doing an sql query that can sometimes return multiple rows of
    data. For example I might get 3 rows of results, and in field $STATUS
    one row might be an O for operational and another row might be F for
    failed. I am looking to check one row at a time for certain
    information. Is there a way to put a key:value pair on each ROW, and
    then call that row when needed and get all the values from my sql
    query. How can I label each row and get all the data in it? I could put
    a counter in here and unshift it into an array but I don't think I
    would be able to get all the data that is in that row.

    I am not sure if hashing will work becuase I don't know if you can have
    multiple value's per key. I hope I am wrong.



    ################################################################
    # LINK QUERY #
    ################################################################
    sub db_link_query{


    my $link_sql = qq( select link.status, link.service_type,
    link.bandwidth, physica
    l_link.pln, router_interface.router_id,
    router_interface.router_if_id,link.fms_ck
    t_stus_cd, link.fms_no, link.fr_net_addr, physical_link.ckt_id,
    physical_link.alt
    _ckt_id,physical_link.alt_crr_id, physical_link.lec_trbl_num,
    link.link_type,link
    ..link_encaps,physical_link.dial_line_type,link.OPL_XCPT_REAS_TXT,
    physical_link.T
    RPT_TYP_CD from router_interface, link, physical_link, device where
    physical_li
    nk.link_id = link.link_id and router_interface.router_id =
    device.device_id and d
    evice.device_id = '$device' and router_interface.router_id =
    link.router_id_a and
    router_interface.router_if_id = link.router_if_id_a order by
    router_interface.r
    outer_id ASC);

    my $sth = $dbh->prepare($link_sql);
    #Execute SQL Statement
    $sth->execute or die "DBI::execute : $!";

    print "Content-type: text/html\n\n";
    print "<html><head><title>DEVICE QA REPORT for $device</title></head>";
    print "<body bgcolor=#dddddd text=$green>";
    print "<p align=center><b><font color=#000000 size=6>DEVICE QA
    REPORT</font></b>";
    print "<br>";
    print "<p align=center><font color=#000000>Submitted on $dateStamp[0]
    EST</font><
    /p>";
    print "<br>";
    print "<p align=center><b><font color=#000000 size=3>DEVICE NAME:
    $device</font></p
    ></b>";
    ></b>";

    print "<br>";

    while (@link = $sth->fetchrow_array) {
    ($STATUS,$SERVICE_TYPE,$BANDWIDTH,$PLN,$ROUTER_ID,$
    ROUTER_INTERFACE,$FMS_CKT_ST
    ATUS,$FMS_NO,$FR_NET_ADDR ,$CKT_ID, $ALT_CKT_ID, $ALT_CRR_ID,
    $LEC_TRBL_NUM,$LINK
    _TYPE,$LINK_ENCAPS,$DIAL_LINE_TYPE,$OPL_XCPT_REAS_TXT,$TRPT_TYP_CD
    )=@link;


    print <<link_contents;
    <table width="92%" border="1" height="65">
    <tr align="center" valign="middle">
    <td width="5%" height="81"><font
    color="$black"><b>Status</b></font></td>
    <td width="5%" height="81"><font color="$black"><b>Service
    Type</td>
    <td width="5%" height="81"><font
    color="$black"><b>Bandwidth</b></td>
    <td width="11%" height="81"><font color="$black"><b>PL</b></td>
    <td width="10%" height="81"><font color="$black"><b>Router
    IF</b></td>
    <td width="10%" height="81"><font color="$black"><b>FMS Ckt
    STATUS</b></td>
    <td width="7%" height="81"><font color="$black"><b>FMS NO.</b></td>
    (MORE OF THE SAME, SNIPPED, BUT I PRINT ALL THE ROWS IN A NICE HTML
    TABLE)

    </tr>
    link_contents
    }
    $sth->finish();
    print "</table>";
    }
    erik, May 15, 2005
    #1
    1. Advertising

  2. erik

    erik Guest

    erik, May 15, 2005
    #2
    1. Advertising

  3. erik

    Guest

    erik wrote:
    > I am doing an sql query that can sometimes return multiple rows of
    > data. For example I might get 3 rows of results, and in field $STATUS
    > one row might be an O for operational and another row might be F for
    > failed. I am looking to check one row at a time for certain
    > information. Is there a way to put a key:value pair on each ROW, and
    > then call that row when needed and get all the values from my sql
    > query. How can I label each row and get all the data in it? I could

    put
    > a counter in here and unshift it into an array but I don't think I
    > would be able to get all the data that is in that row.
    >
    > I am not sure if hashing will work becuase I don't know if you can

    have
    > multiple value's per key. I hope I am wrong.


    my %myhash;
    $myhash{mykey} = ['one','two','three']; #anonymous array as hash value
    print $myhash{mykey}->[0]; #print the first array element
    #multiple values per key
    #is that what you meant?
    , May 15, 2005
    #3
  4. erik

    erik Guest

    I guess something like:

    my %link_results;

    $link_results{mykey} = ['$STATUS','$SERVICE_TYPE','$BANDWIDTH'];
    #anonymous array as hash value
    print $link_results{mykey}->[0,1,2 and so on]; #print all elements of
    array
    #multiple values per key

    But my key would have to have a distinct number associated with it. So
    I could reference different rows of data.

    My data stream from the database looks like:

    O VPN 1544 etc....
    F FRAME 1544 etc...
    O FRAME 45000 etc...

    So since my hash is in a while loop I would like to assign a distinct
    value to be able to grab F FRAME and 1544 from row number 1 (0 indexed
    I am guessing).

    So I guess I would do a foreach loop inside the while loop and use your
    hash code provided. I would use a foreach loop to assign a counter to
    'mykey.' I wonder if that would work?
    erik, May 15, 2005
    #4
  5. erik

    erik Guest

    I am reading more and more and determined I need fetchall_arrayref.

    while (@link = $sth->fetchrow_array)

    I changed this to fetchrow_arrayref and it returned, ARRAY(0x41d490).
    Not sure what that is but if I can reference that and get all colums I
    will be psyched.
    erik, May 15, 2005
    #5
  6. "erik" <> wrote in news:1116173256.470778.26730
    @o13g2000cwo.googlegroups.com:

    > I am reading more and more


    In all your reading, have you not run into the posting guidelines for
    this group?

    > and determined I need fetchall_arrayref.


    Good for you. But please quote some context when you are replying.

    > while (@link = $sth->fetchrow_array)
    >
    > I changed this to fetchrow_arrayref and it returned, ARRAY(0x41d490).
    > Not sure what that is


    It is a reference to an array (which is what you asked for).

    > but if I can reference that and get all colums I will be psyched.


    You mean 'dereference'. See

    perldoc perlreftut

    Sinan

    --
    A. Sinan Unur <>
    (reverse each component and remove .invalid for email address)

    comp.lang.perl.misc guidelines on the WWW:
    http://mail.augustmail.com/~tadmc/clpmisc/clpmisc_guidelines.html
    A. Sinan Unur, May 15, 2005
    #6
  7. erik

    erik Guest

    Please explain how I am not complying with the guidelines???? Your
    input has not helped at all.
    erik, May 15, 2005
    #7
  8. erik wrote:
    > A. Sinan Unur wrote:
    >> "erik" wrote:
    >>> I am reading more and more

    >>
    >> In all your reading, have you not run into the posting guidelines for
    >> this group?

    >
    > Please explain how I am not complying with the guidelines???? Your
    > input has not helped at all.


    One way for you to learn about the posting guidelines would be that
    Sinan explained them to you.

    Another way would be that you read them.

    Which way makes most sense to you?

    --
    Gunnar Hjalmarsson
    Email: http://www.gunnar.cc/cgi-bin/contact.pl
    Gunnar Hjalmarsson, May 15, 2005
    #8
  9. erik

    erik Guest

    I have read them. Thanks.
    erik, May 16, 2005
    #9
  10. Sherm Pendley, May 16, 2005
    #10
  11. Sherm Pendley <> wrote:
    > erik wrote:
    >> I have read them. Thanks.

    >
    > What have you read???



    I think he is saying that he has read the Posting Guidelines.


    > Please quote a little context when you post followup messages.



    Looks like he does not intend to follow them, just that he has read them.


    --
    Tad McClellan SGML consulting
    Perl programming
    Fort Worth, Texas
    Tad McClellan, May 16, 2005
    #11
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. David
    Replies:
    0
    Views:
    412
    David
    Nov 3, 2004
  2. Michael B Allen

    [nio] Mutliple Messages in ByteBuffer

    Michael B Allen, Dec 13, 2004, in forum: Java
    Replies:
    1
    Views:
    401
    Esmond Pitt
    Dec 13, 2004
  3. Dominique
    Replies:
    3
    Views:
    446
    Dominique
    Jun 29, 2004
  4. Ami

    Mutliple Drop down list boxes

    Ami, Aug 25, 2006, in forum: ASP .Net
    Replies:
    1
    Views:
    379
    =?Utf-8?B?QXVndXN0aW4gUHJhc2FubmE=?=
    Aug 25, 2006
  5. Replies:
    3
    Views:
    293
    Phil Wilks
    Nov 7, 2006
Loading...

Share This Page