Page 1 of 1

Query Help

Posted: Tue Feb 23, 2010 3:53 am
by Jay87
I have the following Query which counts the no. of top priority calls for all our customers:

Code: Select all

SELECT Count(id) as pricnt from faultlog where (queueid=1 or queueid=2) and status<15 and priority=1
I want to adjust the code so on the summary table we can make it count for the specific customer that is logged in.... i.e. Company X- can see they have 2 priority calls and Company Y can see they have 0 priority calls.

I have the following session variables on the login screen:

Code: Select all

$result = mssql_fetch_array(mssql_query("SELECT id,(forename + ' ' + surname) as contact,company FROM contact WHERE username='" . $_POST['user'] . "' AND PWDCOMPARE('" . $_POST['pass'] . "',password)=1",$db));
 
    //Check if the user is a customer
 if ($result!=NULL){
      $_SESSION['user'] = $result['contact'];
[color=#FF0040]      $_SESSION['company'] = $result['company'];[/color]
      $_SESSION['guid'] = $result['id'];
      $_SESSION['level']=1;
      $_SESSION['rights']=3;
      $_SESSION['pfx'] = "C" . date('Y');
      $_SESSION['conts'] = array ($result['id']);
I believe the session variable in red is the one i need to add to my query, i just can't seem to add that statement to the original query to get it to work...

Any Ideas?

8)

Re: Query Help

Posted: Tue Feb 23, 2010 6:27 am
by Jay87
Tried the following:

Code: Select all

$query = mssql_query("SELECT Count(id) as pricustcnt, SUM(CASE WHEN faultlog.Company = '" . $_SESSION[company] . "' THEN 1 ELSE 0 END) AS MyCompanyCount from faultlog where (queueid=1 or queueid=2) and status<15 and priority=1", $db);  
                    
$PriorityCustCount=mssql_result($query,0,'pricustcnt');
This still brings back all the companies priority calls instead of it being unique to the company

Re: Query Help

Posted: Tue Feb 23, 2010 10:27 am
by AbraCadaver
Assuming that $_SESSION['company'] and the column Company hold the same value, just add another WHERE condition:

Code: Select all

$query = mssql_query("
   SELECT COUNT(id) AS pricnt
      FROM faultlog
      WHERE (queueid=1 OR queueid=2)
      AND status<15
      AND priority=1
      AND Company='{$_SESSION['company']}'
";
In a properly normalized database though, the Company column would hold an ID that related to a companies table. So it depends on your schema and what $_SESSION['company'] holds.