asked on

Query Problem Want distinct

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

ASKER CERTIFIED SOLUTION

bebonham

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

maneshr

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,

maneshr

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,

Moondancer

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