Understanding COUNT() as `count`,

Question

I'm currently learning how to build a site in PHP mysql. However, I seem to fail to understand COUNT() as count and wouldn't mind some further explanation.

I get the principles of COUNT, 0 || 1, and how it returns all the values that pertain to that query. But, don't see how COUNT as count works.

Anyhow, this is how the code I'm writing goes - so we have a working example - and where I first became perplexed.

SELECT COUNT(`id`) as `count`, 
       `id` 
FROM `user` 
WHERE `email`='$email' 
AND `password`='".md5$password."'";

If anyone can explain be a great help!

Answer 1

That is called a column alias.

An alias is used to give columns a new name for the result set. This can be used to name a column based on an expressions (because otherwise the expression wouldn't "have" a name) or to rename columns, e.g. because the select list contains columns from different tables with the same name:

select t1.foo as t1_foo,
       t2.foo as t2_foo
from t1
   join t2 on ...

returns a result with the columns t1_foo and t2_foo

or

select avg(salary) as avg_salary
from employees

Btw: using a reserved word as an object name (count) isn't such a good idea in the first place.

Answer 2

$query = "select count(*) as `count` from tbl_name";
$result = mysql_query($query);
$data = mysql_fetch_array($result);

Now you can easily get the value of count as

echo $data['count'];

instead of

echo $data['count(*)'];

So as you see count is just an alias of count(*).