show number of times x is found in a database column

jmartinez

What I am wanting to to is to have a database that has multiple values stored in it and be able to show the number of times a certain value is found in a specific column. For example

A|B|C

1|2|4
2|2|5
5|6|9

the number of times 9 is found in column C is 1. hope this makes since..

herve

Yes, use the count() SQL statement, like this :

SELECT COUNT(c) AS nb FROM table WHERE (c='9');

jmartinez

OK this is what i have

      <?php
      $Result = mysql_query("SELECT COUNT(*) FROM cat_info WHERE '$catname' LIKE '$catfathername'")
      $num_rows = mysql_num_rows($result);
	  echo "$Result";
      ?>

and I get this error

parse error, unexpected T_VARIABLE

hostcord

the variable $result is case sensitive so try that good luck

herve

also the Mysql query is quite surely broken

WHERE '$catname' LIKE '$catfathername'

isn't good

should be something like :

WHERE (father_name = '$catfathername')

where "father_name" is the column of the table where you store the cat's father name and if
the objective of the query is to found how many kittens a given cat has ?

Weedpacket

And also every statement (including the first one) needs to end with a semicolon.

jmartinez

Ok I must be missing the concept completely.. getting frustrated here.

I have tried all of the above suggestions and have this for the code now after reading many many links on this subject.

<?php
      $sql = "SELECT COUNT (*) from cat_info WHERE catfathername = '$catname'";
      $result=mysql_query($sql);
      $num = mysql_numrows($result);
      echo "$result";
?>

but get this error

mysql_numrows(): supplied argument is not a valid MySQL result resource

Maybe I should explain what I am doing. I have a table named cat_info with columns named id, catname, catfathername. What I am attempting to do is show the number of catfathername that are like catname. Aghhh now I am more confused...help!!!

LOL thnks!

Fox1337

For Count() you can simply use mysql_fetch_row(). It's not a matter of figuring out how many rows were returned (It will always be one with Count()), but rather the contents of the single row returned.

The following code should do what you are wanting:

$sql = "SELECT COUNT (*) FROM `cat_info` WHERE `catfathername`='$catname'";
$result = mysql_query($sql);
$count = mysql_fetch_row($result);
echo $count[0];

Or Simply:

$result = mysql_fetch_row(mysql_query("SELECT COUNT (*) FROM `cat_info` WHERE `catfathername`='$catname'"));
echo $result[0];

Weedpacket

Well, that's no different except you've quoted cat_info and catfathername in case they're reserved words in MySQL. I don't think they are.

More useful would be to see what the error message is being returned by MySQL so that you have a chance to find out why mysql_query() isn't producing a valid result resource.

$sql = "SELECT COUNT (*) FROM cat_info WHERE catfathername='$catname'";
$result = mysql_query($sql) or die(mysql_error());
$count = mysql_fetch_row($result);
echo $count[0];

jmartinez

Ok using Weedpacket's way I get this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(*) FROM cat_info WHERE catfathername='ACCESSORIES'' at line 1

I am using MYSQL 4.1.14-standard with PHP 4.4.1