RE: [PHP-DB] serious help with linking data together...

[Jason Vincent]

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 nooooo not for me!

CODE:

<?php

        $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);

?>
        
<tr valign="top">
        
<td class="content2"><?php echo $keyResult[keyword]; ?></td>
        
<td colspan="2">
        
<table border="0" cellpadding="0" cellspacing="0" width="250">
        
<tr valign="top">
        
<td><img src="../Graphics/spacer.gif"  width="150" height="1"></td>
        
<td><img src="../Graphics/spacer.gif"  width="100" height="1"></td>
        
</tr>
<?php
                
                        while ($refResult = db_fetch($refQuery)) {

?>
        
<tr valign="top">
        
<td class="content2"><?php echo $refResult[referer]; ?></td>
        
<td class="content2" align="center"><?php echo $refResult[refTotal]; ?></td>
        
</tr>
<?php

                        }

?>
        
</table>

        
</td>
        
</tr>
<?php

                }


        }

?>


If this is too messed up let me know and I'll reformat.

Sorry guy the annoying question guys.

Aaron