RE: [PHP-DB] serious help with linking data together...
By George I think I may have got it!
Code:
Keyword
Search Engine
Count
' ' GROUP BY keyword ORDER BY kTotal
DESC");
while ($keyResult = db_fetch($keyQuery)) {
$refQuery = db_query("select referer,
COUNT(referer) as refTotal FROM SiteTrackingTable WHERE
keyword=".escapeQuote($keyResult[keyword])." GROUP BY referer ORDER BY
refTotal DESC");
$nrows = db_numrows($refQuery);
?>
Link: http://www.rinkrake.com/blah.php
Wacha think?
Aaron
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] serious help with linking data together...
Hi all,
Sorry about not posting the problem that I was having. Was hard to
explain without showing an example page and figure there was a problem
with the code SQL itself that someone would spot off the bat.
Anyway.. here's a link: http://www.rinkrake.com/blah.php
You'll see what I am trying to do but here are the problems:
I want to order the "keywords" from MOST to least (so I guess I need a
count on the keyword grouping then ORDER by it.
Some of the keywords aren't showing ANY Search Engine info. Each keyword
DOES have a Search Engine associated with it.
All seems very confusing to me and I don't know how to trouble shoot
this :(
Here's the code again and thanks very much for your time.
Keyword
Search Engine
Count
' ' GROUP BY keyword");
while ($keyResult = db_fetch($keyQuery)) {
$refQuery = db_query("SELECT referer,
COUNT(referer) as refTotal FROM SiteTrackingTable WHERE
keyword=".$keyResult[keyword]." GROUP BY referer");
$nrows = db_numrows($refQuery);
?>
-Original Message-
From: Jason Vincent [mailto:jayv@;nortelnetworks.com]
Sent: November 15, 2002 8:51 AM
To: Aaron Wolski; [EMAIL PROTECTED]
Subject: RE: [PHP-DB] serious help with linking data together...
you haven't really said what the problem is - but this is what I can
see...
Functions like COUNT, MAX and MIN etc. are group functions (or aggregate
functions). For example, select count(*) actually only returns 1 record
-
the total count. When you mix aggregate functions with regular selects
(that return many rows - like select keyword) you are going to get
strange
results.
What are you seeing
Regards,
J
-Original Message-
From: Aaron Wolski [mailto:aaronjw@;martekbiz.com]
Sent: Thursday, November 14, 2002 5:17 PM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] serious help with linking data together...
Hey all,
Been trying too figure this out all day.
First off I'll explain what I am TRYING to do.
I am creating a Search Engine Tracking Report that lists keywords and
the
Search Engines used on that keyword along with the count total for that
search Engine - example:
Keyword Search Engine Count
Web hosting Yahoo
27
Google
20
MSN
12
Web Design Overture
30
MSN
17
So on and so forth.
Seems simple right? But no not for me!
CODE:
2) {
$refQuery = db_query("SELECT *, COUNT(referer)
as refTotal FROM SiteTrackingTable WHERE keyword=".$keyResult[keyword]."
GROUP BY referer ORDER BY refTotal DESC");
$nrows = db_numrows($refQuery);
?>
If this is too messed up let me know and I'll reformat.
Sorry guy the annoying question guys.
Aaron
RE: [PHP-DB] serious help with linking data together...
you haven't really said what the problem is - but this is what I can see...
Functions like COUNT, MAX and MIN etc. are group functions (or aggregate
functions). For example, select count(*) actually only returns 1 record -
the total count. When you mix aggregate functions with regular selects
(that return many rows - like select keyword) you are going to get strange
results.
What are you seeing
Regards,
J
-Original Message-
From: Aaron Wolski [mailto:aaronjw@;martekbiz.com]
Sent: Thursday, November 14, 2002 5:17 PM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] serious help with linking data together...
Hey all,
Been trying too figure this out all day.
First off I'll explain what I am TRYING to do.
I am creating a Search Engine Tracking Report that lists keywords and the
Search Engines used on that keyword along with the count total for that
search Engine - example:
Keyword Search Engine Count
Web hosting Yahoo
27
Google
20
MSN
12
Web Design Overture
30
MSN
17
So on and so forth.
Seems simple right? But no not for me!
CODE:
2) {
$refQuery = db_query("SELECT *, COUNT(referer)
as refTotal FROM SiteTrackingTable WHERE keyword=".$keyResult[keyword]."
GROUP BY referer ORDER BY refTotal DESC");
$nrows = db_numrows($refQuery);
?>
If this is too messed up let me know and I'll reformat.
Sorry guy the annoying question guys.
Aaron
Re: [PHP-DB] serious help with linking data together...
Right, ok, I understand your code. What's wrong?
On Thu, 14 Nov 2002, Aaron Wolski wrote:
> I am creating a Search Engine Tracking Report that lists keywords and
> the Search Engines used on that keyword along with the count total for
> that search Engine - example:
>
>
> Keyword Search Engine Count
> Web hosting Yahoo 27
> Google 20
> MSN 12
> Web DesignOverture30
> MSN 17
> So on and so forth.
> Seems simple right? But no not for me!
>
> $keyQuery = db_query("SELECT keyword, COUNT(keyword) as kTotal
>FROM SiteTrackingTable WHERE $query GROUP BY keyword");
> while ($keyResult = db_fetch($keyQuery)) {
> if (strlen($keyResult[keyword]) > 2) {
> $refQuery = db_query("SELECT *, COUNT(referer)
> as refTotal FROM SiteTrackingTable WHERE keyword=".$keyResult[keyword]."
> GROUP BY referer ORDER BY refTotal DESC");
> $nrows = db_numrows($refQuery);
> ?>
>
>
>
>
>
>
>
>
>while ($refResult = db_fetch($refQuery)) { > ?>
>
>
>
>
>} ?>
>
>
>
> }
> }
> ?>
---
Peter BeckmanSystems Engineer, Fairfax Cable Access Corporation
[EMAIL PROTECTED] http://www.purplecow.com/
---
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] serious help with linking data together...
Hey all,
Been trying too figure this out all day.
First off I'll explain what I am TRYING to do.
I am creating a Search Engine Tracking Report that lists keywords and
the Search Engines used on that keyword along with the count total for
that search Engine - example:
Keyword Search Engine Count
Web hosting Yahoo
27
Google
20
MSN
12
Web Design Overture
30
MSN
17
So on and so forth.
Seems simple right? But no not for me!
CODE:
2) {
$refQuery = db_query("SELECT *, COUNT(referer)
as refTotal FROM SiteTrackingTable WHERE keyword=".$keyResult[keyword]."
GROUP BY referer ORDER BY refTotal DESC");
$nrows = db_numrows($refQuery);
?>
If this is too messed up let me know and I'll reformat.
Sorry guy the annoying question guys.
Aaron
