Query error COUNT not working correctly

[Virangya]

Hello,

I wrote a query to find the products names by the name entered and to count now many records are outputted.

$query="SELECT COUNT(product_id) AS tot,product_id,product_name FROM tblproduct WHERE product_name LIKE '%$pname%'";

$query="SELECT COUNT(*) AS tot,product_id,product_name FROM tblproduct WHERE product_name LIKE '%$pname%'";

I tried both way. but always it returns one record, but at the same time output 'tot' as the correct total.

why?

[tesuji]

Hello,

the WHY is given there

Carefully read my two postings. If you have still problems with aggregate functions and "missing" group-by clause, simply ask again.

-- tesu

[tesuji]

// Your query

$query="SELECT COUNT(product_id) AS tot,product_id,product_name FROM tblproduct WHERE product_name LIKE '%$pname%'"

// must have group-by clause

$query="SELECT COUNT(product_id) AS tot,product_id,product_name FROM tblproduct WHERE product_name LIKE '%$pname%' [B]GROUP BY[/B] product_id, product_name"

//  in order to work properly.

If you don't like GROUP-BY clause you must omit "product_id,product_name ". If you don't omit these two columns the results are indeterminate.

-- tesu

[Virangya]

:D thanx a lot! :) at one point i did thought of that, but then i think i forgot..

[tesuji]

:D thanx a lot! :) at one point i did thought of that, but then i think i forgot..

Ok:) Unfortunately, one can easily be seduced by MySQL when using aggregate functions. Other database systems always adamantly return an error if group-by is missing when using aggregate function together with other columns.

-- tesu

[Virangya]

thanx for that information too :)

[Virangya]

Ok:) Unfortunately, one can easily be seduced by MySQL when using aggregate functions. Other database systems always adamantly return an error if group-by is missing when using aggregate function together with other columns.

-- tesu

hey,
here i changed my query again... but now its not working :( tot always equal to 1 ....

$query=sprintf("SELECT COUNT(product_id) AS tot,product_id,product_name FROM tblproduct WHERE product_name LIKE '%s' GROUP BY product_id LIMIT %s,%s",
"%".mysql_real_escape_string($pname)."%",
mysql_real_escape_string($start),
mysql_real_escape_string($recs_for_page)
);

I dont know whether you are familiar with php.. but in mysql only, this is also equal to something like;

$query="SELECT COUNT(product_id) AS tot,product_id,product_name FROM tblproduct WHERE product_name LIKE '%name%' GROUP BY product_id LIMIT 1,3;"

whats wrong with it?

[tesuji]

Hi again,

can you tell me what "product_id" is?

Most likely "product_id" seems to be an unique IDent, possibly generated by autoincrement. If so, there is nothing to group-by. That means count(*) is always 1 because each product_id is unique.

If you want the grand total, that is you want to count all rows of your table, then your select should be:

SELECT COUNT(product_id) AS grandtotal FROM tblproduct;

If you want to count all rows, where product_name is like '%name% your select should be:

SELECT COUNT(product_id) AS tot, product_name FROM tblproduct WHERE product_name LIKE '%name%' GROUP BY product_name ORDER BY tot LIMIT 10;

Here only rows with same '%name%' will be counted together. Every time the value of '%name%' changes, count(*) is reset to 0 and starts again.

If you have LIMIT in your select statement, ORDER BY is mandatory. If you omit ORDER BY, the result is indeterminate too. Almost all database systems will raise an error if FIRST, TOP or LIMIT is used without ORDER BY clause.

-- tesu