Count especific table rows in mysql

Question

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, Item 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?

Answer 1

you can use simple

Select count(*) from table

or If you want not null values

Select count(item) from table

Answer 2

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.

Answer 3

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.

Answer 4

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";

Answer 5

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.