getting result from a select sum if query

[kapkap]

Hi there

I am new to this forum and not much of a php expert. I hope you can assist me.

My problem is as follows:

I have a column consisting of several values (in this case: sceience, art, humanistics and music)

I would like to use the following mysqli query in order to count the appearances of each value in this column:

 

 

$query = "SELECT SUM (IF(department = 'science', 1, 0)) AS science, SUM(IF(department = 'art', 1, 0))-> AS art FROM new2"

 

I have tried lots of ways to loop the results but couldn't get the it working.

Can anyone suggest any idea?

 

Thanks

 

Hanan

 

[jazzman1]

 

$query = "SELECT SUM (IF(department = 'science', 1, 0)) AS science, SUM(IF(department = 'art', 1, 0))-> AS art FROM new2"

 

Is this the query string trying to execute by php to mysql? if so, that the syntax of your query is not correct.

Edited September 5, 2014 by jazzman1

[cyberRobot]

Try changing this:

$query = "SELECT SUM (IF(department = 'science', 1, 0)) AS science, SUM(IF(department = 'art', 1, 0))-> AS art FROM new2";

 

To this:

$query = "SELECT SUM (IF(department = 'science', 1, 0)) AS science, SUM(IF(department = 'art', 1, 0)) AS art FROM new2";

 

 

Note that I removed the "->" before " AS art".

[jazzman1]

 

SUM (IF(department = 'science', 1, 0))

Remove the empty space between sum and (). Sum is a function in mysql

Edited September 5, 2014 by jazzman1

[cyberRobot]

Remove the empty space between sum and (). Sum is a function in mysql

 

For some reason I thought the space didn't matter, but you're right. I must have removed it subconsciously when I ran my tests. 

Edited September 5, 2014 by cyberRobot

[kapkap]

Thanks

any advice regarding the display of this query, I have tried so many and  got nothing.

Thanks

[jazzman1]

what debugging steps have you taken so far? A chance to see some of your actual code? 

[kapkap]

hi 

solved with help from a friend, a little different but gives me the data I need

 

$query = "select department, count(department) as count from new2

group by department";

if ($result = mysqli_query($con, $query)) {

 

while ($row = mysqli_fetch_assoc($result)) {

printf ("%s (%s)\n", $row['department'], $row['count']);

}

 

/* free result set */

mysqli_free_result($result);

}

 

mysqli_close($con);

 

thank you all