Help with complex query

[galbus]

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!

[rhodesa]

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.

[galbus]

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.

[rhodesa]

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

[galbus]

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!

[rhodesa]

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