CODEWITHSUNDEEP
phpmysql
Ryan
Ryan

Reputation: 13

Returning Mysql result with COUNT( * )

I'm trying to return a count from mysql. My code is below

$number_of_options_sql = tep_db_query("SELECT COUNT( * ) FROM 
(select sum(options_id) as total from products_attributes 
where products_id='".$products_id."' group by options_id) as total");
$number_of_options_result = tep_db_fetch_array($number_of_options_sql);

When I run this query in Phpmyadmin, it shows the result with COUNT(*) at the table heading. I'm getting the correct result, the query works for me, I just can't print it out on the screen.

I've tried returning the value the following ways and neither print anything on the screen:

echo $number_of_options_result[COUNT( * )];
echo $number_of_options_result[total];

Upvotes: 0

Views: 718

Answers (4)

nerkn
nerkn

Reputation: 1980

put in a variable "total"

$number_of_options_sql = tep_db_query("SELECT COUNT(*) as total

then it works

echo $number_of_options_result['total'];

Upvotes: 0

Chandu
Chandu

Reputation: 82913

Your query is assigning an alias to the table/query not the column. use this one below:

$number_of_options_sql = tep_db_query("SELECT COUNT(*) as total FROM (select sum(options_id) as total from products_attributes where products_id='".$products_id."' group by options_id) a");
$number_of_options_result = tep_db_fetch_array($number_of_options_sql);

Also looks like you want to know count of distinct options id for a product_id, I would rewrite the query as follows:

$number_of_options_sql = tep_db_query("SELECT COUNT(DISTINCT options_id) as total FROM  products_attributes WHERE products_id='".$products_id."'");

Upvotes: 0

tsusanka
tsusanka

Reputation: 4841

Just edit your query to 

SELECT COUNT(*) as count FROM ....

then it will be stored like 'count' and you can print it the $number_of_options_result[count]; way.

Upvotes: 0

RabidFire
RabidFire

Reputation: 6330

Use AS field_name after COUNT(*)

$number_of_options_sql = tep_db_query("SELECT COUNT(*) AS field_name FROM (select sum(options_id) as total from products_attributes where products_id='".$products_id."' group by options_id) as total");

To print: 

echo $number_of_options_result['field_name'];

(Replace "field_name" with any relevant name of your choice)

Upvotes: 2

Related Questions