brewman
asked on
Help write a small part of a program!
I extract information from a database using an SQL statement. There is basically 3 colums with many rows of data. Anyway, I end up taking the first row and using push to got to a list.
I end up with somthing like this?
@somelist=("A","A","A","B" ,"B","C"," C")
These are just an example and are not the same data but basically, I want to be able to make the list look like this:
@somelist=("A"," "," ","B"," ","C"," ")
Basically replacing the elements found more than once with a blank space. I am trying to write a report back as a table but don't want show this A or B or C or ? more than once.
The table would end up looking like this:
A x x
x x
B x x
x x
C x x
x x
These two other colums are different arrays and can't be touched so I am only concerned with the first list. The criteria is that the element is not known but once it is identified as being there, the rest of the same elements must be replaced with a blank space......
I don't know any other way to put this so let me know if you need more information.
Mike
I end up with somthing like this?
@somelist=("A","A","A","B"
These are just an example and are not the same data but basically, I want to be able to make the list look like this:
@somelist=("A"," "," ","B"," ","C"," ")
Basically replacing the elements found more than once with a blank space. I am trying to write a report back as a table but don't want show this A or B or C or ? more than once.
The table would end up looking like this:
A x x
x x
B x x
x x
C x x
x x
These two other colums are different arrays and can't be touched so I am only concerned with the first list. The criteria is that the element is not known but once it is identified as being there, the rest of the same elements must be replaced with a blank space......
I don't know any other way to put this so let me know if you need more information.
Mike
I thought I knew what you were talking about, but then re-read, and changed my mind. Tell you what, post the sql that you read with, and then the sql that you write with. BTW, it isn't that difficult, just a matter of expressing it properly.
ASKER
sub PS {
#Convert Dates to proper SQL syntax
&DateFormat;
if (!($db=new Win32::ODBC("DSN=SNASTPP;U ID=mlammon "))) {
print "Error connecting to $DSN\n";
print "Error: " . Win32::ODBC::Error() . "\n";} else {
$Sql="
SELECT DISTINCTROW tbl_SNAS_TPP_Data.Site, tbl_SNAS_TPP_Data.ServiceP roduct, Count(tbl_SNAS_TPP_Data.Se rviceProdu ct) AS CountOfServiceProduct
FROM tbl_SNAS_TPP_Data
WHERE (((tbl_SNAS_TPP_Data.DateT imeOut) Between CDate(#$start#) And CDate(#$stop#)))
GROUP BY tbl_SNAS_TPP_Data.Site, tbl_SNAS_TPP_Data.ServiceP roduct
HAVING (((tbl_SNAS_TPP_Data.Site) Is Not Null) AND ((tbl_SNAS_TPP_Data.Servic eProduct) Is Not Null))
ORDER BY tbl_SNAS_TPP_Data.Site, Count(tbl_SNAS_TPP_Data.Se rviceProdu ct) DESC;";
}
if ($db->Sql($Sql)) {
print "SQL failed.\n";
print "Error: " . $db->Error() . "\n";
} else {
while($db->FetchRow()) {
%Data = $db->DataHash();
($site,$service,$count)=($ Data{"Site "},$Data{" ServicePro duct"},$Da ta{"CountO fServicePr oduct"});
push (@a,$site);push (@b,$service);push (@c,$count);
}
}
$count=@a;
print "<html>\n";
print "<title>SERVICE VS PRODUCT FROM $start TO $stop</title>\n";
print "<body>\n";
print "<table border=1>\n";
print "<tr><th>Site</th><th>Serv ice</th><t h>Number Of Entries</th></tr>\n";
for ($i=0;$i<$count;$i++) {
print "<tr><td>" . $a[$i] . "</td>" . "<td>" . $b[$i] . "<td>" . $c[$i] . "</td></tr> \n";}
print "</table>\n";
print "</body>\n";
print "</html>\n";
$db->Close();
}
You see the list is @a is all the sites from the query: The table in html comes out like this:
SITE1 PRODUCTX some#
SITE1 PRODUCTY some#
SITE1 PRODUCTZ some#
SITE2 PRODUCTT some#
SITE2 PRODUCTM some#
but I want it to look like:
SITE1 PRODUCTX some#
PRODUCTY some#
PRODUCTZ some#
SITE2 PRODUCTT some#
PRODUCTM some#
etc....
Does this help!
#Convert Dates to proper SQL syntax
&DateFormat;
if (!($db=new Win32::ODBC("DSN=SNASTPP;U
print "Error connecting to $DSN\n";
print "Error: " . Win32::ODBC::Error() . "\n";} else {
$Sql="
SELECT DISTINCTROW tbl_SNAS_TPP_Data.Site, tbl_SNAS_TPP_Data.ServiceP
FROM tbl_SNAS_TPP_Data
WHERE (((tbl_SNAS_TPP_Data.DateT
GROUP BY tbl_SNAS_TPP_Data.Site, tbl_SNAS_TPP_Data.ServiceP
HAVING (((tbl_SNAS_TPP_Data.Site)
ORDER BY tbl_SNAS_TPP_Data.Site, Count(tbl_SNAS_TPP_Data.Se
}
if ($db->Sql($Sql)) {
print "SQL failed.\n";
print "Error: " . $db->Error() . "\n";
} else {
while($db->FetchRow()) {
%Data = $db->DataHash();
($site,$service,$count)=($
push (@a,$site);push (@b,$service);push (@c,$count);
}
}
$count=@a;
print "<html>\n";
print "<title>SERVICE VS PRODUCT FROM $start TO $stop</title>\n";
print "<body>\n";
print "<table border=1>\n";
print "<tr><th>Site</th><th>Serv
for ($i=0;$i<$count;$i++) {
print "<tr><td>" . $a[$i] . "</td>" . "<td>" . $b[$i] . "<td>" . $c[$i] . "</td></tr> \n";}
print "</table>\n";
print "</body>\n";
print "</html>\n";
$db->Close();
}
You see the list is @a is all the sites from the query: The table in html comes out like this:
SITE1 PRODUCTX some#
SITE1 PRODUCTY some#
SITE1 PRODUCTZ some#
SITE2 PRODUCTT some#
SITE2 PRODUCTM some#
but I want it to look like:
SITE1 PRODUCTX some#
PRODUCTY some#
PRODUCTZ some#
SITE2 PRODUCTT some#
PRODUCTM some#
etc....
Does this help!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
$hold='';
@a=map{$_ eq $hold?'':($hold=$_)}@a;
@a=map{$_ eq $hold?'':($hold=$_)}@a;
ASKER
b2pi:
I used:
$hold = '';
for ($i=0;$i<$count;$i++) {
print "<tr><td>";
if ($a[$i] ne $hold) {
$hold = $a[$i];
print "$hold";}else {print " ";}
print "</td><td>$b[$i]</td><td>$ c[$i]</td> </tr> \n";
}
print "</table>\n";
print "</body>\n";
print "</html>\n";
and it works great! Thanks man!! I now learned somthing new. This little trick will come in handy!
Again.. Thanks!
I used:
$hold = '';
for ($i=0;$i<$count;$i++) {
print "<tr><td>";
if ($a[$i] ne $hold) {
$hold = $a[$i];
print "$hold";}else {print " ";}
print "</td><td>$b[$i]</td><td>$
}
print "</table>\n";
print "</body>\n";
print "</html>\n";
and it works great! Thanks man!! I now learned somthing new. This little trick will come in handy!
Again.. Thanks!