Function to count MySQL rows using WHERE

am making function to count rows using “WHERE”, but i get a mysql error

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:AppServwwwtesttestindex.php on line 9 Unknown column ‘1’ in ‘where clause’

here is my function

    function CountRows($table, $field = NULL, $value = NULL){
        mysql_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD);
        mysql_select_db(DB_NAME);
        if($field != NULL && $value != NULL){
            return mysql_num_rows(mysql_query("SELECT * FROM `".$table."` WHERE `".$field."` = `".$value."`"))or die(mysql_error());
        }else{
            return mysql_num_rows(mysql_query("SELECT * FROM `".$table."`"));   
        }
    }

i’ve created this function to simplify counting rows mysql rows for banned members, inactive members etc, since all will be using WHERE

all help will be appreciated, thanks in advance

Answer

1. You should not connect to database each time you need to do a query.. Just keep a persistent connection or ideally use PDO.

2. Value should be enclosed with simple single quotes. This is probably what is getting you an error, as anything enclosed in backticks kind of quotes is considered a database/table/field name.

3. Use COUNT(*), it does not fetch all the database rows.

4. If value is possibly supplied by user, make sure that it is safe by escaping it with mysql_real_escape_string if not using PDO.

Without using PDO code would be:

mysql_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD);
mysql_select_db(DB_NAME);

function CountRows($table, $field = NULL, $value = NULL){

    if ($field != NULL && $value != NULL) {
        $query = "SELECT COUNT(*) 
                  FROM `".$table."` 
                  WHERE `".$field."` = '". mysql_real_escape_string($value) . "'";
    } else {
        $query = "SELECT COUNT(*) FROM `".$table."`";   
    }

    $count = mysql_fetch_array(mysql_query($query));
    return $count[0];
}