Solved

Query Problem Want distinct

Posted on 2001-08-09
5
235 Views
Last Modified: 2013-12-25
Tables in MySQL

Table Count:


uid|vendor|date|time|page|url|count
1|1|08-03-2001|12:00|a.html|http://www.wiley.com/science|1
2|1|08-04-2001|12:00|a.html|http://www.wiley.com/science|1
3|1|08-04-2001|12:00|b.html|http://www.wiley.com/math|1
4|1|08-05-2001|12:00|a.html|http://www.wiley.com/science|1
5|2|08-05-2001|12:00|j.html|http://www.johnson.com|1
6|2|08-05-2001|12:00|j.html|http://www.johnson.com|1
7|1|08-05-2001|12:00|b.html|http://www.wiley.com/math|1
8|1|08-05-2001|12:00|a.html|http://www.wiley.com/science|1
9|1|08-06-2001|12:00|a.html|http://www.wiley.com/science|1
10|1|08-06-2001|12:00|b.html|http://www.wiley.com/math|1


Table
Vendor

id|Name
1|Wilson Interscience
2|Johnson Publishing


Table Journal

id|vendor|title|url
1|1|Wiley
Science|/cgi-bin/counter.cgi?http://www.wiley.com/science
2|1|Wiley Math|/cgi-bin/counter.cgi?http://wwww.wiley.com/math
3|2|Johnson




#!/usr/bin/perl -w
use CGI;
use Mysql;

print "Content-Type: text/html\n\n";
print "<html>\n";
print "<head>\n";
print "<title>Result</title>\n";
print "<meta http-equiv=\"Content-Type\" content=\"text/html; charset=iso-8859-1\">\n";
print "</head>\n";
print "\n";
print "<body bgcolor=\"#FFFFFF\">\n";
print "<br>";

###########################################
$dbh = Mysql->connect("localhost", "journals", "root", "root1");


$q=new CGI;
#print $q->param(startdate);
$startdate=$q->param("startdate");
$enddate=$q->param("enddate");
$vendor=$q->param("vendor");

print "Your serch is:<br>Vendor=$vendor<br>
startdate=$startdate<br>enddate=$enddate<br>";

$qstr="Select distinct v.name, j.title,c.url,c.count
from vendor v, journal j, count c
where v.uid = j.vendor
and substring(j.url,22)=c.url
and c.date>=\"$startdate\"
and c.date<=\"$enddate\"
and v.name=\"$vendor\"
;";

#print "$qstr<br>";

$sth = $dbh->query($qstr);

$name="0";
print "<table border=3>\n";
print "<tr><td>name</td><td>title</td><td>url</td><td>count</td></tr>\n";
$notfound=1;
while ($name ne ""){
      ($name,$title,$url,$count) = $sth->fetchrow;
      if ($name eq ""){last}
      $s{$name."</td><td>".$title."</td><td>".$url}=
      $s{$name."</td><td>".$title."</td><td>".$url}+$count;
      $notfound=0;
}

for $j(sort keys %s)
{
      print "<tr><td>$j</td><td>$s{$j}</td></tr>\n";
}

print "</table>\n";
if ($notfound==1) {
print "<p>Sorry No data Collected for $vendor from $startdate to $enddate</p>";
}
print "</body>\n";
print "</html>\n";
print "\n";


Produces excessive output
(Ouput based on real journals)

Your serch is:
Vendor=Wiley Interscience
startdate=2001-08-09
enddate=2001-08-09
 name
             title
                                                        url
                                                                                 
             count
 Wiley Interscience
             Advanced Materials
                                                        http://www3.interscience.wiley.com/cgi-bin/jtoc?ID=10008336
                                                                                 
             1
 Wiley Interscience
             Advanced Materials
                                                        http://www3.interscience.wiley.com/cgi-bin/jtoc?ID=26737
                                                                                 
             1
 Wiley Interscience
             Advanced Materials
                                                        http://www3.interscience.wiley.com/cgi-bin/jtoc?ID=27981
                                                                                 
             1
 Wiley Interscience
             Advanced Materials
                                                        http://www3.interscience.wiley.com/cgi-bin/jtoc?ID=29240
                                                                                 
             1
 Wiley Interscience
             Advanced Materials
                                                        http://www3.interscience.wiley.com/cgi-bin/jtoc?ID=30728
                                                                                 
             1
 Wiley Interscience
             Advanced Materials
                                                        http://www3.interscience.wiley.com/cgi-bin/jtoc?ID=32249
                                                                                 
             1
 Wiley Interscience
             Angewandte Chemie International Edition
                                                        http://www3.interscience.wiley.com/cgi-bin/jtoc?ID=10008336
                                                                                 
             1
 Wiley Interscience
             Angewandte Chemie International Edition
                                                        http://www3.interscience.wiley.com/cgi-bin/jtoc?ID=26737
                                                                                 
             1
 Wiley Interscience
             Angewandte Chemie International Edition
                                                        http://www3.interscience.wiley.com/cgi-bin/jtoc?ID=27981
                                                                                 
             1
 Wiley Interscience
             Angewandte Chemie International Edition
                                                        http://www3.interscience.wiley.com/cgi-bin/jtoc?ID=29240
                                                                                 
             1


This is how resport should look if
proper:

Query by end user

vendor: Wiley Interscience

Start Date: 2001-08-04
End
Date: 2001-08-05

submit


Form returned

Vedor Link Count Resport for Wiley Interscience

From:
2001-08-04
To: 2001-08-05

Vendor             Journal         Url             Count

Wiley       Interscience
       Science Journal            http://www.wiley.com/science    3
Wiley       Interscience
       Math  Journal            http://www.wiley.com/math    2



 

Anyone know how to fix this thing

thanks

0
Comment
Question by:zipdog44
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 8

Accepted Solution

by:
bebonham earned 100 total points
ID: 6404346
what is $count?

you don't define any where I see...
can you do this?
     $s{$name."</td><td>".$title."</td><td>".$url}++;    

also, the behaviour you are talking about sounds like it is being caused because %s's keys are not exactly the same...

hope that helps.

0
 
LVL 16

Expert Comment

by:maneshr
ID: 6628526
zipdog44,

Did you get the solution you were looking for?

What solution, if any, did you use??

Please let us know.

If you have already found the solution on your own or this question has become obsolete, please go ahead and delete this question.

Thanks,
0
 
LVL 16

Expert Comment

by:maneshr
ID: 6654395
zipdog44,

                     Did you get the solution you were looking for?

                     What solution, if any, did you use??

                     Please let us know.

                     If you have already found the solution on your own or this question has become obsolete, please go ahead
                     and delete this question.

                     Thanks,
0
 
LVL 16

Expert Comment

by:maneshr
ID: 6697080
zipdog44,

Did you get the solution you were looking for?

What solution, if any, did you use?

Please let us know.

If you have already found the solution on your own or this question has become obsolete, please go ahead
and delete this question.

Thanks,
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6699253
Last login was October, no response to follow up requests, assume Asker was satisfied and force accepting to close.
Moondancer
Community Support Moderator @ Experts Exchange
0

Featured Post

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

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

It is a general practice to get rid of old user profiles on a computer  in a LAN environment. As I have been working with a company in a LAN environment where users move from one place to some other place at times. This will make many user profil…
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

624 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