Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Query Problem Want distinct

Posted on 2001-08-09
5
Medium Priority
?
240 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 400 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This tutorial will discuss the log-in process using WhizBase. In this article I assume you already know HTML. I will write the code using WhizBase Server Pages, so you need to know some basics in WBSP (you might look at some of my other articles abo…
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 modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses

926 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