Jump to content

Help with complex query


galbus

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!

Link to comment
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.

Link to comment
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.

Link to comment
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);
?>

Link to comment
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!

Link to comment
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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.