Jump to content

Archived

This topic is now archived and is closed to further replies.

galbus

Help with complex query

Recommended Posts

Hello (first post  :)),

 

I am using SQL Server 2005 and I'm having trouble generating a complex query.

 

I have the following tables:

 

+--------------------+

| Users              |

+--------------------+

| UserID            |

| CompanyName        |

| Postcode          |

+--------------------+

 

And a table called PageVisits

 

+--------------------+

| PageVisits        |

+--------------------+

| url                |

| UserID            |

| timestamp          |

+--------------------+

 

Each time a user visits a page a new row is added to PageVisits with the respective UserID and page url.

 

I need to list each user and the respective number of page visits they have made in the following format:

 

+---------------------------------------------------------------+

| UserID | Company | Postcode | index.html | contact.html | ... |

+---------------------------------------------------------------+

| user  | company | NW6 3AB  | 2          | 10          | ... |

+---------------------------------------------------------------+

 

Where the list of URLs is generated from the unique URLs in the pageVisits table. Pages can be added at any point so explicitly declaring these is not ideal.

 

Can any SQL whizz help me? I'm stuck!

Share this post


Link to post
Share on other sites

a structure like that is lot really possible without a complex procedure or something to dynamically generate a temporary table. why can't you just do a normal join grouping on the url. so if the user has been to 5 pages, there would be 5 entries for that user.

Share this post


Link to post
Share on other sites

I'd love to - but the format of the output has already been agreed so I can't change that - and the data format id from a legacy database too so I can't change that!

 

Thanks anyway rhodesa, I'll look into doing a procedure now.

Share this post


Link to post
Share on other sites

can you manipulate the data at all between the query and handing it off to the calling script? aka, if this was PHP (not sure what you are using):

 

<?php
  $result = mssql_query('SELECT u.UserID,u.CompanyName,u.Postcode,p.url,COUNT(p.timestamp) as visits FROM PageVisits p LEFT JOIN Users u ON P.UserID = u.UserID GROUP BY u.UserID,u.CompanyName,u.Postcode,p.url')
    or die(mssql_get_last_message());

  $data = array();
  while($row = mssql_fetch_assoc($query)){
    if(!isset($data[$row['UserID']])){
      $data[$row['UserID']] = array(
        'UserID' => $row['UserID'],
        'CompanyName' => $row['CompanyName'],
        'Postcode' => $row['Postcode'],
      );
    }
    $data[$row['UserID']][$row['url']] = $row['visits'];
  }
  print_r($data);
?>

Share this post


Link to post
Share on other sites

Cheers Rhodesa, this works well. I modified it a bit to this for my needs:

 

<?php
  $result = mssql_query('SELECT u.UserID,u.CompanyName,u.Postcode,p.url,COUNT(p.timestamp) as visits FROM PageVisits p LEFT JOIN Users u ON P.UserID = u.UserID GROUP BY u.UserID,u.CompanyName,u.Postcode,p.url')
    or die(mssql_get_last_message());
  // $urlList contains a list of all site URLs to be counted
  $urlList = array('/', '/contact.html', '/products.html');
  $data = array();
  while($row = mssql_fetch_assoc($query)){
    if(!isset($data[$row['UserID']])){
      $data[$row['UserID']] = array(
        'UserID' => $row['UserID'],
        'CompanyName' => $row['CompanyName'],
        'Postcode' => $row['Postcode'],
      );
      foreach ($urlList as $url) {
       $data[$row['UserID']][$url] = 0;
      }
    }
    $data[$row['UserID']][$row['url']] = $row['visits'];
  }
  ksort($data);
  print_r($data);
?>

 

Now each user has the same number of entries, and any pages not visited have 0 in the counter. When we add a new page we just need to add an entry in the $urlList array and it will be counted.

 

Thanks for your help!

Share this post


Link to post
Share on other sites

if you want to make urlList dynamic, you can add another query before all of this code to build $urlList:

$urlList = array();
$result = mssql_query('SELECT DISTINCT(url) FROM PageVisits');
while($row = mssql_fetch_array($query)){
 $urlList[] = $row[0];
}

 

edit: this does rely on at least one person going to the page before it will show up in the stats...but at least you won't have to edit the file each time

Share this post


Link to post
Share on other sites

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.