Solved

Help write a small part of a program!

Posted on 1998-09-30
5
140 Views
Last Modified: 2010-03-05
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
0
Comment
Question by:brewman
  • 2
  • 2
5 Comments
 
LVL 5

Expert Comment

by:b2pi
ID: 1205047
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.
0
 

Author Comment

by:brewman
ID: 1205048
sub PS {

#Convert Dates to proper SQL syntax
&DateFormat;

if (!($db=new Win32::ODBC("DSN=SNASTPP;UID=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.ServiceProduct, Count(tbl_SNAS_TPP_Data.ServiceProduct) AS CountOfServiceProduct
FROM tbl_SNAS_TPP_Data
WHERE (((tbl_SNAS_TPP_Data.DateTimeOut) Between CDate(#$start#) And CDate(#$stop#)))
GROUP BY tbl_SNAS_TPP_Data.Site, tbl_SNAS_TPP_Data.ServiceProduct
HAVING (((tbl_SNAS_TPP_Data.Site) Is Not Null) AND ((tbl_SNAS_TPP_Data.ServiceProduct) Is Not Null))
ORDER BY tbl_SNAS_TPP_Data.Site, Count(tbl_SNAS_TPP_Data.ServiceProduct) 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{"ServiceProduct"},$Data{"CountOfServiceProduct"});
 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>Service</th><th>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!
0
 
LVL 5

Accepted Solution

by:
b2pi earned 200 total points
ID: 1205049
Oh, if that's all, the easiest way to do it might be this...

$hold = '';
for ($i=0;$i<$count;$i++) {
       print "<tr><td>";
       if ($a[$i] ne $hold) {
           $hold = $a[$i];
           print "$hold";
      }
     print "</td><td>$b[$i]<td>$c[$i]</td></tr> \n";
       print "</table>\n";
       print "</body>\n";
       print "</html>\n";

       $db->Close();
 }
0
 
LVL 84

Expert Comment

by:ozo
ID: 1205050
$hold='';
@a=map{$_ eq $hold?'':($hold=$_)}@a;

0
 

Author Comment

by:brewman
ID: 1205051
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 "&nbsp";}
 
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!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (http://dilbert.com/strips/comic/2007-08…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now