• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 521
  • Last Modified:

COUNT(IF( PHP MSSQL query issue

hi Experts,

Thank you for taking a look at my question!  Your assistance is very much appreciated.

I am having issues with my query which works fine until I add this to it:

,COUNT(IF(RiskLevelID='4',1,null)) as RSKLVL2

The full query is here:
$query = "SELECT CONVERT(VARCHAR(10),DateFunded,111), COUNT(BrandID) as counter FROM db Where CONVERT(VARCHAR(10),DateFunded,111) is not NULL and CONVERT(VARCHAR(10),DateFunded,111) BETWEEN '".$firstmonth."' and '".$today."' GROUP BY CONVERT(VARCHAR(10),DateFunded,111) order by CONVERT(VARCHAR(10),DateFunded,111)" ;

Can anyone spot where I have gone wrong?  Thank you very much for your help!!
0
mavmanau
Asked:
mavmanau
  • 7
  • 3
1 Solution
 
pritamduttCommented:
Can please post the error you are getting and also the generated query
0
 
mavmanauNetwork Engineer/SysadminAuthor Commented:
there is no error I can see as such.

is there a way to configure it to give me a message back?  this is my current code.:
$query = "SELECT CONVERT(VARCHAR(10),DateFunded,111), COUNT(BrandID) as counter FROM DB Where CONVERT(VARCHAR(10),DateFunded,111) is not NULL and CONVERT(VARCHAR(10),DateFunded,111) BETWEEN '".$firstmonth."' and '".$today."' GROUP BY CONVERT(VARCHAR(10),DateFunded,111) order by CONVERT(VARCHAR(10),DateFunded,111)" ;
echo $query."<br />";
//Load the query
$stmt = $c->prepare($query) ;

 //Run the query
 $stmt->execute() ;

With using mySQL libraries in the past I have normally been able to use "or DIE ( error message) but this is first time I have been using PDO sql.
0
 
pritamduttCommented:
can you please post the output of  echo $query command
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
mavmanauNetwork Engineer/SysadminAuthor Commented:
SELECT CONVERT(VARCHAR(10),DateFunded,111), COUNT(BrandID) as counter, COUNT(IF(RiskLevelID='2',1,null)) as RSKLVL2 FROM DB Where CONVERT(VARCHAR(10),DateFunded,111) is not NULL and CONVERT(VARCHAR(10),DateFunded,111) BETWEEN '2011/09/01' and '2011/09/30' GROUP BY CONVERT(VARCHAR(10),DateFunded,111) order by CONVERT(VARCHAR(10),DateFunded,111)
0
 
pritamduttCommented:
I don't remember seeing IF within a Select Statement,

Please see http://msdn.microsoft.com/en-us/library/ms189499(v=SQL.90).aspx for SQL Select Syntax
0
 
mavmanauNetwork Engineer/SysadminAuthor Commented:
if I alter the where statement in the query of course it will work, but the end result I want is to a total of records for each date, and then a total if the RiskLevelID = 2, and one or 2 other totals as well, but once I have one worked out the rest should follow suit.

SELECT CONVERT(VARCHAR(10),DateFunded,111), COUNT(*) as counter FROM DB Where CONVERT(VARCHAR(10),DateFunded,111) is not NULL and RiskLevelID = '2' and CONVERT(VARCHAR(10),DateFunded,111) BETWEEN '2011/09/01' and '2011/09/30' GROUP BY CONVERT(VARCHAR(10),DateFunded,111) order by CONVERT(VARCHAR(10),DateFunded,111)

everything I have googled points to:
COUNT(IF(condition,value if true, value if false))
0
 
mavmanauNetwork Engineer/SysadminAuthor Commented:
in actual queries run on a server, I believe the syntax is to use     CASE rather than IF.

I have used it before in other projects but that was talking to an ODBC connection or a MYSQL server using the standard MYSQL libararies rather than using the PDOSQL libraries in PHP, maybe it doesn't work using this extension.

0
 
mavmanauNetwork Engineer/SysadminAuthor Commented:
got an error finally:
PDO::errorInfo(): Array ( [0] => 42000 [1] => 156 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword 'if'. )
0
 
mavmanauNetwork Engineer/SysadminAuthor Commented:
All good worked it out, had to use this instead
count(case when RiskLevelID=2 then 1 else null end)

this is because of some limitations etc in PDO.

Thank you for your assistance....sometimes good just to have a sounding board!
0
 
mavmanauNetwork Engineer/SysadminAuthor Commented:
thank you for your help....I didn't use the page that you linked but you responded and got me thinking in a different way.

cheers!!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now