How do I count?

Manat

Hi,

I have a search tool on my extranet.
I search through a list of keywords in the keywords table.

Table is like this:

SkuNum | keyword

8888 life stories
8888 life
8888 stories

So if I have a keyword that the user entered, $keyword
and I search through number of results with a LIKE %% clause to the WHERE, how do I count number of DISTINCT skus?

I do this:

$count=0;
while ($row=mysql_fetch_array($get_data))
{
if ($row[sku] != $sku_use)
{$count++; }
}

$get_data is my sql query.

Sometimes it doesn't work and my count is off because it would count all 3 above results when they are from the same SKU.

Any ideas?

thx
MK

AstroTeg

It would help greatly to see the sql code you're using. In the meantime, I'd recommend using SQL to do all the work for you.

Use GROUP BY to group the results by skus. The add the COUNT() function to your SELECT clause. You'd probably want to count the skus, so you might do COUNT(skus) or something similar. Then you might want to give this a field name so you might do "COUNT(skus) AS skuscount" which will assign the counted value to skuscount which you can use in your result set.

But it sort of depends on how your original sql query is setup. Sometimes this works great. Othertimes, when you start joining tables together, you can get results that are slightly off (or at least, results you weren't looking for).

Manat

Here is the SQL:

$keyword=$_GET['keyword'];
$get_data = mysql_query("SELECT keywords.*, Product.*, Price_Range.* FROM keywords, Price_Range, Product WHERE keywords.keyword LIKE '$keyword%' AND keywords.sku = Product.SKU  AND Product.Price_Range_ID = Price_Range.Price_Range_ID AND Product.Is_Active=1") or die(mysql_error());

I had originally started with Group By, but I kept getting parse errors, so I gave it up. I don't have that code anymore either.

If you have a good group by method, plz let me know.
I had tried what you suggested but was flooded by parse errors.

thx
mk

AstroTeg

Ok - I see this: "keywords., Product., Price_Range.*"

How many columns are you really using? Typically, doing * in a select clause is not a good practice. You've found one of the reasons why its not.

GROUP BY needs to know which specific columns need to be grouped. It also works with the select clause. If you do * in the select clause, then you're telling SQL to grab all fields. What is GROUP BY supposed to group on if you're tossing everything in? GROUP BY wants only what you're interested in.

Lets see if this makes sense.

1 table. 4 fields (ID, Name, Address, Phone).

SELECT * FROM table GROUP BY Name

Here, because ID is part of * in the select, its unique and each row will be brought back. This isn't what you want.

But if you did:

SELECT Name FROM table GROUP BY Name

Then this will bring back all unique names. If AstroTeg appeared 3 times and your name once, then it would just bring back AstroTeg and your name as the only two rows.

Its likely this doesn't make much sense. If it doesn't, I HIGHLY recommend reading up on GROUP BY and how it works and interacts. Then practice tossing some queries together and see how it works.

Once you have GROUP BY down. You can then play with COUNT(). COUNT won't work until you've mastered GROUP BY. COUNT just keeps a running total of whatever you want. Lets use my example above. We want to count how many unique people there are (remember: 3 AstroTegs and one with your name - the count should be 2).

SELECT COUNT(Name) FROM table GROUP BY Name

Here, GROUP BY is required to make COUNT work. Put COUNT in without GROUP BY and you'll get a parse error (what is COUNT supposed to count up if you don't tell it to group data together? - otherwise everything will have a count value of 1 and that doesn't make any sense).