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.

[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

[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. 

[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