Solved

Query Problem Want distinct

Posted on 2001-08-09
5
227 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
  • 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

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

Making a simple AJAX shopping cart Couple years ago I made my first shopping cart, I used iframe and JavaScript, it was very good at that time, there were no sessions or AJAX, I used cookies on clients machine. Today we have more advanced techno…
In this tutorial I will show you how to make a simple HTML bar chart with the usage of WhizBase, If you want more information about WhizBase please read my previous articles at http://www.experts-exchange.com/ARTH_5123186.html (http://www.experts-ex…
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

863 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

20 Experts available now in Live!

Get 1:1 Help Now