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!