Solved

Query Problem Want distinct

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

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

Question has a verified solution.

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

In this tutorial I will focus on how to use WhizBase as a tool for sending ICQ messages to ICQ. Here I will use a new technology in WhizBase, published in WhizBase 5.1 version. In this tutorial I will use 3 files, pager.wbsp for the processing, e…
This tutorial will discuss fancy secure registration forms, with AJAX technology support. In this article I assume you already know HTML and some JS. I will write the code using WhizBase Server Pages, so you need to know some basics in WBSP (you mig…
Learn the basics of strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
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 …

729 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