Count especific table rows in mysql

I want to count how many rows I have in a mysql table and display it in html.

For example:

There is: Item 1, Item 2, It开发者_运维百科em 3, Item 4, Item 5, Item 6

If Item 1 and Item 2 are filled, I want a html code to say - 2 items in row

any ideas?

you can use simple

Select count(*) from table

or If you want not null values

Select count(item) from table

IF its total rows you are after, then as Nishant mentioned above you could do something like this

$query = "SELECT COUNT(*) FROM mytable WHERE myfield='myvalue'"; 

I am counting all the ids, because it will reduce the time taken to calculate the total instead of '*' and

Thanks to Nico, count(*) and count(column) are not so different considering performance. Here is a comparision

Use a where condition to narrow your total as you require.

If you want to count only rows which are filled (meaning, which are not null), you should add a small WHERE clause, or specify the column of interest in the count() function:

• using where

  SELECT count(*) FROM mytable WHERE fieldx IS NOT NULL

• specifying the field of interest

  SELECT count(fieldx) FROM mytable

in this last solution, count() will only take into account not-null fieldx values.

You can get the number of rows of your query using the function mysql_num_rows().

So if you have a SELECT statement (to select which items you want), you just have to call mysql_num_rows() after doing the query. Example from php.net:

$link = mysql_connect("localhost", "mysql_user", "mysql_password");
mysql_select_db("database", $link);

$result = mysql_query("SELECT * FROM table1", $link);
$num_rows = mysql_num_rows($result);

echo "$num_rows Rows\n";

What do you mean by filled? Anyway, if by 'filled' you mean that some specific field has a NOT NULL value you could do something like this:

$sqlConn = new mysqli('host', 'user', 'password', 'database');
$sqlConn->query('SELECT * FROM table WHERE field1 IS NOT NULL AND field2 IS NOT NULL');
echo 'You have ' . $sqlConn->affected_rows . ' rows.';

Hope it helps.