mysql query count with criteria with result as number

Question

What i try to do is to get a mysql query to search in the table megabase in the column kla_abbonnement_groep for the words zilver,goud,platina and returns the amount of time the words are in the column into one number.

The database column consists out of 2x zilver, 2 x goud, 2 x platina.

I have the following code but it does not work. I have tried a lot of other methods but those where not displaying anything. The code below constantly displays a 1 but it should display a 6.

<?
include 'data/php/config.php';
// create query
$result = mysql_query("SELECT COUNT(*) FROM megabase WHERE kla_abbonnement_groep = 'zilver' AND kla_abbonnement_groep = 'goud' AND kla_abbonnement_groep = 'platina'");
$num_rows = mysql_num_rows($result);
echo "$num_rows";
?>

Thanks for any help in advance.

CHANGED THE CODE TO THE FOLLOWING WITH HELP FROM RIKESH

<?
include 'data/php/config.php';
// create query
$query = "SELECT COUNT(*) FROM megabase WHERE kla_abbonnement_groep = 'zilver' OR     kla_abbonnement_groep = 'goud' OR kla_abbonnement_groep = 'platina'";
print mysql_result(mysql_query($query),0)
?>

The question that remains how can I use the number that is printed in for example a javascript on the same page.

When I add:

$result = mysql_result(mysql_query($query),0);

I get the error.

Parse error: syntax error, unexpected T_VARIABLE in ... on line 60

I GOT IT WORKING EVERYONE THANKS FOR THE HELP COULDNT HAVE DONE IT WITHOUT:

This is the working code!

<?
include 'data/php/config.php';
// create query
$query = "SELECT COUNT(*) FROM megabase WHERE kla_abbonnement_groep = 'zilver' OR kla_abbonnement_groep = 'goud' OR kla_abbonnement_groep = 'platina'";
print mysql_result(mysql_query($query),0);
$result1 = mysql_result(mysql_query($query),0);
?>

I don't know PHP, but you want to return the value the query is returning, not the number of rows. The number of rows will be one. The value the query is returning will be the count. — Tom, Mar 14 '13 at 16:38

score 3 · Accepted Answer · edited May 25 '19 at 14:27

3

Use should use OR (Any of the condition satisfy) and not AND (All condition must have to satisfy).

$query = "SELECT COUNT(*) as count FROM megabase
WHERE kla_abbonnement_groep = 'zilver'
OR kla_abbonnement_groep = 'goud'
OR kla_abbonnement_groep = 'platina'";

Updated:

$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_assoc($result);
echo $row['count'];

NOTE:

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

edited May 25 '19 at 14:27

Zoe

27,060
21
118
148

answered Mar 14 '13 at 16:34

Rikesh

26,156
14
79
87

Thanks you Rikesh I have changed the code and it is working brilliantly. I still have a question how can i make this print mysql_result(mysql_query($query),0) into for example $result= mysql_result(mysql_query($query),0) – Mar 14 '13 at 16:45
Thanks for the answers Rikesh. Your answer with or instead of and gave me the first solution and your updated $result showed me that i had forgotten a ; which offcourse gives a nice error. Thank you – Mar 14 '13 at 16:58

score 1 · Answer 2 · answered Mar 14 '13 at 16:35

1

SELECT kla_abbonnement_groep, COUNT(kla_abbonnement_groep) FROM megabase 
WHERE kla_abbonnement_groep = 'zilver' 
OR kla_abbonnement_groep = 'goud' 
OR kla_abbonnement_groep = 'platina'
GROUP BY kla_abbonnement_groep

answered Mar 14 '13 at 16:35

karmafunk

1,453
12
20

I think he just wants a single count, not a separate count by group. – Tom Mar 14 '13 at 16:40
You may be right in which case @hennysmafter remove the GROUP BY statement and the first field in the SELECT. – karmafunk Mar 14 '13 at 16:42
Yes i want a single count and that function is now working thank you for your answers tom and karmafunk now i need to get it in for example $result=mysql_result(mysql_query($query),0); – Mar 14 '13 at 16:47

score 0 · Answer 3 · answered Mar 14 '13 at 16:59

Answer:

<?
include 'data/php/config.php';
// create query
$query = "SELECT COUNT(*) FROM megabase WHERE kla_abbonnement_groep = 'zilver' OR kla_abbonnement_groep = 'goud' OR kla_abbonnement_groep = 'platina'";
print mysql_result(mysql_query($query),0);
$result1 = mysql_result(mysql_query($query),0);
?>

mysql query count with criteria with result as number

3 Answers3