hashing mutliple rows from sql results?

E

erik

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 said:
</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>";
}
 
I

ioneabu

erik said:
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?
 
E

erik

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?
 
E

erik

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.
 
A

A. Sinan Unur

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
 
E

erik

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

Gunnar Hjalmarsson

erik said:
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?
 
S

Sherm Pendley

erik said:
I have read them. Thanks.

What have you read??? Who are you thanking???

Please quote a little context when you post followup messages.

sherm--
 
T

Tad McClellan

Sherm Pendley said:
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.
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top