count number of filed value

I want to count the number of values in one field in MySQL. I would then like to store the resulting count in the database开发者_StackOverflow中文版.

Please help me work out the query required to do this.

Also could you help me to write a count function in PHP?

$query="select count(<column>) as total from <table>";
$result = mysql_query($query);
$total=mysql_result($result,0,"total");

Incomplete question. Fuzzy answering.

http://dev.mysql.com/doc/refman/5.1/en/counting-rows.html

SELECT COUNT(field) FROM table

http://php.net/manual/en/function.count.php

count($array);

This would return the count of rows in MySQL.

SELECT COUNT(*) AS Column_Count FROM Table_Name

You could also just do this:

SELECT * FROM Table_Name

And count manually via PHP, via:

$query = mysql_query("SELECT * FROM Table_Name");
$count = mysql_num_rows($query);

something like:

function getTotalRows($table,$column = 'id')
{
    global $mysql_link;

    try
    {
        $resource = mysql_query(sprintf('SELECT count(%s) AS count FROM %s',$column,$table),$mysql_link);

        if($resource !== false)
        {
             return (int)mysql_result($resource,0,'count');
        }

        return false;
    }catch(Exception $e)
    {
        return false;
    } 
}

and them do:

$mysql_link = mysql_connect(....);

$total = getTotalRows('users','id');

To store the result of a count directly in the database, you can use INSERT INTO ... SELECT:

INSERT INTO table1 (count_col) SELECT COUNT(*) FROM table2;

Connect to DB:

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Not connected : ' . mysql_error());
}

// make foo the current db
$db_selected = mysql_select_db('foo', $link);
if (!$db_selected) {
    die ('Can\'t use foo : ' . mysql_error());
}

Update column:

$qry = "UPDATE `<my_table>` SET `<column>` = (SELECT COUNT(<column>) FROM <table> )";
Mysql_query($qry);

How to select value are in others posts.