Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Query Problem Want distinct

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Introduction This tutorial will give you a fast look what you can do with WhizBase. I expect you already know how to work with HTML at least, and that you understand the basics of the internet and how the internet works. WhizBase is a server-s…
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 while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…
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 …

715 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