Query Help

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Jay87
Forum Commoner
Posts: 61
Joined: Thu Jan 07, 2010 5:22 am

Query Help

Post 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)
Jay87
Forum Commoner
Posts: 61
Joined: Thu Jan 07, 2010 5:22 am

Re: Query Help

Post 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
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Query Help

Post 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.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Post Reply