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.
精彩评论